CODEWITHSUNDEEP
sqlsql-server
Tiffany C
Tiffany C

Reputation: 63

SQL. full range of dates for every user

Please see code below. For every Underwriter that exists that is in a given month, I need every single date in the month and the given Underwriter. Thank you so much

Original Table:

CREATE TABLE [Table6] ([Underwriter] nvarchar(10), [UW_First] date, [UW_Last] date)

INSERT INTO [Table6] VALUES ('Kim', '11/1/2019', '11/30/2019')
INSERT INTO [Table6] VALUES ('Kim', '12/1/2019', '12/31/2019')
INSERT INTO [Table6] VALUES ('Sarah', '11/1/2019', '11/30/2019')
INSERT INTO [Table6] VALUES ('Sarah', '12/1/2019', '12/31/2019')
INSERT INTO [Table6] VALUES ('Rebecca', '11/1/2019', '11/30/2019')

Underwriter | UW_First   | UW_Last     |
------------------------------------------
Kim         | 11/1/2019  | 11/30/2019  |
Kim         | 12/1/2019  | 12/31/2019  |
Sarah       | 11/1/2019  | 11/30/2019  |
Sarah       | 12/1/2019  | 12/31/2019  |
Rebecca     | 11/1/2019  | 11/30/2019  |

Desired Output:

Underwriter    | Date       |
------------------------------
Kim            | 11/1/2019  |
Kim            | 11/2/2019  |
Kim             ...
Kim            | 11/30/2019 |
Kim            | 12/1/2019  |
Kim             ...
Kim            | 12/30/2019 |
Sarah          | 11/1/2019  |
Sarah          | 11/2/2019  |
Sarah           ...
Sarah          | 11/30/2019 |
Sarah          | 12/1/2019  |
Sarah           ...
Sarah          | 12/30/2019 |
Rebecca        | 11/1/2019  |
Rebecca        | 11/2/2019  |
Rebecca         ...
Rebecca        | 11/30/2019 |

Please note that Rebecca should not have dates for 12/1/2019 to 12/31/2019

Attempted Code

SELECT [Underwriter], ________ AS [Date]
FROM [Table6]
CROSS JOIN [] = [UW_First] Between [UW_Last]

Upvotes: 0

Views: 85

Answers (2)

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

You need recursive cte : 

with cte as (
    select Underwriter, UW_First, UW_Last
    from Table6 t
    union all
    select Underwriter, dateadd(day, 1, c.UW_First), UW_Last
    from cte c
    where dateadd(day, 1, UW_First) <= UW_Last
)

select c.Underwriter, c.UW_First as Date
from cte c
order by Underwriter, UW_First;

Upvotes: 1

Thom A
Thom A

Reputation: 95905

Use a Tally, they are really fast at such operations:

/*
Underwriter | UW_First   | UW_Last     |
------------------------------------------
Kim         | 11/1/2019  | 11/30/2019  |
Kim         | 12/1/2019  | 12/31/2019  |
Sarah       | 11/1/2019  | 11/30/2019  |
Sarah       | 12/1/2019  | 12/31/2019  |
Rebecca     | 11/1/2019  | 11/30/2019  |
*/
--Create sample table

CREATE TABLE dbo.YourTable (Underwriter varchar(10),
                            UW_First date,
                            UW_Last date);
GO

--Insert sample data    
INSERT INTO dbo.YourTable (Underwriter,UW_First,UW_Last)
SELECT RTRIM(U),
       CONVERT(date,F,101),
       CONVERT(date,L,101)
FROM (VALUES('Kim    ','11/1/2019','11/30/2019'),
            ('Kim    ','12/1/2019','12/31/2019'),
            ('Sarah  ','11/1/2019','11/30/2019'),
            ('Sarah  ','12/1/2019','12/31/2019'),
            ('Rebecca','11/1/2019','11/30/2019'))V(U, F, L);
GO

--Start creating the Tally    
WITH N AS(
    SELECT N
    FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
--Create the tally, using Cross Joins.
Tally AS(
    SELECT TOP (SELECT MAX(DATEDIFF(DAY,UW_First,UW_Last))+1 FROM dbo.YourTable)
           ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1 AS I
    FROM N N1, N N2, N N3) --1000 days
--And then make the data set
SELECT YT.Underwriter,
       DATEADD(DAY,T.I,YT.UW_First) AS [Date]
FROM dbo.YourTable YT
     JOIN Tally T ON T.I <= DATEDIFF(DAY,YT.UW_First,YT.UW_Last)
ORDER BY YT.Underwriter,
         [Date];
GO

DROP TABLE dbo.YourTable;

Upvotes: 1

Related Questions