Share via

Find first remaining date range record within main date range using single query

Ashok Mistry 1 Reputation point
2022-05-23T12:15:40.727+00:00

I have two tables in SQL Server database.

1 Rental
This table stores rental contract with its duration
204704-image1.png

CREATE TABLE [dbo].Rental ON [PRIMARY]
GO

SET IDENTITY_INSERT [dbo].[Rental] ON
GO
INSERT [dbo].[Rental] ([RentalId], [RentalContract], [StartDate], [EndDate]) VALUES (1, N'C1', CAST(N'2022-01-01' AS Date), CAST(N'2022-01-31' AS Date))
GO
INSERT [dbo].[Rental] ([RentalId], [RentalContract], [StartDate], [EndDate]) VALUES (2, N'C2', CAST(N'2022-02-01' AS Date), CAST(N'2022-02-15' AS Date))
GO
INSERT [dbo].[Rental] ([RentalId], [RentalContract], [StartDate], [EndDate]) VALUES (3, N'C3', CAST(N'2022-03-01' AS Date), CAST(N'2022-03-31' AS Date))
GO
INSERT [dbo].[Rental] ([RentalId], [RentalContract], [StartDate], [EndDate]) VALUES (4, N'C4', CAST(N'2022-04-01' AS Date), CAST(N'2022-04-30' AS Date))
GO
SET IDENTITY_INSERT [dbo].[Rental] OFF
GO

2 RentalInvoice
This table stores invoices created for the specified rental contract. The invoice also will be for specific duration.
204694-image2.png

CREATE TABLE [dbo].RentalInvoice ON [PRIMARY]
GO

SET IDENTITY_INSERT [dbo].[RentalInvoice] ON
GO
INSERT [dbo].[RentalInvoice] ([InvoiceId], [InvoiceNo], [InvoiceDate], [RentalId], [StartDate], [EndDate]) VALUES (1, N'A1', CAST(N'2022-01-15' AS Date), 1, CAST(N'2022-01-10' AS Date), CAST(N'2022-01-31' AS Date))
GO
INSERT [dbo].[RentalInvoice] ([InvoiceId], [InvoiceNo], [InvoiceDate], [RentalId], [StartDate], [EndDate]) VALUES (2, N'A2', CAST(N'2022-02-06' AS Date), 2, CAST(N'2022-02-02' AS Date), CAST(N'2022-02-06' AS Date))
GO
INSERT [dbo].[RentalInvoice] ([InvoiceId], [InvoiceNo], [InvoiceDate], [RentalId], [StartDate], [EndDate]) VALUES (3, N'A3', CAST(N'2022-03-11' AS Date), 3, CAST(N'2022-03-01' AS Date), CAST(N'2022-03-10' AS Date))
GO
INSERT [dbo].[RentalInvoice] ([InvoiceId], [InvoiceNo], [InvoiceDate], [RentalId], [StartDate], [EndDate]) VALUES (4, N'A4', CAST(N'2022-04-15' AS Date), 4, CAST(N'2022-04-01' AS Date), CAST(N'2022-04-15' AS Date))
GO
INSERT [dbo].[RentalInvoice] ([InvoiceId], [InvoiceNo], [InvoiceDate], [RentalId], [StartDate], [EndDate]) VALUES (6, N'A4', CAST(N'2022-04-25' AS Date), 4, CAST(N'2022-04-20' AS Date), CAST(N'2022-04-25' AS Date))
GO
SET IDENTITY_INSERT [dbo].[RentalInvoice] OFF
GO

I want a pending invoice list for each rental contract along with first pending date range for which invoice is to be created.

Something like

RentalContract, Pending Start Date, Pending End Date
204705-image3.png

Is it possible to get the output in single query?

I have tried as following:

SELECT r.*,
CASE
WHEN inv.MinInvStartDate IS NULL THEN r.StartDate
WHEN r.StartDate < inv.MinInvStartDate THEN r.StartDate
WHEN r.StartDate = inv.MinInvStartDate AND inv.MaxInvEndDate < r.EndDate THEN DATEADD(day, 1, inv.MaxInvEndDate)
ELSE NULL
END AS PendingStartDate,
CASE
WHEN inv.MaxInvEndDate IS NULL THEN r.EndDate
WHEN r.StartDate < inv.MinInvStartDate THEN DATEADD(day, -1, inv.MinInvStartDate)
WHEN r.StartDate = inv.MinInvStartDate AND inv.MaxInvEndDate < r.EndDate THEN r.EndDate
ELSE NULL
END AS PendingEndDate
from Rental r
LEFT JOIN (
SELECT RentalId, MIN(StartDate) AS MinInvStartDate, MAX(EndDate) AS MaxInvEndDate
from RentalInvoice
GROUP BY RentalId) inv
ON r.RentalId = inv.RentalId

The above sql returns proper PendingStartDate, PendingEndDate for RentalId = 1, 2 & 3. But for RentalId = 4, it is giving '2022-04-26' as PendingStartDate and '2022-04-30' as PendingEndDate... Actually I need the first pending duration, i.e. '2022-04-16' as PendingStartDate and '2022-04-19' as PendingEndDate.

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,559 questions
0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Tom Cooper 8,466 Reputation points
    2022-05-23T16:15:23.507+00:00

    This is a form of an "Islands and Gaps" problem. If you're not familiar with them, just google that term. I also use a calendar table. If you don't have one, you might want to create a permanent one, they have many uses. In this case I just created a rudimental temporary one. 

    --Create the calendar table  
Declare @Calendar Table(TheDate date);  
;With cte As   
(Select Cast('20210101' As date) As TheDate  
Union All Select DateAdd(day, 1, TheDate) From cte Where TheDate < '20221231')  
Insert Into @Calendar  
Select TheDate From cte  
Option (MaxRecursion 0);  
  
-- Get the dates which are in dbo.Rental but not in dbo.RentalInvoice  
;With cteMissingDates As  
(Select r.RentalId, r.RentalContract, c.TheDate  
From dbo.Rental r  
Inner Join @Calendar c On c.TheDate Between r.StartDate And r.EndDate  
Except  
Select ri.RentalId, r.RentalContract, c.TheDate  
From dbo.RentalInvoice ri  
Inner Join dbo.Rental r On ri.RentalId = r.RentalId  
Inner Join @Calendar c On c.TheDate Between ri.StartDate And ri.EndDate),  
  
-- Find the islands  
cteIslands As  
(Select RentalId, RentalContract, TheDate,  
  DateDiff(day, '19000101', TheDate) - Row_Number() Over(Partition By RentalID, RentalContract Order By TheDate) As Island  
From cteMissingDates)  
  
--Return the dates in the first island for each RentalID, RentalContract  
Select i.RentalId, i.RentalContract, Min(i.TheDate) As StartDate, Max(i.TheDate) As EndDate  
From cteIslands i  
Cross Apply (Select Min(Island) As MinIsland From cteIslands i1 Where i.RentalId = i1.RentalId And i.RentalContract = i1.RentalContract) As a  
Where i.Island = a.MinIsland  
Group By i.RentalId, i.RentalContract  
Order By i.RentalId, i.RentalContract;

    Tom

    1 person found this answer helpful.
  2. Bert Zhou-msft 3,421 Reputation points
    2022-05-24T02:51:00.143+00:00

    Hi，@Ashok Mistry
    Welcome to Microsoft T-SQL Q&A Forum!

    Please check this: 

    ;WITH cte as  
(  
SELECT r.*,  
CASE  
WHEN inv.MinInvStartDate IS NULL THEN r.StartDate  
WHEN r.StartDate < inv.MinInvStartDate THEN r.StartDate  
WHEN r.StartDate = inv.MinInvStartDate   
AND inv.MaxInvEndDate < r.EndDate   
THEN DATEADD(day, 1, inv.MaxInvEndDate)  
ELSE NULL  
END AS PendingStartDate,  
  
CASE  
WHEN inv.MaxInvEndDate IS NULL THEN r.EndDate  
WHEN r.StartDate < inv.MinInvStartDate   
THEN DATEADD(day, -1, inv.MinInvStartDate)  
WHEN r.StartDate = inv.MinInvStartDate   
AND inv.MaxInvEndDate < r.EndDate THEN r.EndDate  
ELSE NULL  
END AS PendingEndDate  
  
from Rental r  
LEFT JOIN   
(  
SELECT RentalId, StartDate AS MinInvStartDate, EndDate AS MaxInvEndDate  
from RentalInvoice  
) inv  
ON r.RentalId = inv.RentalId  
)  
  
select  RentalId,RentalContract, StartDate , EndDate ,  
max(PendingStartDate) PendingStartDate,min(PendingEndDate)PendingEndDate  
from cte  
group by RentalId,RentalContract, StartDate , EndDate

    204921-image.png

    Best regards,
    Bert Zhou

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    1 person found this answer helpful.
  3. Jingyang Li 5,891 Reputation points
    2022-05-26T15:10:11+00:00 
    CREATE TABLE [dbo].[RentalInvoice](
[InvoiceId] [int] IDENTITY(1,1) NOT NULL,
[InvoiceNo] [varchar](15) NOT NULL,
[InvoiceDate] [date] NOT NULL,
[RentalId] [int] NOT NULL,
[StartDate] [date] NOT NULL,
[EndDate] [date] NOT NULL,
CONSTRAINT [PK_RentalInvoice] PRIMARY KEY CLUSTERED
(
[InvoiceId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

SET IDENTITY_INSERT [dbo].[RentalInvoice] ON
GO
INSERT [dbo].[RentalInvoice] ([InvoiceId], [InvoiceNo], [InvoiceDate], [RentalId], [StartDate], [EndDate]) VALUES (1, N'A1', CAST(N'2022-01-15' AS Date), 1, CAST(N'2022-01-10' AS Date), CAST(N'2022-01-31' AS Date))
GO
INSERT [dbo].[RentalInvoice] ([InvoiceId], [InvoiceNo], [InvoiceDate], [RentalId], [StartDate], [EndDate]) VALUES (2, N'A2', CAST(N'2022-02-06' AS Date), 2, CAST(N'2022-02-02' AS Date), CAST(N'2022-02-06' AS Date))
GO
INSERT [dbo].[RentalInvoice] ([InvoiceId], [InvoiceNo], [InvoiceDate], [RentalId], [StartDate], [EndDate]) VALUES (3, N'A3', CAST(N'2022-03-11' AS Date), 3, CAST(N'2022-03-01' AS Date), CAST(N'2022-03-10' AS Date))
GO
INSERT [dbo].[RentalInvoice] ([InvoiceId], [InvoiceNo], [InvoiceDate], [RentalId], [StartDate], [EndDate]) VALUES (4, N'A4', CAST(N'2022-04-15' AS Date), 4, CAST(N'2022-04-01' AS Date), CAST(N'2022-04-15' AS Date))
GO
INSERT [dbo].[RentalInvoice] ([InvoiceId], [InvoiceNo], [InvoiceDate], [RentalId], [StartDate], [EndDate]) VALUES (6, N'A4', CAST(N'2022-04-25' AS Date), 4, CAST(N'2022-04-20' AS Date), CAST(N'2022-04-25' AS Date))
GO
SET IDENTITY_INSERT [dbo].[RentalInvoice] OFF
GO

CREATE TABLE [dbo].[Rental](
[RentalId] [int] IDENTITY(1,1) NOT NULL,
[RentalContract] [varchar](50) NOT NULL,
[StartDate] [date] NOT NULL,
[EndDate] [date] NOT NULL,
CONSTRAINT [PK_Rental] PRIMARY KEY CLUSTERED
(
[RentalId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

SET IDENTITY_INSERT [dbo].[Rental] ON
GO
INSERT [dbo].[Rental] ([RentalId], [RentalContract], [StartDate], [EndDate]) VALUES (1, N'C1', CAST(N'2022-01-01' AS Date), CAST(N'2022-01-31' AS Date))
GO
INSERT [dbo].[Rental] ([RentalId], [RentalContract], [StartDate], [EndDate]) VALUES (2, N'C2', CAST(N'2022-02-01' AS Date), CAST(N'2022-02-15' AS Date))
GO
INSERT [dbo].[Rental] ([RentalId], [RentalContract], [StartDate], [EndDate]) VALUES (3, N'C3', CAST(N'2022-03-01' AS Date), CAST(N'2022-03-31' AS Date))
GO
INSERT [dbo].[Rental] ([RentalId], [RentalContract], [StartDate], [EndDate]) VALUES (4, N'C4', CAST(N'2022-04-01' AS Date), CAST(N'2022-04-30' AS Date))
GO
SET IDENTITY_INSERT [dbo].[Rental] OFF
GO

;WITH Num1 (n) AS (
SELECT 1 as n
UNION ALL SELECT n+1 as n
FROM Num1 Where n <101),
Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y), 
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num2)

,mycteRental as ( 
 Select  RentalId,[RentalContract], dateadd(day,n-1,StartDate) dt  
 from [Rental]
 Cross apply (select n from nums ) d(n)
 where  dateadd(day,n-1,StartDate)  <= [EndDate]
)

,mycteRentalInvoice as (
  Select  RentalId, dateadd(day,n-1,StartDate) dt  
  from [RentalInvoice]
 Cross apply (select n from nums ) d(n)
 where  dateadd(day,n-1,StartDate)  <= [EndDate]
)
,mycteFindIsland as (
select *,DATEADD(DAY, - ROW_NUMBER() OVER(PARTITION BY RentalId ORDER BY dt), dt) AS grp 
from mycteRental m1
where not exists 
(select 1 from mycteRentalInvoice m2 where m1.RentalId=m2.RentalId and m1.dt=m2.dt) 
)
,mycteFindFirstIsland as (
select *,dense_rank() OVER(PARTITION BY RentalId  Order by grp) dnk 
from mycteFindIsland
)
select RentalId,RentalContract, min(dt) StartDate, max(dt) EndDate
from mycteFindFirstIsland
where dnk=1 -- get first
group by RentalId,RentalContract


drop table [dbo].[RentalInvoice],   [dbo].[Rental]
    0 comments