我需要从一个会员表中提取和拆分数据。
我想要将范围拆分,每年得到一行。
日期从和日期到可以是一年中的任何一天,但是当日期被拆分时,我们假设一行在12月31日结束,新的一行从1月1日开始。
以下是数据的样式:
membershipId - groupId - ClientId - DateFrom - DateTo
2707 20008 1579 1997-01-01 00:00:00.000 1997-12-31 00:00:00.000
20989 20008 1579 1999-01-01 00:00:00.000 2004-12-31 00:00:00.000
39874 20298 1579 2005-01-01 00:00:00.000 2008-12-31 00:00:00.000
50295 21661 1579 2009-01-01 00:00:00.000 2009-12-31 00:00:00.000
50988 20399 1579 2010-01-01 00:00:00.000 2010-12-31 00:00:00.000
52378 21661 1579 2011-01-01 00:00:00.000 2013-12-31 00:00:00.000
57274 21660 1579 2014-01-01 00:00:00.000 3000-01-01 00:00:00.000
期望的结果是:(按范围拆分)
2707 20008 1579 1997-01-01 00:00:00.000 1997-12-31 00:00:00.000
20989 20008 1579 1999-01-01 00:00:00.000 1999-12-31 00:00:00.000
20989 20008 1579 2000-01-01 00:00:00.000 2000-12-31 00:00:00.000
20989 20008 1579 2001-01-01 00:00:00.000 2001-12-31 00:00:00.000
20989 20008 1579 2002-01-01 00:00:00.000 2002-12-31 00:00:00.000
20989 20008 1579 2003-01-01 00:00:00.000 2003-12-31 00:00:00.000
20989 20008 1579 2004-01-01 00:00:00.000 2004-12-31 00:00:00.000
50295 21661 1579 2009-01-01 00:00:00.000 2009-12-31 00:00:00.000
50988 20399 1579 2010-01-01 00:00:00.000 2010-12-31 00:00:00.000
52378 21661 1579 2011-01-01 00:00:00.000 2011-12-31 00:00:00.000
52378 21661 1579 2012-01-01 00:00:00.000 2012-12-31 00:00:00.000
52378 21661 1579 2013-01-01 00:00:00.000 2013-12-31 00:00:00.000
57274 21660 1579 2014-01-01 00:00:00.000 3000-01-01 00:00:00.000
我尝试使用基于这篇文章的递归CTE:
Possible recursive CTE query using date ranges但是我无法达到期望的结果。
我创建了以下查询:
WITH splitDates(startDate,endDate, newDate,client, groupingId ) as
(
SELECT m.datefrom as startDate, m.dateTo
, CASE
when year(m.dateFrom) <> year(m.dateto) then CAST(CAST(year(m.dateFrom) AS varchar) + '-' + CAST(12 AS varchar) + '-' + CAST(31 AS varchar) AS DATETIME)
else m.dateTo
end
, m.legalEntityId, m.groupingId
from adesse.dbo.membership m
UNION ALL
SELECT DATEADD(year, 1, startDate),
CAST(CAST(year(startDate)+1 AS varchar) + '-' + CAST(12 AS varchar) + '-' + CAST(31 AS varchar) AS DATETIME)
,CAST(CAST(year(startDate)+1 AS varchar) + '-' + CAST(12 AS varchar) + '- ' + CAST(31 AS varchar) AS DATETIME)
,client, groupingId
FROM splitDates
WHERE year(startDate) <> year(endDate)
)
SELECT *
FROM splitDates
where client = 1579
order by startDate
但结果不完整 :(
startDate endDate newDate client groupingId
1997-01-01 00:00:00.000 1997-12-31 00:00:00.000 1997-12-31 00:00:00.000 1579 20008
1999-01-01 00:00:00.000 2004-12-31 00:00:00.000 1999-12-31 00:00:00.000 1579 20008
2000-01-01 00:00:00.000 2000-12-31 00:00:00.000 2000-12-31 00:00:00.000 1579 20008
2005-01-01 00:00:00.000 2008-12-31 00:00:00.000 2005-12-31 00:00:00.000 1579 20298
2006-01-01 00:00:00.000 2006-12-31 00:00:00.000 2006-12-31 00:00:00.000 1579 20298
2009-01-01 00:00:00.000 2009-12-31 00:00:00.000 2009-12-31 00:00:00.000 1579 21661
2010-01-01 00:00:00.000 2010-12-31 00:00:00.000 2010-12-31 00:00:00.000 1579 20399
2011-01-01 00:00:00.000 2013-12-31 00:00:00.000 2011-12-31 00:00:00.000 1579 21661
2012-01-01 00:00:00.000 2012-12-31 00:00:00.000 2012-12-31 00:00:00.000 1579 21661
2014-01-01 00:00:00.000 3000-01-01 00:00:00.000 2014-12-31 00:00:00.000 1579 21660
2015-01-01 00:00:00.000 2015-12-31 00:00:00.000 2015-12-31 00:00:00.000 1579 21660
感谢你的帮助
DATEADD(YEAR, DATEDIFF(YEAR, 0, dateFrom), 0) dateFrom
作为datefrom,以及DATEADD(day, -1, DATEADD(YEAR,1,DATEADD(YEAR, DATEDIFF(YEAR, 0, dateFrom), 0))) newDateTo
作为newdateto。 - JamieD77