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:
x
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 tableCREATE 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 setSELECT 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];GODROP TABLE dbo.YourTable;