גרי רשף

Use TempDB;

Go

If Object_Id('T_SCD','U') Is Not Null Drop Table T_SCD;

Go

Create Table T_SCD(ID Int, StartDate DateTime, EndDate DateTime);

Go

--1 תקין

Insert Into T_SCD Select 1 ID, '20090101' StartDate, '20090731' EndDate;

Insert Into T_SCD Select 1 ID, '20090801' StartDate, '20090815' EndDate;

Insert Into T_SCD Select 1 ID, '20090816' StartDate, '20091031' EndDate;

Insert Into T_SCD Select 1 ID, '20091101' StartDate, '20091231' EndDate;

--2 רשומה אחת "חותכת" את האחרת

Insert Into T_SCD Select 2 ID, '20090101' StartDate, '20090731' EndDate;

Insert Into T_SCD Select 2 ID, '20090801' StartDate, '20090815' EndDate;

Insert Into T_SCD Select 2 ID, '20090810' StartDate, '20091031' EndDate;

Insert Into T_SCD Select 2 ID, '20091101' StartDate, '20091231' EndDate;

--3 רשומה אחת "בולעת" את האחרת

Insert Into T_SCD Select 3 ID, '20090101' StartDate, '20090815' EndDate;

Insert Into T_SCD Select 3 ID, '20090801' StartDate, '20090810' EndDate;

Insert Into T_SCD Select 3 ID, '20090816' StartDate, '20091031' EndDate;

Insert Into T_SCD Select 3 ID, '20091101' StartDate, '20091231' EndDate;

--4 רשומות חופפות

Insert Into T_SCD Select 4 ID, '20090101' StartDate, '20090815' EndDate;

Insert Into T_SCD Select 4 ID, '20090101' StartDate, '20090815' EndDate;

Insert Into T_SCD Select 4 ID, '20090816' StartDate, '20091031' EndDate;

Insert Into T_SCD Select 4 ID, '20091101' StartDate, '20091231' EndDate;

--5 יש "חור" באמצע

Insert Into T_SCD Select 5 ID, '20090101' StartDate, '20090731' EndDate;

Insert Into T_SCD Select 5 ID, '20090801' StartDate, '20090815' EndDate;

Insert Into T_SCD Select 5 ID, '20090816' StartDate, '20091031' EndDate;

Insert Into T_SCD Select 5 ID, '20091201' StartDate, '20091231' EndDate;

--6 תאריך ההתחלה אחרי תאריך הסיום

Insert Into T_SCD Select 6 ID, '20090131' StartDate, '20090121' EndDate;

--7 עוד אחד תקין

Insert Into T_SCD Select 7 ID, '20120101' StartDate, '20120103' EndDate;

Insert Into T_SCD Select 7 ID, '20120104' StartDate, '20120105' EndDate;

Insert Into T_SCD Select 7 ID, '20120106' StartDate, '20120108' EndDate;

Insert Into T_SCD Select 7 ID, '20120109' StartDate, '20120110' EndDate;

--8 שתי תקופות חופפות מיותרות באמצע

Insert Into T_SCD Select 8 ID, '20120101' StartDate, '20120103' EndDate;

Insert Into T_SCD Select 8 ID, '20120104' StartDate, '20120110' EndDate;

Insert Into T_SCD Select 8 ID, '20120105' StartDate, '20120106' EndDate;

Insert Into T_SCD Select 8 ID, '20120105' StartDate, '20120106' EndDate;

--9 שתי תקופות חופפות בתוך חור באמצע

Insert Into T_SCD Select 9 ID, '20120101' StartDate, '20120103' EndDate;

Insert Into T_SCD Select 9 ID, '20120108' StartDate, '20120110' EndDate;

Insert Into T_SCD Select 9 ID, '20120105' StartDate, '20120106' EndDate;

Insert Into T_SCD Select 9 ID, '20120105' StartDate, '20120106' EndDate;

--10 שני זוגות של תקופות חופפות

Insert Into T_SCD Select 10 ID, '20120101' StartDate, '20120102' EndDate;

Insert Into T_SCD Select 10 ID, '20120101' StartDate, '20120102' EndDate;

Insert Into T_SCD Select 10 ID, '20120105' StartDate, '20120106' EndDate;

Insert Into T_SCD Select 10 ID, '20120109' StartDate, '20120110' EndDate;

Insert Into T_SCD Select 10 ID, '20120109' StartDate, '20120110' EndDate;

--11 עוד אחד תקין

Insert Into T_SCD Select 11 ID, '20120101' StartDate, '20120110' EndDate;

Go

Select  *

From    T_SCD

Order By ID,

        StartDate;

Select  ID,

        StartDate [Date]

From    T_SCD

Union All

Select  ID,

        EndDate [Date]

From    T_SCD

Order By ID,

        [Date];

Select  ID,

        T1.[Date]

From    T_SCD T

Cross Apply (Select * From (Values(T.StartDate),(T.EndDate)) AS D([Date])) T1

Order By ID,

        [Date];

Select  ID,

        [Date],

        Sum(DateDiff(Day,StartDate,EndDate+1)) SDD,

        Min(StartDate) Mn,

        Max(EndDate)+1 Mx,

        Min(Min(StartDate)) Over(Partition By ID) MnMn,

        Max(Max(EndDate)) Over(Partition By ID)+1 MxMx,

        Count(*) Cnt

From    T_SCD T

Cross Apply (Select * From (Values(T.StartDate),(T.EndDate+1)) AS D([Date])) T1

Group By ID,

        [Date]

Order By ID,

        [Date];

With T1 As

(Select ID,

        [Date],

        Sum(DateDiff(Day,StartDate,EndDate+1)) SDD,

        Min(StartDate) Mn,

        Max(EndDate)+1 Mx,

        Min(Min(StartDate)) Over(Partition By ID) MnMn,

        Max(Max(EndDate)) Over(Partition By ID)+1 MxMx,

        Count(*) Cnt

From    T_SCD T

Cross Apply (Select * From (Values(T.StartDate),(T.EndDate+1)) AS D([Date])) T1

Group By ID,

        [Date])

Select  ID,

        Sum(SDD)/2 SDD,

        DateDiff(Day,MIN(Mn),MAX(Mx)) DDs,

        Sum(Case When [Date]=MnMn Then Cnt End) SmMn,

        Sum(Case When [Date]=MxMx Then Cnt End) SmMx,

        Count(Case When Mn<>MnMn And Mx<>MxMx And Cnt<>2 Then 1 End) CntEls

From    T1

Group By ID

Order By ID;

With T1 As

(Select ID,

        [Date],

        Sum(DateDiff(Day,StartDate,EndDate+1)) SDD,

        Min(StartDate) Mn,

        Max(EndDate)+1 Mx,

        Min(Min(StartDate)) Over(Partition By ID) MnMn,

        Max(Max(EndDate)) Over(Partition By ID)+1 MxMx,

        Count(*) Cnt

From    T_SCD T

Cross Apply (Select * From (Values(T.StartDate),(T.EndDate+1)) AS D([Date])) T1

Group By ID,

        [Date]),

T2 As

(Select ID,

        Sum(SDD)/2 SDD,

        DateDiff(Day,MIN(Mn),MAX(Mx)) DDs,

        Sum(Case When [Date]=MnMn Then Cnt End) SmMn,

        Sum(Case When [Date]=MxMx Then Cnt End) SmMx,

        Count(Case When [Date]<>MnMn And [Date]<>MxMx And Cnt<>2 Then 1 End) CntEls

From    T1

Group By ID)

Select  *

From    T2

Where SDD<>DDS

      Or (SmMn<>1

         Or SmMx<>1

         Or CntEls>0)

Order By ID;