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]
Advertisement
Answer
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;