Split row into multiple rows and calculate date for each row

Transact-SQL
1

I have a table of timesheet data from our ERP system and I need to split it into 7 rows, one for each day of the week and add the date for that day based on the column name and weekending date.

Here is some partial data from the source database:

+----------+----------------------------------+------------+----------+-------------------+----------+----------+----------+----------+----------+----------+----------+-----------+-----------+
| RECORDER |             CIRECID              |  ENDDATE   |  ITEMNO  |       MEMO        | STDHRS#1 | STDHRS#2 | STDHRS#3 | STDHRS#4 | STDHRS#5 | STDHRS#6 | STDHRS#7 | OTHRS#1#1 | OTHRS#2#1 |
+----------+----------------------------------+------------+----------+-------------------+----------+----------+----------+----------+----------+----------+----------+-----------+-----------+
|       91 | 0A186D8CDB6B499C8BFEBF104C023C6C | 08/08/2021 | 14MC1136 | Adrian Baxanean   |        9 |        9 |        9 |        9 |        9 |        0 |        0 |         2 |         1 |
|       93 | 911BD813528546089A3102CDB5F5BE26 | 08/08/2021 | 14MC0513 | Scott Fleming     |        9 |        9 |        9 |        9 |        9 |        0 |        0 |         1 |         1 |
|      100 | 0A186D8CDB6B499C8BFEBF104C023C6C | 15/08/2021 | 14MC1136 | Adrian Baxanean   |        9 |        9 |        9 |        9 |        9 |        0 |        0 |         2 |         2 |
+----------+----------------------------------+------------+----------+-------------------+----------+----------+----------+----------+----------+----------+----------+-----------+-----------+

This should be split into 7 rows for each row, with three columns added for Standard Hours and Overtime Hours 1 & 2 (STDHRS, OTHRS1 and OTHRS2). The date should then be calculated based on the ENDDATE which is always a Sunday, the hours columns

STDHRS#1 is Monday
STDHRS#2 is Tuesday etc.

OTHRS#1#1 is Monday
OTHRS#2#1 is Tuesday etc.

(The second overtime rate is not shown in the data above but would be the same format with OTHRS#1#2 being Monday, OTHRS#2#2 being Tuesday etc.)

For the source data above, this is what needs to be returned:

+----------+----------------------------------+------------+----------+-------------------+------------+--------+---------+---------+
| RECORDER |             CIRECID              |  ENDDATE   |  ITEMNO  |       MEMO        |    DATE    | STDHRS | OTHRS#1 | OTHRS#2 |
+----------+----------------------------------+------------+----------+-------------------+------------+--------+---------+---------+
|       91 | 0A186D8CDB6B499C8BFEBF104C023C6C | 08/08/2021 | 14MC1136 | Adrian Baxanean   | 02/08/2021 |      9 |       2 |       0 |
|       91 | 0A186D8CDB6B499C8BFEBF104C023C6C | 08/08/2021 | 14MC1136 | Adrian Baxanean   | 03/08/2021 |      9 |       1 |       0 |
|       91 | 0A186D8CDB6B499C8BFEBF104C023C6C | 08/08/2021 | 14MC1136 | Adrian Baxanean   | 04/08/2021 |      9 |       0 |       0 |
|       91 | 0A186D8CDB6B499C8BFEBF104C023C6C | 08/08/2021 | 14MC1136 | Adrian Baxanean   | 05/08/2021 |      9 |       0 |       0 |
|       91 | 0A186D8CDB6B499C8BFEBF104C023C6C | 08/08/2021 | 14MC1136 | Adrian Baxanean   | 06/08/2021 |      9 |       0 |       0 |
|       91 | 0A186D8CDB6B499C8BFEBF104C023C6C | 08/08/2021 | 14MC1136 | Adrian Baxanean   | 07/08/2021 |      0 |       0 |       0 |
|       91 | 0A186D8CDB6B499C8BFEBF104C023C6C | 08/08/2021 | 14MC1136 | Adrian Baxanean   | 08/08/2021 |      0 |       0 |       0 |
|       93 | 911BD813528546089A3102CDB5F5BE26 | 08/08/2021 | 14MC0513 | Scott Fleming     | 02/08/2021 |      9 |       1 |       0 |
|       93 | 911BD813528546089A3102CDB5F5BE26 | 08/08/2021 | 14MC0513 | Scott Fleming     | 03/08/2021 |      9 |       1 |       0 |
|       93 | 911BD813528546089A3102CDB5F5BE26 | 08/08/2021 | 14MC0513 | Scott Fleming     | 04/08/2021 |      9 |       0 |       0 |
|       93 | 911BD813528546089A3102CDB5F5BE26 | 08/08/2021 | 14MC0513 | Scott Fleming     | 05/08/2021 |      9 |       0 |       0 |
|       93 | 911BD813528546089A3102CDB5F5BE26 | 08/08/2021 | 14MC0513 | Scott Fleming     | 06/08/2021 |      9 |       0 |       0 |
|       93 | 911BD813528546089A3102CDB5F5BE26 | 08/08/2021 | 14MC0513 | Scott Fleming     | 07/08/2021 |      0 |       0 |       0 |
|       93 | 911BD813528546089A3102CDB5F5BE26 | 08/08/2021 | 14MC0513 | Scott Fleming     | 08/08/2021 |      0 |       0 |       0 |
|      100 | 0A186D8CDB6B499C8BFEBF104C023C6C | 15/08/2021 | 14MC1136 | Adrian Baxanean   | 09/08/2021 |      9 |       2 |       0 |
|      100 | 0A186D8CDB6B499C8BFEBF104C023C6C | 15/08/2021 | 14MC1136 | Adrian Baxanean   | 10/08/2021 |      9 |       2 |       0 |
|      100 | 0A186D8CDB6B499C8BFEBF104C023C6C | 15/08/2021 | 14MC1136 | Adrian Baxanean   | 11/08/2021 |      9 |       0 |       0 |
|      100 | 0A186D8CDB6B499C8BFEBF104C023C6C | 15/08/2021 | 14MC1136 | Adrian Baxanean   | 12/08/2021 |      9 |       0 |       0 |
|      100 | 0A186D8CDB6B499C8BFEBF104C023C6C | 15/08/2021 | 14MC1136 | Adrian Baxanean   | 13/08/2021 |      9 |       0 |       0 |
|      100 | 0A186D8CDB6B499C8BFEBF104C023C6C | 15/08/2021 | 14MC1136 | Adrian Baxanean   | 14/08/2021 |      0 |       0 |       0 |
|      100 | 0A186D8CDB6B499C8BFEBF104C023C6C | 15/08/2021 | 14MC1136 | Adrian Baxanean   | 15/08/2021 |      0 |       0 |       0 |
+----------+----------------------------------+------------+----------+-------------------+------------+--------+---------+---------+

Here is create and sample data:

CREATE TABLE #TimeSheets (
	
	[RECORDER] [numeric](18, 0) NULL,
	[CIRECID] [varchar](32) NULL,
	[ENDDATE] [datetime] NULL,
	[ITEMNO] [varchar](20) NULL,
	[MEMO] [varchar](max) NULL,
	[STDHRS#1] [decimal](15, 4) NULL,
	[STDHRS#2] [decimal](15, 4) NULL,
	[STDHRS#3] [decimal](15, 4) NULL,
	[STDHRS#4] [decimal](15, 4) NULL,
	[STDHRS#5] [decimal](15, 4) NULL,
	[STDHRS#6] [decimal](15, 4) NULL,
	[STDHRS#7] [decimal](15, 4) NULL,
	[OTHRS#1#1] [decimal](15, 4) NULL,
	[OTHRS#2#1] [decimal](15, 4) NULL

	);


INSERT INTO #timesheets VALUES (91,'0A186D8CDB6B499C8BFEBF104C023C6C','2021-08-08','14MC1136','Adrian Baxanean',9,9,9,9,9,0,0,2,1);

INSERT INTO #timesheets VALUES (93,'911BD813528546089A3102CDB5F5BE26','2021-08-08','14MC0513','Scott Fleming',9,9,9,9,9,0,0,1,1);

INSERT INTO #timesheets VALUES (100,'0A186D8CDB6B499C8BFEBF104C023C6C','2021-08-15','14MC1136','Adrian Baxanean',9,9,9,9,9,0,0,2,2);


SELECT * FROM #timesheets

How can I accomplish this? I had thought to use UNPIVOT but couldn't see that would give me what I need. Any help would be appreciated.

Many thanks.

2

No other days of the week are involved?

3

There could be STDHRS and OT1HRS and/or OT2HRS hours on any day of the week. The ENDDATE is always a Sunday, so the DATE for STDHRS#7, OTHRS#7#1 and OTHRS#7#2 rows should be the same as the ENDDATE.

I've not shown from OT#3#1 to OT#7#1 or from OT#1#2 to OT#7#2 in the source table for convenience in this post.

Thanks

4

that is a lot of yards for the simple question: No other days of the week are involved?

Anyways, the ENDDATE and DATE format are different, is that be design?

5

No, datetime is fine for both.

6

What is first day of the week, in your neck of the woods?

7

Monday

8

why are 8/7 and 8/8 zero for the STDHRS. Is that because they are weekends?

9

Yes and all time on weekends in our business would be either Overtime 1 or 2.

10 
SELECT
    CA1.*
FROM #TimeSheets TS
CROSS APPLY ( VALUES
    (RECORDER, CIRECID, ENDDATE, ITEMNO, MEMO, DATEADD(DAY, -6, ENDDATE),  STDHRS#1, OTHRS#1#1/*, OTHRS#1#2*/),
    (RECORDER, CIRECID, ENDDATE, ITEMNO, MEMO, DATEADD(DAY, -5, ENDDATE),  STDHRS#2, OTHRS#2#1/*, OTHRS#2#2*/),
    (RECORDER, CIRECID, ENDDATE, ITEMNO, MEMO, DATEADD(DAY, -4, ENDDATE),  STDHRS#3, 0/*OTHRS#3#1*//*, OTHRS#3#2*/),
    (RECORDER, CIRECID, ENDDATE, ITEMNO, MEMO, DATEADD(DAY, -3, ENDDATE),  STDHRS#4, 0/*OTHRS#4#1*//*, OTHRS#4#2*/),
    (RECORDER, CIRECID, ENDDATE, ITEMNO, MEMO, DATEADD(DAY, -2, ENDDATE),  STDHRS#5, 0/*OTHRS#5#1*//*, OTHRS#5#2*/),
    (RECORDER, CIRECID, ENDDATE, ITEMNO, MEMO, DATEADD(DAY, -1, ENDDATE),  STDHRS#6, 0/*OTHRS#6#1*//*, OTHRS#6#2*/),
    (RECORDER, CIRECID, ENDDATE, ITEMNO, MEMO, ENDDATE,  STDHRS#7, 0/*OTHRS#7#1*//*, OTHRS#7#2*/)
) AS CA1 ( RECORDER, CIRECID, ENDDATE, ITEMNO, MEMO, DATE, STDHRS, OTHRS#1/*, OTHRS$2*/ )
ORDER BY ITEMNO, DATE
1 Like
11

Thanks Scott, I will give this a try later.

12

Thanks Scott, this worked perfectly.

13

I know I'm necro'ing an 8 month old post but... you can simplify Scott's good answer a bit by moving the "common" data out of the CROSS APPLY to the SELECT, like this...

--===== Note that common data for each row has been moved out of the CROSS APPLY to the SELECT.
     -- The ORDER BY has been modified to produce the output in the order that the  OP posted.
 SELECT  RECORDER, CIRECID, ENDDATE, ITEMNO, MEMO --Data common to all rows
        ,unpvt.* --Unpivoted data
   FROM #TimeSheets TS
  CROSS APPLY (VALUES
                (DATEADD(DAY,-6,ENDDATE), STDHRS#1, OTHRS#1#1     /*, OTHRS#1#2*/),
                (DATEADD(DAY,-5,ENDDATE), STDHRS#2, OTHRS#2#1     /*, OTHRS#2#2*/),
                (DATEADD(DAY,-4,ENDDATE), STDHRS#3, 0/*OTHRS#3#1*//*, OTHRS#3#2*/),
                (DATEADD(DAY,-3,ENDDATE), STDHRS#4, 0/*OTHRS#4#1*//*, OTHRS#4#2*/),
                (DATEADD(DAY,-2,ENDDATE), STDHRS#5, 0/*OTHRS#5#1*//*, OTHRS#5#2*/),
                (DATEADD(DAY,-1,ENDDATE), STDHRS#6, 0/*OTHRS#6#1*//*, OTHRS#6#2*/),
                (               ENDDATE , STDHRS#7, 0/*OTHRS#7#1*//*, OTHRS#7#2*/)
              ) AS unpvt (          DATE, STDHRS  , OTHRS#1/*       , OTHRS$2*/ )
  ORDER BY ENDDATE, MEMO, ITEMNO, DATE
;