使用日期范围的可能的递归CTE查询 [英] Possible recursive CTE query using date ranges

问题描述

不知道如何在这个标题上加上标题！

Not sure how to even phrase the title on this one!

我有以下数据：

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',NULL,NULL,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',NULL,'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',NULL,'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',NULL,'2011-09-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',NULL,'2011-09-25 00:00:00.000',5)
INSERT INTO #data VALUES ('D5E5197A-E8E1-468C-9991-C8712224C2BF','323395','2011-08-25 00:00:00.000',NULL,NULL,0)
INSERT INTO #data VALUES ('0EC4976C-16B9-4C99-BD07-D0CBDF014D32','323741','2011-08-25 00:00:00.000',NULL,NULL,0)

我希望能够分组所有基于以下条件的活动，失效或新类别的引用：

And I want to be able to group all references into a category of 'active', 'lapsed' or 'new' based upon the following criteria:

• 活动有一个开始日期，该日期小于参考月份的最后日期，上个月的最后一天之后的失效日期和value_received> 0;

• Active has a start date that is less than the last date of the reference month, a lapse date after the last day of the prior month and a value_received > 0;

新建的开始日期在参考月份之内；

New has a start date which falls within the reference month;

失效的失效日期在参考月之内。

Lapsed has a lapse date which falls within the reference month.

然后应用这些定义滚动13个月的每个参考文献（因此从现在回到2010年7月），这样我每个月就能看到每个组有多少参考文献。

And to then apply these definitions for each reference for a rolling 13 months (so from Now going back as far as July 2010) so that for each month I can see how many references fall into each group.

我可以使用以下内容来定义当前月份：

I am able to use the following to define this for the current month:

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,0,GETDATE())+1,0)) --last day of current month
        AND value_received > 0
        THEN 'Active'
        WHEN lapse_date < DATEADD(month,DATEPART(Month,GETDATE()) + 1,DATEADD(year,DATEPART(year,GETDATE())-1900,0)) --next month start
            AND lapse_date > DATEADD(ms,-3,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)) --last day of prior month
        THEN 'lapse'
        WHEN start_date < DATEADD(month,DATEPART(Month,GETDATE()) + 1,DATEADD(year,DATEPART(year,GETDATE())-1900,0)) --next month start date
        AND start_date > DATEADD(ms,-3,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)) --last day of prior month
        THEN 'New'
        ELSE 'Not applicable'
 END AS [type]
from #data

但我看不到

这是一个很好的/有效的方法（除了将这个查询重复13次并合并结果，我知道这很糟糕）。

Would this be a case for using the current month as an anchor and using recursion (if so, some pointers would be most appreciated)?

任何一如既往的帮助：）

Any help most appreciated as always :)

*编辑为包含实际解决方案*

如果任何人都感兴趣，这就是我使用的最终查询：

In case it's of interest to anyone, this is the final query I used:

;WITH Months as 
(
SELECT DATEADD(ms,-3,DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,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) + ' ' + DATENAME(YEAR,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。不过，您可以使用一个作为月份参考：

You don't need a "real" recursive CTE here. You can use one for the month references though:

;WITH Months
as
(
    SELECT DATEADD(day, -DATEPART(day, GETDATE())+1, GETDATE()) as 'MonthStart'
    UNION ALL
    SELECT DATEADD(month, -1, MonthStart) as 'MonthStart'
    FROM Months
)

然后您可以 JOIN 到选择前13位*从月份在上述查询中。

Then you can JOIN to SELECT TOP 13 * FROM Months in your above query.

我是不会尝试解析所有 CASE 语句，但是基本上可以在日期和日期上使用 GROUP BY MonthStart 字段，例如：

I'm not going to try to parse all your CASE statements, but essentially you can use a GROUP BY on the date and the MonthStart fields, like:

GROUP BY Datepart（year，monthstart），Datepart（月，月开始）

并按月汇总。将所有选项（活动，失效等）作为列，并使用 SUM（在...时为1，否则为0的情况下结束）进行计算，这将是最简单的。因为使用 GROUP BY 会更容易。

and aggregate by month. It will probably be easiest to have all your options (active, lapsed, etc) as columns and calculate each with a SUM(CASE WHEN ... THEN 1 ELSE 0 END) as it will be easier with a GROUP BY.

这篇关于使用日期范围的可能的递归CTE查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！