sql - 使用 SQL Server 每周创建日历表
问题描述
我正在使用 Microsoft SQL Server 2012,我想创建下表(从 2018-12-02 到 2019-08-31):
Clinic_code Clinic_name Day_start Day_end Weeks_passed
-----------------------------------------------------------------------------
A123 NAME1 2018-12-02 2018-12-08 1
A124 NAME2 2018-12-02 2018-12-08 1
A125 NAME3 2018-12-02 2018-12-08 1
[...]
A123 NAME1 2018-12-09 2018-12-15 2
A124 NAME2 2018-12-09 2018-12-15 2
A125 NAME3 2018-12-09 2018-12-15 2
[...]
A123 NAME1 2018-12-16 2018-12-22 3
A124 NAME2 2018-12-16 2018-12-22 3
A125 NAME3 2018-12-16 2018-12-22 3
我正在使用以下代码:
DECLARE @fromstartdate date = '2018-12-02'
DECLARE @fromenddate date = '2018-12-08'
SELECT
#MyTable.*, B.Day_start, C.Day_end,
DATEDIFF(day, MIN(B.Day_start) OVER (),
B.Day_start) + 1 AS Weeks_passed
FROM
#MyTable
CROSS APPLY
(SELECT TOP (DATEDIFF(DAY, @fromstartdate, DATEADD(Month, 9, @fromstartdate)))
Day_start = CONVERT(date, DATEADD(DAY, -1 + 7 * ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), @fromstartdate))
FROM master..spt_values n1) B
CROSS APPLY
(SELECT TOP (DATEDIFF(DAY, @fromenddate, DATEADD(Month, 9, @fromenddate)))
Day_end = CONVERT(date, DATEADD(DAY, -1 + 7 * ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), @fromenddate))
FROM master..spt_values n1) C
但我没有得到第一个@fromstartdate
,@fromenddate
而且它已经超过了 2019 年 8 月 31 日。你能告诉我我做错了什么吗?
解决方案
我会选择创建递归公用表表达式并将其加入您的表的方法。使用 CTE,您可以创建日期列表并通过在每周的每个开始添加 6 天来定义日期范围。通过的周数按增量计算。
DECLARE @fromstartdate date = '2018-12-02'
DECLARE @fromenddate date = '2019-08-31'
;WITH cteDateList AS
(
SELECT @fromstartdate AS Day_start, DATEADD(D, 6, @fromstartdate) AS Day_end, 1 AS Weeks_passed
UNION ALL
SELECT DATEADD(D, 7, Day_start), DATEADD(D, 6, Day_end), Weeks_passed + 1 FROM cteDateList
WHERE DATEADD(D, 7, Day_start) <= @fromenddate
)
SELECT mt.clinic_code, mt.clinic_name, dl.* FROM MyTable mt
CROSS APPLY (SELECT * FROM cteDateList) dl
ORDER BY dl.Day_start, mt.clinic_code;
推荐阅读
- aws-lambda - 如何在 Node.js/AWS Lambda 中使用我的函数
- c# - 使用 C# API 屏蔽 Twilio 呼叫电话号码
- ansible - ec2_ami ansible 模块导致 AWS 实例无法访问
- here-api - HERE 卡车的兴趣点 (POI)
- rsyslog - rsyslog 配置转发所有消息,但不转发 kern 工具
- javascript - 获取js触发的请求
- django-rest-framework - 生成密钥并将其保存到模型 Django Rest Framework
- symfony - 更新 composer.json 后未找到 PHPUnit 特征
- google-maps-api-3 - 如何将已解析的 kml 文件中的扩展数据存储到变量中
- java - 如何使用 jfilechooser 将多个 Excel 表导入数据库?