CODEWITHSUNDEEP
sqlsql-serversql-server-2008
SqlLearner
SqlLearner

Reputation: 793

Sql to repeat rows for all the months based on Startdate and EndDate

I have a table 'Quota' Which has Quota Amount for every team with Start Date and End Date of quota's. Here is the sample quota Table

enter image description here

'StartDate' is Start date of quota, 'StartMonthYear' is month+Year of startdate, 'EndDate' is end date of quota , 'QuotaAmount' is quota between startdate and EndDate and TeamName is Team Name for which this Quota assigned to.

Now I how can i generate repeated rows of a particular Quota on monthly basis? Sample output i am looking for is

enter image description here

Here Team T1 is having quota for 3 months so i want 3 different records for each month with start date as month start date and 'End date' as Month End Date.

Note : Quotas always start from 1 day of month and end at last day of month.

Thanks in advance

Upvotes: 1

Views: 1972

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 81950

If you don't have a calendar table, you can use a TVF which generates dynamic date/time ranges

Declare @Date1 date = '2016-01-01'  -- These could be a query for min/max dates
Declare @Date2 date = '2016-12-31' 

Select A.StartDate
      ,StartMonthYear = month(A.StartDate)*10000+Year(A.StartDate)
      ,A.EndDate
      ,B.QuotaAmount
      ,B.TeamName
 From (Select StartDate=RetVal,EndDate=DateAdd(DD,-1,DateAdd(MM,1,RetVal)) From [dbo].[udf-Range-Date](@Date1,@Date2,'MM',1)) A
 Join YourTable B
   on A.StartDate between B.StartDate and B.EndDate
 Order by TeamName,A.StartDate

Returns

enter image description here

The UDF if Needed

CREATE FUNCTION [dbo].[udf-Range-Date] (@R1 datetime,@R2 datetime,@Part varchar(10),@Incr int)
Returns Table
Return (
    with cte0(M)   As (Select 1+Case @Part When 'YY' then DateDiff(YY,@R1,@R2)/@Incr When 'QQ' then DateDiff(QQ,@R1,@R2)/@Incr When 'MM' then DateDiff(MM,@R1,@R2)/@Incr When 'WK' then DateDiff(WK,@R1,@R2)/@Incr When 'DD' then DateDiff(DD,@R1,@R2)/@Incr When 'HH' then DateDiff(HH,@R1,@R2)/@Incr When 'MI' then DateDiff(MI,@R1,@R2)/@Incr When 'SS' then DateDiff(SS,@R1,@R2)/@Incr End),
         cte1(N)   As (Select 1 From (Values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N(N)),
         cte2(N)   As (Select Top (Select M from cte0) Row_Number() over (Order By (Select NULL)) From cte1 a, cte1 b, cte1 c, cte1 d, cte1 e, cte1 f, cte1 g, cte1 h ),
         cte3(N,D) As (Select 0,@R1 Union All Select N,Case @Part When 'YY' then DateAdd(YY, N*@Incr, @R1) When 'QQ' then DateAdd(QQ, N*@Incr, @R1) When 'MM' then DateAdd(MM, N*@Incr, @R1) When 'WK' then DateAdd(WK, N*@Incr, @R1) When 'DD' then DateAdd(DD, N*@Incr, @R1) When 'HH' then DateAdd(HH, N*@Incr, @R1) When 'MI' then DateAdd(MI, N*@Incr, @R1) When 'SS' then DateAdd(SS, N*@Incr, @R1) End From cte2 )

    Select RetSeq = N+1
          ,RetVal = D 
     From  cte3,cte0 
     Where D<=@R2
)
/*
Max 100 million observations -- Date Parts YY QQ MM WK DD HH MI SS
Syntax:
Select * from [dbo].[udf-Range-Date]('2016-10-01','2020-10-01','YY',1) 
Select * from [dbo].[udf-Range-Date]('2016-01-01','2017-01-01','MM',1) 
*/

Upvotes: 0

Related Questions