不知道如何在这一个上标题！
我有以下数据：
IF OBJECT_ID ('tempdb..#data') IS NOT NULL DROP TABLE #data
CREATE TABLE #data
(
id UNIQUEIDENTIFIER,reference NVARCHAR(30),start_date DATETIME,end_date DATETIME,lapse_date DATETIME,value_received DECIMAL(18,3)
)
INSERT INTO #data VALUES ('BE91B9C1-C02F-46F7-9B63-4D0B25D9BA2F','168780','2006-05-01 00:00:00.000',NULL,'2011-09-27 00:00:00.000',537.42)
INSERT INTO #data VALUES ('B538F123-C839-447A-B300-5D16EACF4560','320858','2011-08-08 00:00:00.000',0)
INSERT INTO #data VALUES ('1922465D-2A55-434D-BAAA-8E15D681CF12','306597','2011-04-08 00:00:00.000','2011-06-22 13:14:40.083','2011-08-07 00:00:00.000',12)
INSERT INTO #data VALUES ('7DF8FBCC-B490-4892-BDC5-8FD2D73B0323','321461','2011-07-01 00:00:00.000','2011-09-25 00:00:00.000',8.44)
INSERT INTO #data VALUES ('1EC2E754-F325-4313-BDFC-9010E255F6FE','74215','2000-10-31 00:00:00.000','2011-08-30 00:00:00.000',258)
INSERT INTO #data VALUES ('9E59B09C-0198-48AC-8EEC-A0D76CEA9385','169194','2008-06-25 00:00:00.000',1766.4)
INSERT INTO #data VALUES ('97CF6C0F-324A-49A6-B9D8-AC848A1F821A','288039','2010-09-01 00:00:00.000','2011-07-29 00:00:00.000','2011-08-21 00:00:00.000',55)
INSERT INTO #data VALUES ('97CF6C0F-324A-49A6-B9D8-AC848A1F821A','324423','2011-08-01 00:00:00.000',5)
INSERT INTO #data VALUES ('D5E5197A-E8E1-468C-9991-C8712224C2BF','323395','2011-08-25 00:00:00.000',0)
INSERT INTO #data VALUES ('0EC4976C-16B9-4C99-BD07-D0CBDF014D32','323741',0)
我希望能够根据以下标准将所有引用分组为“活动”,“已失效”或“新”类别：
>活动的开始日期小于参考月的最后日期,上个月的最后一天之后的失效日期和value_received> 0;
> New的开始日期在参考月份内;
>失效的失效日期在参考月份内.
然后为每个参考应用这些定义,滚动13个月(所以从现在回溯到2010年7月),以便每个月我可以看到有多少参考属于每个组.
我可以使用以下内容为当月定义：
select
id,reference,start_date,end_date,lapse_date,value_received,CASE WHEN start_date < DATEADD(month,DATEPART(Month,GETDATE()) + 1,DATEADD(year,DATEPART(year,GETDATE())-1900,0)) --next month start date
AND lapse_date > DATEADD(ms,-3,DATEADD(mm,DATEDIFF(mm,GETDATE())+1,0)) --last day of current month
AND value_received > 0
THEN 'Active'
WHEN lapse_date < DATEADD(month,0)) --next month start
AND lapse_date > DATEADD(ms,GETDATE()),0)) --last day of prior month
THEN 'lapse'
WHEN start_date < DATEADD(month,0)) --next month start date
AND start_date > DATEADD(ms,0)) --last day of prior month
THEN 'New'
ELSE 'Not applicable'
END AS [type]
from #data
但我看不到一个好的/有效的方法(除了重复这个查询13次并结合结果,我知道这很糟糕)
这是使用当前月份作为锚点和使用递归的情况(如果是这样,一些指针将是最受欢迎的)？
任何最受赞赏的帮助:)
如果它对任何人感兴趣,这是我使用的最终查询：
;WITH Months as
(
SELECT DATEADD(ms,0)) as month_end,0 AS level
UNION ALL
SELECT DATEADD(month,-1,month_end)as month_end,level + 1 FROM Months
WHERE level < 13
)
SELECT
DATENAME(Month,month_end) + ' ' + DATENAME(YEAR,month_end) as date,SUM(CASE WHEN start_date <= month_end
AND Month(start_date) <> MONTH(Month_end)
AND lapse_date > Month_end
THEN 1 ELSE 0 END) AS Active,SUM(CASE WHEN start_date <= Month_end
AND DATENAME(MONTH,start_date) + ' ' + DATENAME(YEAR,start_date) =
DATENAME(MONTH,month_end)
THEN 1 ELSE 0 END) AS New,SUM(CASE WHEN lapse_date <= Month_end
AND Month(lapse_date) = MONTH(Month_end)
THEN 1 ELSE 0 END) AS lapse
FROM #data
CROSS JOIN Months
WHERE id IS NOT NULL
AND start_date IS NOT NULL
GROUP BY DATENAME(Month,month_end) + ' ' + DATENAME(YEAR,month_end)
ORDER by MAX(level) ASC
解决方法
这里你不需要一个“真正的”递归CTE.您可以使用一个月份参考：
;WITH Months
as
(
SELECT DATEADD(day,-DATEPART(day,GETDATE()) as 'MonthStart'
UNION ALL
SELECT DATEADD(month,MonthStart) as 'MonthStart'
FROM Months
)
然后你可以在上面的查询中加入SELECT TOP 13 * FROM Months.
我不会尝试解析所有CASE语句,但基本上您可以在日期和MonthStart字段上使用GROUP BY,例如：
GROUP BY Datepart(年,月开始),Datepart(月,月开始)
并按月汇总.将所有选项(活动,失效等)作为列进行计算可能是最简单的,并使用SUM(CASE WHEN … THEN 1 ELSE 0 END)计算每个选项,因为使用GROUP BY会更容易.
