Dates Falls Within a Range

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

Solution

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>