英文:
Dates Falls Within a Range
问题
我想计算在给定日期范围内的记录数量。这两个记录应该在派生字段Year_1985中计数。我无法弄清如何确保这些日期在指定范围内。AD_Start_Date和AD_End_Date都是我的表中的DATE字段。
记录1（应在Year_1985、Year_1986和Year_1987中计数）：
AD_Start_Date = '01/30/1980'
AD_End_Date = '07/01/1990'
记录2（仅应在Year_1985中计数）：
AD_Start_Date = '03/30/1985'
AD_End_Date = '07/01/1985'
记录3（应在Year_1985、Year_1986和Year_1987中计数）：
AD_Start_Date = '02/01/1978'
AD_End_Date = '07/01/1990'
记录4（应在Year_1986和Year_1987中计数）：
AD_Start_Date = '05/01/1986'
AD_End_Date = '11/30/1987'
SELECT
SUM(CASE WHEN to_char(AD_Start_Date, 'MM/DD/YYYY') <= '12/31/1985' AND
to_char(AD_End_Date, 'MM/DD/YYYY') >= '01/01/1985' THEN 1 ELSE 0 END) AS Year_1985
,SUM(CASE WHEN to_char(AD_Start_Date, 'MM/DD/YYYY') <= '12/31/1986' AND
to_char(AD_End_Date, 'MM/DD/YYYY') >= '01/01/1986' THEN 1 ELSE 0 END) AS Year_1986
,SUM(CASE WHEN to_char(AD_Start_Date, 'MM/DD/YYYY') <= '12/31/1987' AND
to_char(AD_End_Date, 'MM/DD/YYYY') >= '01/01/1987' THEN 1 ELSE 0 END) AS Year_1987
英文:
I would like to count the number of records which is within a given date range. These two records should be counted in the derived field Year_1985. I can't figure out how to make sure that these dates fall within the specified range. AD_Start_Date and AD_End_Date are both DATE fields in my table.
Record 1 (Should be counted in Year_1985, Year_1986, Year_1987):
AD_Start_Date = '01/30/1980'
AD_End_Date = '07/01/1990'
Record 2 (Should be counted in Year_1985 only):
AD_Start_Date = '03/30/1985'
AD_End_Date = '07/01/1985'
Record 3 (Should be counted in Year_1985, Year_1986, Year_1987)
AD_Start_Date = '02/01/1978'
AD_End_Date = '07/01/1990'
Record 4 (Should be counted in Year_1986, Year_1987)
AD_Start_Date = '05/01/1986'
AD_End_Date = '11/30/1987'
SELECT
SUM(CASE WHEN to_char(AD_Start_Date, 'MM/DD/YYYY') <= '12/31/1985' AND
to_char(AD_End_Date,
'MM/DD/YYYY') >= '01/01/1985' THEN 1 ELSE 0 END) AS Year_1985
,SUM(CASE WHEN to_char(AD_Start_Date, 'MM/DD/YYYY') <= '12/31/1986' AND
to_char(AD_End_Date,
'MM/DD/YYYY') >= '01/01/1986' THEN 1 ELSE 0 END) AS Year_1986
,SUM(CASE WHEN to_char(AD_Start_Date, 'MM/DD/YYYY') <= '12/31/1987' AND
to_char(AD_End_Date,
'MM/DD/YYYY') >= '01/01/1987' THEN 1 ELSE 0 END) AS Year_1987
答案1
得分: 2
假设你发布的查询应该返回所需的结果，修改它以比较日期，而不是字符串（这是你正在做的）。
例如：
select
sum(case when ad_start_date <= date '1985-12-31' and
ad_end_date >= date '1985-01-01' then 1 else 0 end) as year_1985,
...
我使用了日期字面值，它们始终包含date关键字和日期值，用单引号括起来，格式为YYYY-MM-DD。
你也可以尝试使用to_date函数，其第一个参数表示日期值，而第二个参数反映该值的格式，例如
to_date('31.12.1985', 'dd.mm.yyyy')。
[编辑]，在你发布了更多信息之后。
条件聚合，与你所做的略有不同。如果我理解你正确，对于示例数据：
with test (id, ad_start_date, ad_end_Date) as
(select 1, date '1980-01-30', date '1990-07-01' from dual union all -- 1985, 1986, 1987
select 2, date '1985-03-30', date '1985-07-01' from dual union all -- 1985
select 3, date '1978-02-01', date '1990-07-01' from dual union all -- 1985, 1986, 1987
select 4, date '1986-05-01', date '1987-11-30' from dual -- 1986, 1987
)
查询看起来像这样；偶然地，**所有**年份（1985年、1986年和1987年）都具有相同的结果值：`3`：
```sql
select
sum(case when 1985 between extract(year from ad_start_date) and
extract(year from ad_end_date) then 1 else 0 end) year_1985,
sum(case when 1986 between extract(year from ad_start_date) and
extract(year from ad_end_date) then 1 else 0 end) year_1986,
sum(case when 1987 between extract(year from ad_start_date) and
extract(year from ad_end_date) then 1 else 0 end) year_1987
from test;
英文:
Presuming that query you posted should return desired result, modify it so that you compare dates, not strings (which is what you're doing).
For example:
select
sum(case when ad_start_date <= date '1985-12-31' and
ad_end_date >= date '1985-01-01' then 1 else 0 end) as year_1985,
...
I used date literals which always consist of the date keyword and date value enclosed into single quotes in YYYY-MM-DD format.
You could also try with to_date function whose first parameter represents date value, while the second one reflects that value's format, e.g.
to_date('31.12.1985', 'dd.mm.yyyy').
[EDIT], after you posted some more info.
Conditional aggregation it is, just a little bit different from what you did. If I understood you correctly, with sample data
SQL> with test (id, ad_start_date, ad_end_Date) as
2 (select 1, date '1980-01-30', date '1990-07-01' from dual union all -- 1985, 1986, 1987
3 select 2, date '1985-03-30', date '1985-07-01' from dual union all -- 1985
4 select 3, date '1978-02-01', date '1990-07-01' from dual union all -- 1985, 1986, 1987
5 select 4, date '1986-05-01', date '1987-11-30' from dual -- 1986, 1987
6 )
Query looks like this; accidentally, all years (1985, 1986 and 1987 have the same resulting value: 3:
7 select
8 sum(case when 1985 between extract(year from ad_start_date) and
9 extract(year from ad_end_date) then 1 else 0 end) year_1985,
10 sum(case when 1986 between extract(year from ad_start_date) and
11 extract(year from ad_end_date) then 1 else 0 end) year_1986,
12 sum(case when 1987 between extract(year from ad_start_date) and
13 extract(year from ad_end_date) then 1 else 0 end) year_1987
14 from test;
YEAR_1985 YEAR_1986 YEAR_1987
---------- ---------- ----------
3 3 3
SQL>
答案2
得分: 1
可以使用BETWEEN：
SELECT
SUM(CASE WHEN ('1985-01-01' BETWEEN AD_Start_Date AND AD_End_Date
OR '1985-12-31' BETWEEN AD_Start_Date AND AD_End_Date) THEN 1 ELSE 0 END) AS Year_1985
英文:
You can use BETWEEN :-
SELECT
SUM(CASE WHEN ('01-JAN-1985' BETWEEN AD_Start_Date AND AD_End_Date
OR '31-DEC-1985' BETWEEN AD_Start_Date AND AD_End_Date ) THEN 1 ELSE 0 END) AS Year_1985
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论