Generate table without overlapping dateranges

Question

Respected all,

I wanted to generate a StartDate and EndDate without overlapping date ranges without cursor or recursion as I am using Synapse.

I have a table like below called Dispenses

I wanted to create 2 new column StartDate and EndDate where the Dispensed tablets will last that long, there shouldnt be any overlapping date ranges. Then some times the dispenses will be skipped for few weeks too, see DispenseNumber 30 row.

Want the output like below

CREATE TABLE [dbo].Dispenses ON [PRIMARY]
GO
INSERT [dbo].[Dispenses] ([DispenseNumber], [DispensedQty], [TransactionDate]) VALUES (1, 30, CAST(N'2018-09-10' AS Date))
GO
INSERT [dbo].[Dispenses] ([DispenseNumber], [DispensedQty], [TransactionDate]) VALUES (2, 30, CAST(N'2018-10-03' AS Date))
GO
INSERT [dbo].[Dispenses] ([DispenseNumber], [DispensedQty], [TransactionDate]) VALUES (3, 30, CAST(N'2018-11-05' AS Date))
GO
INSERT [dbo].[Dispenses] ([DispenseNumber], [DispensedQty], [TransactionDate]) VALUES (4, 30, CAST(N'2018-12-04' AS Date))
GO
INSERT [dbo].[Dispenses] ([DispenseNumber], [DispensedQty], [TransactionDate]) VALUES (5, 30, CAST(N'2019-01-09' AS Date))
GO
INSERT [dbo].[Dispenses] ([DispenseNumber], [DispensedQty], [TransactionDate]) VALUES (6, 30, CAST(N'2019-02-07' AS Date))
GO
INSERT [dbo].[Dispenses] ([DispenseNumber], [DispensedQty], [TransactionDate]) VALUES (7, 30, CAST(N'2019-03-04' AS Date))
GO
INSERT [dbo].[Dispenses] ([DispenseNumber], [DispensedQty], [TransactionDate]) VALUES (8, 30, CAST(N'2019-03-28' AS Date))
GO
INSERT [dbo].[Dispenses] ([DispenseNumber], [DispensedQty], [TransactionDate]) VALUES (9, 30, CAST(N'2019-04-24' AS Date))
GO
INSERT [dbo].[Dispenses] ([DispenseNumber], [DispensedQty], [TransactionDate]) VALUES (10, 30, CAST(N'2019-05-22' AS Date))
GO
INSERT [dbo].[Dispenses] ([DispenseNumber], [DispensedQty], [TransactionDate]) VALUES (11, 30, CAST(N'2019-06-18' AS Date))
GO
INSERT [dbo].[Dispenses] ([DispenseNumber], [DispensedQty], [TransactionDate]) VALUES (12, 30, CAST(N'2019-07-18' AS Date))
GO
INSERT [dbo].[Dispenses] ([DispenseNumber], [DispensedQty], [TransactionDate]) VALUES (13, 30, CAST(N'2019-08-15' AS Date))
GO
INSERT [dbo].[Dispenses] ([DispenseNumber], [DispensedQty], [TransactionDate]) VALUES (14, 30, CAST(N'2019-09-12' AS Date))
GO
INSERT [dbo].[Dispenses] ([DispenseNumber], [DispensedQty], [TransactionDate]) VALUES (15, 30, CAST(N'2019-10-23' AS Date))
GO
INSERT [dbo].[Dispenses] ([DispenseNumber], [DispensedQty], [TransactionDate]) VALUES (16, 30, CAST(N'2019-11-27' AS Date))
GO
INSERT [dbo].[Dispenses] ([DispenseNumber], [DispensedQty], [TransactionDate]) VALUES (17, 30, CAST(N'2019-12-27' AS Date))
GO
INSERT [dbo].[Dispenses] ([DispenseNumber], [DispensedQty], [TransactionDate]) VALUES (18, 30, CAST(N'2020-01-27' AS Date))
GO
INSERT [dbo].[Dispenses] ([DispenseNumber], [DispensedQty], [TransactionDate]) VALUES (19, 30, CAST(N'2020-02-28' AS Date))
GO
INSERT [dbo].[Dispenses] ([DispenseNumber], [DispensedQty], [TransactionDate]) VALUES (20, 30, CAST(N'2020-03-26' AS Date))
GO
INSERT [dbo].[Dispenses] ([DispenseNumber], [DispensedQty], [TransactionDate]) VALUES (21, 30, CAST(N'2020-04-23' AS Date))
GO
INSERT [dbo].[Dispenses] ([DispenseNumber], [DispensedQty], [TransactionDate]) VALUES (22, 30, CAST(N'2020-05-18' AS Date))
GO
INSERT [dbo].[Dispenses] ([DispenseNumber], [DispensedQty], [TransactionDate]) VALUES (23, 30, CAST(N'2020-06-18' AS Date))
GO
INSERT [dbo].[Dispenses] ([DispenseNumber], [DispensedQty], [TransactionDate]) VALUES (24, 30, CAST(N'2020-07-20' AS Date))
GO
INSERT [dbo].[Dispenses] ([DispenseNumber], [DispensedQty], [TransactionDate]) VALUES (25, 30, CAST(N'2020-08-17' AS Date))
GO
INSERT [dbo].[Dispenses] ([DispenseNumber], [DispensedQty], [TransactionDate]) VALUES (26, 30, CAST(N'2020-09-16' AS Date))
GO
INSERT [dbo].[Dispenses] ([DispenseNumber], [DispensedQty], [TransactionDate]) VALUES (27, 30, CAST(N'2020-10-14' AS Date))
GO
INSERT [dbo].[Dispenses] ([DispenseNumber], [DispensedQty], [TransactionDate]) VALUES (28, 30, CAST(N'2020-11-17' AS Date))
GO
INSERT [dbo].[Dispenses] ([DispenseNumber], [DispensedQty], [TransactionDate]) VALUES (29, 30, CAST(N'2020-12-17' AS Date))
GO
INSERT [dbo].[Dispenses] ([DispenseNumber], [DispensedQty], [TransactionDate]) VALUES (30, 30, CAST(N'2021-03-03' AS Date))
GO
INSERT [dbo].[Dispenses] ([DispenseNumber], [DispensedQty], [TransactionDate]) VALUES (31, 30, CAST(N'2021-04-01' AS Date))
GO
INSERT [dbo].[Dispenses] ([DispenseNumber], [DispensedQty], [TransactionDate]) VALUES (32, 30, CAST(N'2021-04-29' AS Date))
GO
INSERT [dbo].[Dispenses] ([DispenseNumber], [DispensedQty], [TransactionDate]) VALUES (33, 30, CAST(N'2021-05-25' AS Date))
GO
INSERT [dbo].[Dispenses] ([DispenseNumber], [DispensedQty], [TransactionDate]) VALUES (34, 30, CAST(N'2021-07-06' AS Date))
GO
INSERT [dbo].[Dispenses] ([DispenseNumber], [DispensedQty], [TransactionDate]) VALUES (35, 30, CAST(N'2021-08-04' AS Date))
GO
INSERT [dbo].[Dispenses] ([DispenseNumber], [DispensedQty], [TransactionDate]) VALUES (36, 30, CAST(N'2021-09-15' AS Date))
GO
INSERT [dbo].[Dispenses] ([DispenseNumber], [DispensedQty], [TransactionDate]) VALUES (37, 30, CAST(N'2021-10-20' AS Date))
GO
INSERT [dbo].[Dispenses] ([DispenseNumber], [DispensedQty], [TransactionDate]) VALUES (38, 30, CAST(N'2021-11-22' AS Date))
GO

Answer 1

OK, so here is a solution based on a recursive CTE. Please note that I don't have access to Synapse myself, I don't know whether this will actually run on Synapse.

; WITH CTE AS (
   SELECT DispenseNumber, DispensedQty, TransactionDate,
          StartDate = TransactionDate, EndDate = DATEADD(DAY, DispensedQty-1, TransactionDate)
   FROM   Dispenses
   WHERE  DispenseNumber = 1
   UNION ALL
   SELECT D.DispenseNumber, D.DispensedQty, D.TransactionDate,
          CASE WHEN CTE.EndDate >= D.TransactionDate
               THEN DATEADD(DAY, 1, CTE.EndDate)
               ELSE D.TransactionDate
          END,
          CASE WHEN CTE.EndDate >= D.TransactionDate
               THEN DATEADD(DAY, D.DispensedQty, CTE.EndDate)
               ELSE DATEADD(DAY, D.DispensedQty-1, D.TransactionDate)
          END
   FROM   Dispenses D
   JOIN   CTE ON D.DispenseNumber = CTE.DispenseNumber + 1
)
SELECT *
FROM   CTE
ORDER  BY DispenseNumber
OPTION (MAXRECURSION 0)

Answer 2

Thank you for asking

EndDate = dateadd(dd,DispensedQty,TransactionDate) but then it is overlapping dates on below rows, so if there is an overlapping dates it has to move subsequently until the end of the series.

Answer 3

Let me explain more on the requirement, this data is 30 tablets dispensed on a TransactionDate, so based on the transactiondate it will last 30 days, but then the next dispense will happen few days before/after. So at the end, I want each of the dispense when will start and end. At times the dispense will be off for few days or few weeks, that is why we have to check the transactiondate and put the startdate should be greater than the transactiondate.

Answer 4

I was using something like below, but then when the transactiondate is greater, that is when I am getting lost

ALTER FUNCTION [dbo].[GenerateDateRange]
(@StartDate AS DATE,
@EndDate AS DATE,
@Interval AS INT
)
RETURNS @Dates TABLE(DateValue DATE)
AS
BEGIN
DECLARE @CUR_DATE DATE
SET @CUR_DATE = @StartDate
WHILE @CUR_DATE <= @EndDate BEGIN
INSERT INTO @Dates VALUES(@CUR_DATE)
SET @CUR_DATE = DATEADD(DAY, @Interval, @CUR_DATE)
END
RETURN;
END;

select *,dateadd(dd,DispensedQty,TransactionDate) from Dispenses

if object_id('tempdb..#t') is not null
drop table #t
select *,ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) as rn,ROW_NUMBER() OVER (partition by dispensenumber ORDER BY (SELECT NULL)) as rn1

from Dispenses d
cross apply dbo.GenerateDateRange(transactiondate,dateadd(dd,dispensedqty,transactiondate),1)

select min(pod_date),max(pod_date),dispensenumber,datediff(dd,min(pod_date),max(pod_date))
from #t1
group by dispensenumber
order by 3

Answer 5

Hi @Ganesh Kaliaperumal ，

SELECT *,EndDate = dateadd(dd,DispensedQty,TransactionDate)   
FROM [dbo].[Dispenses]  

Output:

The EndDate obtained by the rule you provided is not consistent with the expected output.