SQL query with count and case statement

case, count, sql

Solution

then write

   SELECT YEAR(A.FPE) AS "YEAR",
       Sum(CASE WHEN DAYS(A.FIL_DT) - DAYS(A.FPE) < 60 
                  THEN 1 Else 0 End) SixtydayCount,  
       Sum(CASE WHEN DAYS(A.FIL_DT) - DAYS(A.FPE) < 120 
                  THEN 1 Else 0 End) OneTwentyDayCount,
       Sum(CASE WHEN DAYS(A.FIL_DT) - DAYS(A.FPE) < 180 
                  THEN 1  Else 0 End) OneEightyDayCount,
       Sum(CASE WHEN DAYS(A.FIL_DT) - DAYS(A.FPE) > 180 
                  THEN 1 Else 0 End)  OverOneEightyCount  
    From Table A
    WHERE A.FPE BETWEEN '2006-01-01' AND '2008-12-31'
    Group By YEAR(A.FPE)

If you want the 120 day count and the 180 day count to only include the folks who are over 60 and less than 120, etc. then,

     SELECT YEAR(A.FPE) AS "YEAR",
       Sum(CASE WHEN DAYS(A.FIL_DT) - DAYS(A.FPE) < 60 
                  THEN 1 Else 0 End) SixtydayCount,  
       Sum(CASE WHEN DAYS(A.FIL_DT) - DAYS(A.FPE) Between 60 And 119 
                  THEN 1 Else 0 End) OneTwentyDayCount,
       Sum(CASE WHEN DAYS(A.FIL_DT) - DAYS(A.FPE) Between 120 And 179 
                  THEN 1  Else 0 End) OneEightyDayCount,
       Sum(CASE WHEN DAYS(A.FIL_DT) - DAYS(A.FPE) >= 180 
                  THEN 1 Else 0 End)  OverOneEightyCount  
    From Table A
    WHERE A.FPE BETWEEN '2006-01-01' AND '2008-12-31'
    Group By YEAR(A.FPE)

Problem

i need to find how many people have filed (fil_dt) their tax return within 60 days, withing 120 days, within 180 days, over 180 days of their filing period end date (fpe) for 3 different years (2006, 2007, 2008) the statement below will give me ALL years i need a count for each year and for each possibility.. anyway i can do this without 2 queries ? ``` SELECT YEAR(A.FPE) AS "YEAR" ,CASE WHEN DAYS(A.FIL_DT) - DAYS(A.FPE) < 60 THEN '2 ' WHEN DAYS(A.FIL_DT) - DAYS(A.FPE) < 120 THEN '4 ' WHEN DAYS(A.FIL_DT) - DAYS(A.FPE) < 180 THEN '6 ' WHEN DAYS(A.FIL_DT) - DAYS(A.FPE) > 180 THEN '6+' END AS "NBR MTH" WHERE A.FPE BETWEEN '2006-01-01' AND '2008-12-31' ``` i need your help thanks a lot

Original source