Dynamics budgeting data on week range, how to generate it on date level of granularity?-CodePudding

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 dont recieve from Dynamics (source) is the dates between week 1 and 5 so only thing I recieve 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

Column:

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 dont have, meaning I dont 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!

CodePudding user response：

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