请参见下面的代码。对于Underwriter给定月份中存在的每个日期,我都需要该月份中的每个日期以及给定的日期Underwriter。非常感谢
原始表格:
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 |
所需输出:
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 |
请注意,丽贝卡不应该有12/1/2019至12/31/2019的日期
尝试输入的代码
SELECT [Underwriter], ________ AS [Date]
FROM [Table6]
CROSS JOIN [] = [UW_First] Between [UW_Last]
使用Tally,它们在以下操作上确实非常快:
/*
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;
版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。