连续日期

2021-09-10 00:00:00 sql tsql sql-server

这是我正在使用的表格:

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

相关文章