cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Domenick Frequent Visitor
Frequent Visitor

[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

Accepted Solutions
smpa01 Senior Member
Senior Member

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

@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

View solution in original post

5 REPLIES 5
smpa01 Senior Member
Senior Member

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

@Domenick  can you please provide a sample data set

smpa01 Senior Member
Senior Member

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

@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

View solution in original post

smpa01 Senior Member
Senior Member

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

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

Community Support Team
Community Support Team

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

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.
Domenick Frequent Visitor
Frequent Visitor

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

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. 

Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

November 2019 Community Highlights

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)