[Help!] How to use Start/End Date to distribute periodic hours to dates for each employee

[Domenick]

I need to create a data set that will allow me to see daily assigned hours by employee. My result should look like this:

 Which I'm planning to aggregate to look like this so that I can visualize when employees are in need of assignments:

I'm starting with two tables. One is an "Assignments" table that shows each assignment, employee, hours, and (unfortunately) start/end date. I've already added a column that shows number of assigned hours per weekday for each assignment:

And then I have a basic calendar table that looks like this which I'm hoping will be handy here: 

I need to somehow use the start and end date to produce the desired table (see first image) where each weekday for each assignment per employee has its own row with the assigned hours distributed properly. But I don't know how to get there. I'm suspecting I either do a cross join or create some loop in SQL that will do what I need (the data is from a SQL Server direct query).

Does anyone have the alchemy skills to help me out or lend some advice?

[v-juanli-msft]

Hi @Domenick 

Is my understanding correct?

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Domenick]

Yes. That is correct.

[smpa01 · Accepted solution]

@Domenick  since you mentioned that the data comes from a SQL table, would you like to convert the data to your desired output on the SQL server side ?

I recreated a sample data on the sql server side. Let's suppose the sql server table is following

select TOP 10 [Sub Case #], CAST([CreatedDate] as DATE) as [CreatedDate] , CAST([Completion Date] as DATE) as [Completion Date] from [xxx].[yyy]

 which gives me this

Sub Case #	CreatedDate	Completion Date
32982787-1	4/16/2018	4/16/2018
27139392-1	11/20/2016	11/20/2016
27139392-2	11/20/2016	11/21/2016
27172623-1	11/22/2016	2/15/2017
27172842-1	11/22/2016	2/15/2017
27176489-1	11/22/2016	11/23/2016
27176527-1	11/22/2016	11/29/2016
27176527-2	11/29/2016	12/8/2016
27176550-1	11/22/2016	11/23/2016
27176577-1	11/22/2016	11/23/2016

Now you want each row of this table to repeat for each of the unique dates that are contained between CreatedDate and Completion Date.

Sub Case #	CreatedDate	Completion Date	Datediff	Desired Row
32982787-1	4/16/2018	4/16/2018	0	1
27139392-1	11/20/2016	11/20/2016	0	1
27139392-2	11/20/2016	11/21/2016	1	2
27172623-1	11/22/2016	2/15/2017	85	86
27172842-1	11/22/2016	2/15/2017	85	86
27176489-1	11/22/2016	11/23/2016	1	2
27176527-1	11/22/2016	11/29/2016	7	8
27176527-2	11/29/2016	12/8/2016	9	10
27176550-1	11/22/2016	11/23/2016	1	2
27176577-1	11/22/2016	11/23/2016	1	2
				200

DECLARE @start DATE = (SELECT MIN([CreatedDate]) from [xxx].[yyy]), 
        @end DATE = (SELECT MAX([Completion Date]) from [xxx].[yyy])
DECLARE @datetable table ( dt DATE)
while (@start <= @end)
begin
	insert into @datetable
	select @start

	set @start = DATEADD(Day,1,@start)
end

Select 
a.[Sub Case #], a.[CreatedDate], a.[Completion Date],x.[dt] as [Throu]
from
(select TOP 10 [Sub Case #], CAST([CreatedDate] as DATE) as [CreatedDate] , CAST([Completion Date] as DATE) as [Completion Date] from [xxx].[yyy])a
JOIN @datetable x
ON x.[dt] BETWEEN a.[CreatedDate] AND a.[Completion Date]

It gives me this

[Domenick]

Thank you so much! This worked really well. I didn't know you could use BETWEEN in a join like that. It's kind of an elegant way to do it. 

[smpa01]

@Domenick  if you want the rows to be expanded based on the weekdays contained within each date range, then replace the following part with

set @start = DATEADD(Day,1,@start)

  DATEADD(Weekday,1,@start)

[smpa01]

@Domenick  can you please provide a sample data set