sql-server T-SQL为date和getdate之间的周生成行

bsxbgnwa 于 2022-10-31 发布在其他

关注(0)|答案(2)|浏览(137)

我有以下数据样本：
| 识别码|报名日期|目标日期|
| - -|- -|- -|
| ID 238型|2022年9月6日|2022年9月6日|
| ID 238型|2022年9月6日|2022年9月9日|
| ID 238型|2022年9月6日|2022年9月11日|
| ID 636型|2022年9月30日|2022年9月30日|
| ID 636型|2022年9月30日|2022年10月3日|
| ID 636型|2022年9月30日|2022年10月5日|
背景：在每个ID的第3个和最后一个TargetDate之后，我需要为截至今天的每周TargetDate生成行。
我想为每个Id取MAX目标日期，然后在这个MAX目标日期和GETDATE（）之间的每个后续周添加新行。
理想的输出（粗体记录是我想添加的行）-我知道原始的目标日期不在我的查询中。
| 识别码|报名日期|目标日期|
| - -|- -|- -|
| ID 238型|2022年9月6日|2022年9月6日|
| ID 238型|2022年9月6日|2022年9月9日|
| ID 238型|2022年9月6日|2022年9月11日|
| ID 238型|2022年9月6日|2022年9月18日**|
| ID 238型|2022年9月6日|2022年9月25日**|
| ID 238型|2022年9月6日|2022年10月2日|
| ID 238型|2022年9月6日|2022年10月9日|
| ID 238型|2022年9月6日|2022年10月16日|
| ID 238型|2022年9月6日|2022年10月23日**|
| ID 636型|2022年9月30日|2022年9月30日|
| ID 636型|2022年9月30日|2022年10月3日|
| ID 636型|2022年9月30日|2022年10月5日|
| ID 636型|2022年9月30日|2022年10月12日|
| ID 636型|2022年9月30日|2022年10月19日|
我尝试了下面的递归CTE，但并不完全在那里。

;WITH cte AS 
(
SELECT [Id], [EntryDate], MAX([Target Day]) as [TargetDate]
FROM [Base_List]
GROUP BY  [Id], [EntryDate]

UNION ALL

SELECT cte.[Id], x.[EntryDate], CAST(DATEADD(week,1,[TargetDate]) as date)
FROM cte

INNER JOIN [Base_List] as x
    ON x.[Id] = cte.[Id]
    AND [TargetDate] < GETDATE()

)

SELECT [Id], [EntryDate], [TargetDate]
FROM cte
OPTION (MAXRECURSION 0)

sql-server

来源：https://stackoverflow.com/questions/74098615/t-sql-generate-rows-for-weeks-between-date-and-getdate

2条答案

按热度按时间

x6yk4ghg1#

with cal (dt) as 
(
               select min(TargetDate) from t
    union all  select dateadd(day, 1, dt) from cal where dt <= getdate()
)

select   id
        ,EntryDate
        ,coalesce(dt, TargetDate) as TargetDate

from    (
         select   *
                 ,max(TargetDate) over(partition by id) as max_TargetDate 
         from     t
        ) t 
        left join    cal c 
        on           max_TargetDate = TargetDate
                 and c.dt >= t.max_TargetDate 
                 and datediff(day, max_TargetDate, dt)%7 = 0

option (maxrecursion 0)

| 标识符|报名日期|目标日期|
| - -|- -|- -|
| ID 238型|2022年9月6日|2022年9月6日|
| ID 238型|2022年9月6日|2022年9月9日|
| ID 238型|2022年9月6日|2022年9月11日|
| ID 238型|2022年9月6日|2022年9月18日|
| ID 238型|2022年9月6日|2022年9月25日|
| ID 238型|2022年9月6日|2022年10月2日|
| ID 238型|2022年9月6日|2022年10月9日|
| ID 238型|2022年9月6日|2022年10月16日|
| ID 636型|2022年9月30日|2022年9月30日|
| ID 636型|2022年9月30日|2022年10月3日|
| ID 636型|2022年9月30日|2022年10月5日|
| ID 636型|2022年9月30日|2022年10月12日|
Fiddle

赞(0）回复(0）举报 2022-10-31

ioekq8ef2#

在以下解决方案中，我们利用split_string并结合replicate来生成新记录。

select   id
        ,EntryDate
        ,dateadd(day, 7*(row_number() over(partition by id, TargetDate order by (select null))-1), TargetDate) as TargetDate 
from    (
         select   *
                 ,max(TargetDate) over(partition by id) as max_TargetDate 
         from     t
        ) t 
         outer apply string_split(case TargetDate when max_TargetDate then replicate(',',datediff(day, TargetDate, getdate())/7) end,',')

| 标识符|报名日期|目标日期|
| - -|- -|- -|
| ID 238型|2022年9月6日|2022年9月6日|
| ID 238型|2022年9月6日|2022年9月9日|
| ID 238型|2022年9月6日|2022年9月11日|
| ID 238型|2022年9月6日|2022年9月18日|
| ID 238型|2022年9月6日|2022年9月25日|
| ID 238型|2022年9月6日|2022年10月2日|
| ID 238型|2022年9月6日|2022年10月9日|
| ID 238型|2022年9月6日|2022年10月16日|
| ID 636型|2022年9月30日|2022年9月30日|
| ID 636型|2022年9月30日|2022年10月3日|
| ID 636型|2022年9月30日|2022年10月5日|
| ID 636型|2022年9月30日|2022年10月12日|
Fiddle
对于Azure和SQL Server 2022中的SQL，我们有一个基于序数的更干净的解决方案。
Azure SQL数据库、Azure SQL托管示例和Azure Synapse Analytics（仅限无服务器SQL池）当前支持enable_ordinal参数和序号输出列。从SQL Server 2022（16.x）预览版开始，SQL Server中提供了该参数和输出列。

select   id
        ,EntryDate
        ,dateadd(day, 7*(ordinal - 1), TargetDate) as TargetDate 
from    (
         select   *
                 ,max(TargetDate) over(partition by id) as max_TargetDate 
         from     t
        ) t 
         outer apply string_split(case TargetDate when max_TargetDate then replicate(',',datediff(day, TargetDate, getdate())/7) end,',', 1)

赞(0）回复(0）举报 2022-10-31

我来回答

sql-server T-SQL为date和getdate之间的周生成行

2条答案

相关问题

热门标签

最新问答