I have below table
| Monday | Tuesday | Wednesday | Thursday | Friday | WeekStartsOn |
|---|---|---|---|---|---|
| 8 | 8 | 8 | 8 | 8 | 2019-09-06 |
I want to convert it to below table
| DayOftheWeek | ActualHrs | WeekStartsOn | TimecardDate |
|---|---|---|---|
| Monday | 8 | 2019-09-06 | 2019-09-07 |
| Tuesday | 8 | 2019-09-06 | 2019-09-08 |
| Wednesday | 8 | 2019-09-06 | 2019-09-09 |
| Thursday | 8 | 2019-09-06 | 2019-09-10 |
| Friday | 8 | 2019-09-06 | 2019-09-11 |
The logic To add **TimecardDate **is for Monday-->WeekstartsOn+1, Tuesday--> WeekStartsOn+2 .... , Friday--> WeekstartsOn+5
I have tried below Query
SELECT ActualHrs,DayOftheWeek,WeekStartsOn
FROM (select Monday,Tuesday,Wednesday,Thursday,Friday,WeekStartsOn
from itplanning.enriched.timecard_enriched_current) as cp
UNPIVOT
(
ActualHrs FOR DayOftheWeek IN (Monday,Tuesday,Wednesday,Thursday,Friday )
)
AS PivotTable
where ActualHrs<>0
with this Above query, I have got below table
| DayOftheWeek | ActualHrs | WeekStartsOn |
|---|---|---|
| Monday | 8 | 2019-09-06 |
| Tuesday | 8 | 2019-09-06 |
| Wednesday | 8 | 2019-09-06 |
| Thursday | 8 | 2019-09-06 |
| Friday | 8 | 2019-09-06 |
I am unable to do the TimecardDate column.
Actual table is below
| Monday | Tuesday | Wednesday | Thursday | Friday | WeekStartsOn |
|---|---|---|---|---|---|
| 8 | 8 | 8 | 8 | 8 | 2019-09-06 |
Expected Table is below
| DayOftheWeek | ActualHrs | WeekStartsOn | TimecardDate |
|---|---|---|---|
| Monday | 8 | 2019-09-06 | 2019-09-07 |
| Tuesday | 8 | 2019-09-06 | 2019-09-08 |
| Wednesday | 8 | 2019-09-06 | 2019-09-09 |
| Thursday | 8 | 2019-09-06 | 2019-09-10 |
| Friday | 8 | 2019-09-06 | 2019-09-11 |
You can do something like this instead:
SELECT v.d,v.hours,DATEADD(DAY, v.addDay, WeekStartsOn), WeekStartsOn
FROM itplanning.enriched.timecard_enriched_current
CROSS APPLY (
VALUES(Monday,'Monday', 1),(Tuesday,'Tuesday', 2),(Wednesday, 'Wednesday', 3),(Thursday, 'Thursday', 4),(Friday, 'Friday', 5)
) v(hours, d, addDay)
where hours<>0
I unpivot manually and creates a two column table containing week and how many days to add to start of the week
Edit: added an alternative that does it with pivot syntax
select *
, dateadd(day, case DayOftheWeek when 'monday' then 1 when 'tuesday' then 2 when 'wednesday' then 3 when 'thursday' then 4 when 'friday' then 5 end, weekstartson) AS TimecardDate
FROM itplanning.enriched.timecard_enriched_current
UNPIVOT
(
ActualHrs FOR DayOftheWeek IN (Monday,Tuesday,Wednesday,Thursday,Friday )
)
AS PivotTable
Edit #3 (SQLite edition):
with cte as (
select monday, 'monday', 1 AS addition, weekstartson
from timecard_enriched_current
union
select tuesday, 'tuesday', 2, weekstartson
from timecard_enriched_current
union
select wednesday, 'wednesday', 3, weekstartson
from timecard_enriched_current
union
select thursday, 'thursday', 4, weekstartson
from timecard_enriched_current
union
select friday, 'friday', 5, weekstartson
from timecard_enriched_current
)
select *, DATE(weekstartson, '+' || addition || ' days') AS TimecardDate
from cte
order by addition