CODEWITHSUNDEEP
sqlsql-server
Ram
Ram

Reputation: 147

How to get Date Range which is matching a criteria

enter image description here

I can get the desired output by using while loop but since original table has thousands of record, performance is very slow.

How can I get the desired results using Common Table Expression?

Thank You.

Upvotes: 1

Views: 116

Answers (2)

John Cappelletti
John Cappelletti

Reputation: 81930

This will produce the desired results. Not as elegant as Gordon's, but it does allow for gaps in dates and dupicate dates.

If you have a Calendar/Tally Table, the cte logic can be removed.

Example

Declare @YourTable Table ([AsOfDate] Date,[SecurityID] varchar(50),[IsHeld] bit)
Insert Into @YourTable Values
 ('2017-05-19','S1',1)
,('2017-05-20','S1',1)
,('2017-05-21','S1',1)
,('2017-05-22','S1',1)
,('2017-05-23','S1',0)
,('2017-05-24','S1',0)
,('2017-05-25','S1',0)
,('2017-05-26','S1',1)
,('2017-05-27','S1',1)
,('2017-05-28','S1',1)
,('2017-05-29','S1',0)
,('2017-05-30','S1',0)
,('2017-05-31','S1',1)

;with cte1 as ( Select D1=min(AsOfDate),D2=max(AsOfDate) From @YourTable )
     ,cte2 as (
                Select Top (DateDiff(DAY,(Select D1 from cte1),(Select D2 from cte1))+1) 
                       D=DateAdd(DAY,-1+Row_Number() Over (Order By (Select Null)),(Select D1 from cte1)) 
                      ,R=Row_Number() over (Order By (Select Null))
                 From  master..spt_values n1,master..spt_values n2
              )
Select [SecurityID]
      ,[StartDate] = min(D)
      ,[EndDate]   = max(D)
From (
        Select *,Grp = dense_rank() over (partition by securityId order by asofdate )-R
         From @YourTable A
         Join cte2 B on AsOfDate=B.D
         Where IsHeld=1
     ) A
 Group By [SecurityID],Grp
 Order By min(D)

Returns

SecurityID      StartDate   EndDate
S1              2017-05-19  2017-05-22
S1              2017-05-26  2017-05-28
S1              2017-05-31  2017-05-31

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269543

This is a variant of the gaps-and-islands problem. In this case, you can use date arithmetic to calculate the rows with adjacent dates:

select securityId, isheld, min(asofdate), max(asofdate)
from  (select t.*,
              datediff(day,
                       - row_number() over (partition by securityId, isheld
                                            order by asofdate
                                           ),
                       asofdate) as grp
       from t
      ) t
group by grp, securityId, isheld;

Note: This assumes that the dates are contiguous and have no duplicates. The query can be modified to take those factors into account.

The basic idea is that if you have a sequence of days that are increasing one at a time, then you can subtract a sequence of values and get a constant. That is what grp is. The rest is just aggregation.

Upvotes: 1

Related Questions