-1

I want to divide the quantity value into multiple rows divided by number of months from start date & end date column. Each row should have start date and end date of the month. I also want remaining quantity based on previous value. Below is my sample input and output.

Sample input& output

isrikanthd
  • 53
  • 6

1 Answers1

-1

Split column value into multiple rows using SQL

SET DATEFORMAT dmy;

DECLARE @dateranges TABLE( BegDate DATE, EndDate DATE,Qty int)

INSERT INTO @dateranges( BegDate, EndDate,qty) VALUES( '01-01-2023', '31-05-2023', 2000)

;WITH CTE AS

(

--initial part

SELECT  BegDate AS StartOfMonth, DATEADD(DD, -1, DATEADD(MM, 1, BegDate)) AS EndOfMonth, EndDate,qty/datediff(month,begdate,dateadd(month,1,enddate)) as quantity, qty - qty/datediff(month,begdate,dateadd(month,1,enddate)) as rem_quantity
FROM @dateranges

-- recursive part
UNION ALL

SELECT  DATEADD(MM, 1, StartOfMonth) AS StartOfMonth, DATEADD(DD, -1, DATEADD(MM, 2, StartOfMonth)) AS EndOfMonth, EndDate, Quantity, Rem_quantity - Quantity as Rem_quantity
FROM CTE 

WHERE DATEADD(MM, 1, StartOfMonth)< EndDate

)

SELECT StartOfMonth, EndOfMonth, Quantity, rem_quantity FROM CTE

Leela Krishna
  • 1