Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Domenick
Helper IV
Helper IV

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

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

1.PNG

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

2.PNG

 

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:

 

3.PNG

 

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

 

4.PNG

 

 

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?

 

 

1 ACCEPTED SOLUTION
smpa01
Super User
Super User

@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 #CreatedDateCompletion Date
32982787-14/16/20184/16/2018
27139392-111/20/201611/20/2016
27139392-211/20/201611/21/2016
27172623-111/22/20162/15/2017
27172842-111/22/20162/15/2017
27176489-111/22/201611/23/2016
27176527-111/22/201611/29/2016
27176527-211/29/201612/8/2016
27176550-111/22/201611/23/2016
27176577-111/22/201611/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 #CreatedDateCompletion DateDatediffDesired Row 
32982787-14/16/20184/16/201801
27139392-111/20/201611/20/201601
27139392-211/20/201611/21/201612
27172623-111/22/20162/15/20178586
27172842-111/22/20162/15/20178586
27176489-111/22/201611/23/201612
27176527-111/22/201611/29/201678
27176527-211/29/201612/8/2016910
27176550-111/22/201611/23/201612
27176577-111/22/201611/23/201612
    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

Capture.PNG

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

View solution in original post

6 REPLIES 6
v-juanli-msft
Community Support
Community Support

Hi @Domenick 

Is my understanding correct?

Capture23.JPG

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.

Yes. That is correct.

smpa01
Super User
Super User

@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 #CreatedDateCompletion Date
32982787-14/16/20184/16/2018
27139392-111/20/201611/20/2016
27139392-211/20/201611/21/2016
27172623-111/22/20162/15/2017
27172842-111/22/20162/15/2017
27176489-111/22/201611/23/2016
27176527-111/22/201611/29/2016
27176527-211/29/201612/8/2016
27176550-111/22/201611/23/2016
27176577-111/22/201611/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 #CreatedDateCompletion DateDatediffDesired Row 
32982787-14/16/20184/16/201801
27139392-111/20/201611/20/201601
27139392-211/20/201611/21/201612
27172623-111/22/20162/15/20178586
27172842-111/22/20162/15/20178586
27176489-111/22/201611/23/201612
27176527-111/22/201611/29/201678
27176527-211/29/201612/8/2016910
27176550-111/22/201611/23/201612
27176577-111/22/201611/23/201612
    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

Capture.PNG

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

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. 

@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)

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
smpa01
Super User
Super User

@Domenick  can you please provide a sample data set

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.