I have table which having data of some date ranges. when user select start date and end date then the result set will be like all date ranges between that 2 dates also all missing date ranges between that 2 dates.
For Example:
DateRangesTable
ID| fromdate | todate |
----------------------------
1 | 5-May-21 | 10-May-21 |
2 | 17-May-21 | 25-May-21 |
this is my main table ,I mention below all result set which I wanted with above table
if user select : 5-May-2021 to 25-May-2021
Expected Result :
ID| fromdate | todate |
----------------------------
1 | 5-May-21 | 10-May-21 |
0 | 11-May-21 | 16-May-21 |
2 | 17-May-21 | 25-May-21 |
if user select : 6-May-2021 to 23-May-2021
Expected Result :
ID| fromdate | todate |
-----------------------------
1 | 6-May-21 | 10-May-21 |
0 | 11-May-21 | 16-May-21 |
2 | 17-May-21 | 23-May-21 |
if user select : 1-May-2021 to 28-May-2021
Expected Result :
ID| fromdate | todate |
----------------------------
1 | 1-May-21 | 4-May-21 |
1 | 5-May-21 | 10-May-21 |
0 | 11-May-21 | 16-May-21 |
2 | 17-May-21 | 25-May-21 |
2 | 26-May-21 | 28-May-21 |
Here some question which is not to similar but try to find:
SQL how to write a query that return missing date ranges?
Thanks in advance.
A note, I am assuming here that the expected results for your final expected results is wrong here as it doesn't match the other 2. The last and first rows in the expected results for the last set both have a value for ID that isn't 0, but no explanation of why they do is given. I therefore assume the value should be 0 like the row in the "middle".
To do this, I use a Tally to get all the dates between the date range you need; the Tally is limited to 1,000 rows, a little shy of 3 years, but you can cross join to N more if you need more rows. I then use that tally to create an inline calendar table. Next I LEFT JOIN that calendar to your data, and use a gaps and island method to put the values into groups. Finally I then aggregate on those groups, getting the MIN and MAX date in each one:
USE Sandbox;
GO
CREATE TABLE dbo.YourTable (ID int,
FromDate date,
ToDate date);
INSERT INTO dbo.YourTable
VALUES(1,'20210505','20210510'),
(2,'20210517','20210525');
GO
DECLARE @StartDate date = '20210501',
@EndDate date = '20210528';
WITH N AS(
SELECT N
FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
Tally AS(
SELECT 0 AS I
UNION ALL
SELECT TOP (DATEDIFF(DAY, @StartDate, @EndDate))
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS I
FROM N N1, N N2, N N3), --1000 days
Dates AS(
SELECT DATEADD(DAY, T.I, @StartDate) AS [Date],
T.I
FROM Tally T),
Grps AS(
SELECT D.[Date],
YT.ID,
D.I - ROW_NUMBER() OVER (PARTITION BY ID ORDER BY D.[Date]) AS Grp
FROM Dates D
LEFT JOIN dbo.YourTable YT ON D.[Date] >= YT.FromDate AND D.[Date] <= YT.ToDate)
SELECT ISNULL(MAX(G.ID),0) AS ID,
MIN(G.[Date]) AS FromDate,
MAX(G.[Date]) AS ToDate
FROM Grps G
GROUP BY G.Grp
ORDER BY FromDate ASC;
GO
DROP TABLE dbo.YourTable;