在重复行时获取重复的日期

gudnpqoy 于 2021-07-24 发布在 Java

关注(0)|答案(1)|浏览(391)

我尝试在每个雇员的日期范围之间旋转或重复sfhitid（1,2）。
一切都很好，但我不知道如何停止得到重复的日期意味着为什么我得到了重复的日期，我怎么能摆脱它。。。
有人能帮我吗？
我唯一的要求是，如果任何员工ID有一个或多个轮班，那么shiftid应该在给定的日期范围内为每个员工重复。

DECLARE @TempTable TABLE (EmployeeId int, ShiftId int)

INSERT INTO @TempTable
    SELECT 1 , 1
    UNION ALL
    SELECT 1, 3
    UNION ALL
    SELECT 2, 3 

DECLARE @StartDate datetime = '2020-03-05',
        @EndDate   datetime = '2020-03-09';

WITH theDates AS
(
    SELECT @StartDate AS theDate
    UNION ALL
    SELECT DATEADD(DAY, 1, theDate) 
    FROM theDates
    WHERE DATEADD(DAY, 1, theDate) <= @EndDate
)
SELECT theDate, EmployeeID, SHiftId  
FROM theDates
CROSS APPLY @TempTable
ORDER BY EmployeeId, theDate
OPTION (MAXRECURSION 0);

我想要这样的结果。。。

theDate    EmployeeID   SHiftId
2020-03-05    1          1
2020-03-06    1          3
2020-03-07    1          1
2020-03-08    1          3
2020-03-09    1          1

2020-03-05    2          3
2020-03-06    2          3
2020-03-07    2          3
2020-03-08    2          3
2020-03-09    2          3

sql sql-server

来源：https://stackoverflow.com/questions/62847718/getting-duplicate-dates-while-repeating-the-rows

1条答案

按热度按时间

busg9geu1#

使用窗口函数连接两个表：

DECLARE @TempTable TABLE (EmployeeId int, ShiftId int)

INSERT INTO @TempTable
    SELECT 1 , 1
    UNION ALL
    SELECT 1, 3
    UNION ALL
    SELECT 2, 3 

DECLARE @StartDate datetime = '2020-03-05',
        @EndDate   datetime = '2020-03-09';

WITH 
  theDates AS (
    SELECT 1 rn, @StartDate AS theDate
    UNION ALL
    SELECT rn + 1, DATEADD(DAY, 1, theDate) 
    FROM theDates
    WHERE DATEADD(DAY, 1, theDate) <= @EndDate
  ),
  theShifts AS (
    SELECT *, 
      ROW_NUMBER() OVER (PARTITION BY EmployeeId ORDER BY ShiftId) rn,
      COUNT(*) OVER (PARTITION BY EmployeeId) counter
    FROM @TempTable
  )
SELECT d.theDate, s.EmployeeID, s.ShiftId  
FROM theDates d INNER JOIN theShifts s
ON s.rn % s.counter = d.rn % s.counter
ORDER BY s.EmployeeId, d.theDate
OPTION (MAXRECURSION 0);

请看演示。
结果：

> theDate                 | EmployeeID | ShiftId
> :---------------------- | ---------: | ------:
> 2020-03-05 00:00:00.000 |          1 |       1
> 2020-03-06 00:00:00.000 |          1 |       3
> 2020-03-07 00:00:00.000 |          1 |       1
> 2020-03-08 00:00:00.000 |          1 |       3
> 2020-03-09 00:00:00.000 |          1 |       1
> 2020-03-05 00:00:00.000 |          2 |       3
> 2020-03-06 00:00:00.000 |          2 |       3
> 2020-03-07 00:00:00.000 |          2 |       3
> 2020-03-08 00:00:00.000 |          2 |       3
> 2020-03-09 00:00:00.000 |          2 |       3

赞(0）回复(0）举报 2021-07-24

我来回答

在重复行时获取重复的日期

1条答案

相关问题

热门标签

最新问答