CODEWITHSUNDEEP
sqlsql-server
vijay sahu
vijay sahu

Reputation: 815

Getting duplicate dates while repeating the rows

I'm trying to rotate or repeat the sfhitId(1,2) between the date range for each employee.

Everything is working fine but I don't know how to stop getting duplicate dates means why I am getting dublicate dates and how can I rid from it...

Can anyone help me with this?

My only requirement is if any employeeid has 1 or more than 1 shift then shiftId should repeat between given date range for each employee.

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);

enter image description here

and I want result like this...

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

Upvotes: 0

Views: 292

Answers (1)

forpas
forpas

Reputation: 164064

Use window functions to join the 2 tables:

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);

See the demo.
Results:

> 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

Upvotes: 1

Related Questions