我需要找出在3个不同年份(2006年,2007年,2008年)的申请期结束日期(fpe)的180天内,有60天内(180天之内)有120天内提交(fil_dt)纳税申报单的人

下面的陈述将给我所有的岁月
我需要每年和每种可能性的计数。
无论如何,我可以做到这一点,而无需2个查询?

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'

我需要你的帮助
多谢

最佳答案

然后写

   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)

如果您希望120天计数和180天计数仅包括60岁以上且少于120岁的人,等等,那么,
     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)

10-07 16:10
查看更多