I have a issue where I get budgeting data on week range like 1 2022 - 5 2022 meaning week 1 to week 5 - 2022.
What I don't I receive from Dynamics (source) is the dates between week 1 and 5 so only thing I receive is related to week 1 and also related to week 5.
Meaning week 2,3 and 4 it is assumed to be in the range but not any actual data for it.
Thats how it looks in the Budgeting table.
Then there is a Week table that have date ranges however it is depending on the budgeting table to show the budgeting data.
Budget table
Columns:
StartWeekID (unique identifier)
EndWeekID (unique identifier)
DurationPerWeek (8 hours for instance)
Week table
WeekID (unique identifier)
StartDate (2022-01-02)
EndDate (2022-02-05)
WeekNumber (1 or 5 etc.)
SELECT
Sw.WeekNumber as 'Start Week'
Ew.WeekNumber as 'End Week'
Sw.StartDate,
Ew.EndDate,
b.DurationPerWeek
FROM Budget AS b
JOIN week AS Sw ON Sw.WeekID = b.StartWeekID
JOIN week AS Ew ON Ew.WeekID = b.EndWeekID
For above query result would look like this:
| Start Week | End Week | StartDate | EndDate | DurationPerWeek |
|---|---|---|---|---|
| 1 | 2 | 2022-01-02 | 2022-01-16 | 60 |
| 1 | 9 | 2022-01-02 | 2022-03-06 | 60 |
| 1 | 40 | 2022-01-02 | 2022-10-09 | 30 |
| 1 | 52 | 2022-01-02 | 2023-01-01 | 2000 |
Now the question is based on what I have and don't have, meaning I don't have StartDate nor EndDate in the Budget table i.e. its only on weekly level of granularity, is it still possible to make it on daily level of granularity, meaning so I can query budgets on day level? Please help with this, thanks!
Just join Week like this:
SELECT
w.WeekNumber
w.StartDate,
w.EndDate,
b.DurationPerWeek
FROM Budget AS b
JOIN Week AS w
ON w.WeekID >= b.StartWeekID
AND w.WeekID <= b.EndWeek