时间戳

首页 > 解决方案 > 如何在 SQL 跳过周末重复模式？

sql - 如何在 SQL 跳过周末重复模式？

问题描述

我有一个表格，其列模式类似于“1,2,3,4”，列名频率表示每个模式应重复多少次。例如。

我生成了一个模式，但无法跳过周末，这是我当前的代码 -

;WITH TestCteNew (EmployeeId, ShiftId, StartDate, Enddate)AS (
SELECT
    employeeid.n.query('.[1]').value('.', 'INT') EmployeeId,
    shiftid.n.query('.[1]').value('.', 'INT') ShiftId
    ,StartDate, Enddate
FROM 
TestCte
CROSS APPLY employeeid.nodes('x') AS employeeid(n)
CROSS APPLY shiftid.nodes('x') AS shiftid(n)
CROSS APPLY (SELECT TOP(2) ROW_NUMBER() OVER(ORDER BY (SELECT NULL))-1 r_num FROM SYS.ALL_OBJECTS A , SYS.ALL_OBJECTS B) X)
,TestCteFinal(EmployeeId, ShiftId, SDate,r_num) AS (
SELECT EmployeeId, ShiftId, StartDate + ROW_NUMBER() OVER (PARTITION BY EmployeeId ORDER BY r_num)-1 AS SD, x.r_num
FROM TestCteNew
CROSS APPLY (SELECT TOP(2) ROW_NUMBER() OVER(ORDER BY (SELECT NULL))-1 r_num FROM SYS.ALL_OBJECTS A , SYS.ALL_OBJECTS B) X)

使用上面的代码，我可以生成如下所示的内容

 Account DayOfWeek   Shifts   Shifts
    1      20201007    100        1
    2      20201107    100        1   (Saturday)
    3      20201207    100        2   (Sunday)
    4      20201307    100        2
    5      20201407    100        3
    6      20201507    100        3
    7      20201607    100        4
    8      20201707    100        4
                                  ...Same set of records above once again

这里的问题是我的模式不是跳过周末，我想要下面的东西。

DECLARE @Pattern VARCHAR(10)= '1,2,3,4', @Frequency INT=2

Account DayOfWeek   Shifts   Shifts
1      20201007    100        1
2      20201107    100        0   (Saturday)
3      20201207    100        0   (Sunday)
4      20201307    100        1
5      20201407    100        2
6      20201507    100        2
7      20201607    100        3
8      20201707    100        3
9      20201807    100        0   (Saturday)
10     20201907    100        0   (Sunday)
12     20202007    100        4
13     20202107    100        4
14     20202207    100        1
15     20202307    100        1

我想以上述定义的格式重复模式。

标签： sqlsql-server

解决方案

这是伪代码，因为您不清楚数据的来源。您指的TestCte是未定义的。查询从不使用指向所需外部的@Frequencyand变量。@Pattern输出有一个Account在其他任何地方都没有提到的列......但也许这种方法会更好。

declare @Frequency int = 2;
declare @StartDt date = '20200710';
with num(n) as (
    select top (256) row_number() over (order by (select null)) - 1
    from sys.all_objects
), dates(n, dt) as (
    select row_number() over (order by n), dateadd(day, n, @StartDt)
    from num
    -- filter weekend dates
    where datepart(weekday, dateadd(day, n, @StartDt)) between 1 and 5
)
select p.n, r.n, d.dt
from
    patterns as p -- this comes from xml? I'm going to assume these are numbered somehow
    inner join num as r /* repetitions */
        on r.n <= @Frequency -- I think something like "multiples" might be a better name
    inner join dates as d
        on d.n = @Frequency * p.n + r.n

推荐阅读