问题描述
这是我正在使用的表格:
Here is the table that I am working with:
MemberID MembershipStartDate MembershipEndDate ================================================================= 123 2010-01-01 00:00:00.000 2012-12-31 00:00:00.000 123 2011-01-01 00:00:00.000 2012-12-31 00:00:00.000 123 2013-05-01 00:00:00.000 2013-12-31 00:00:00.000 123 2014-01-01 00:00:00.000 2014-12-31 00:00:00.000 123 2015-01-01 00:00:00.000 2015-03-31 00:00:00.000
我想要的是创建一行显示连续成员资格,如果成员资格中断超过 2 天,则为第二行,并具有新的开始和结束日期.
What I want is to create one row that shows continuous membership, and a second row if the membership breaks by more than 2 days, with a new start and end date..
所以我正在寻找的输出是这样的:
So the output I am looking for is like:
MemberID MembershipStartDate MembershipEndDate ================================================================= 123 2010-01-01 00:00:00.000 2012-12-31 00:00:00.000 123 2013-05-01 00:00:00.000 2015-03-31 00:00:00.000
这些日期附加了一个 memberID 字段,用于对它们进行分组.
There is a memberID field attached to these dates which is how they are grouped.
推荐答案
我以前也遇到过这种事情我用这样的东西
I've had to deal with this kind of thing before I use something like this
USE tempdb --Create test Data DECLARE @Membership TABLE (MemberID int ,MembershipStartDate date,MembershipEndDate date) INSERT @Membership (MemberID,MembershipStartDate,MembershipEndDate) VALUES (123,'2010-01-01','2012-12-31'), (123,'2011-01-01','2012-12-31'), (123,'2013-05-01','2013-12-31'), (123,'2014-01-01','2014-12-31'), (123,'2015-01-01','2015-03-31') --Create a table to hold all the dates that might be turning points DECLARE @SignificantDates Table(MemberID int, SignificantDate date, IsMember bit DEFAULT 0) --Populate table with the start and end dates as well as the days just before and just after each period INSERT @SignificantDates (MemberID ,SignificantDate) SELECT MemberID, MembershipStartDate FROM @Membership UNION SELECT MemberID,DATEADD(day,-1,MembershipStartDate ) FROM @Membership UNION SELECT MemberID,MembershipEndDate FROM @Membership UNION SELECT MemberID,DATEADD(day,1,MembershipEndDate) FROM @Membership --Set the is member flag for each date that is covered by a membership UPDATE sd SET IsMember = 1 FROM @SignificantDates sd JOIN @Membership m ON MembershipStartDate<= SignificantDate AND SignificantDate <= MembershipEndDate --To demonstrate what we're about to do, Select all the dates and show the IsMember Flag and the previous value SELECT sd.MemberID, sd.SignificantDate,sd.IsMember, prv.prevIsMember FROM @SignificantDates sd JOIN (SELECT MemberId, SignificantDate, IsMember, Lag(IsMember,1) OVER (PARTITION BY MemberId ORDER BY SignificantDate desc) AS prevIsMember FROM @SignificantDates ) as prv ON sd.MemberID = prv.MemberID AND sd.SignificantDate = prv.SignificantDate ORDER BY sd.MemberID, sd.SignificantDate --Delete the ones where the flag is the same as the previous value delete sd FROM @SignificantDates sd JOIN (SELECT MemberId, SignificantDate,IsMember, Lag(IsMember,1) OVER (PARTITION BY MemberId ORDER BY SignificantDate) AS prevIsMember FROM @SignificantDates ) as prv ON sd.MemberID = prv.MemberID AND sd.SignificantDate = prv.SignificantDate AND prv.IsMember = prv.prevIsMember --SELECT the Start date for each period of membership and the day before the following period of non membership SELECT nxt.MemberId, nxt.SignificantDate AS MembershipStartDate, DATEADD(day,-1,nxt.NextSignificantDate) AS MembershipEndDate FROM ( SELECT MemberID, SignificantDate, LEAd(SignificantDate,1) OVER (PARTITION BY MemberId ORDER BY SignificantDate) AS NextSignificantDate, IsMember FROM @SignificantDates ) nxt WHERE nxt.IsMember = 1