Thread: Spanning date ranges in query

1. Registered User
Join Date
Nov 2003
Posts
54

Unanswered: Spanning date ranges in query

Hello, I am trying to automate our FTE calculations, and I need to be able to determine the total days employed for a given employee for a given period of time. I have the date ranges they worked, but am not sure how to total those based on the required period, For example

empployee|Start Date|End Date
1|1/1/2005|3/1/2006
1| 4/15/2006| 1/1/2008

How do I total the number of days employed for this employee between 2/1/2006 and 2/1/2007?

Thanks in advance for any advice?

2. Annie's Dog Walker
Join Date
Nov 2004
Location
on the wrong server
Posts
8,842
Provided Answers: 6
you want to use the DATEDIFF function. Please see the documentation in Books Online.

3. Registered User
Join Date
Nov 2003
Posts
54
It goes a bit beyond datediff - both of the example rows are for teh same employee - they worked from 1/1/2005 to 3/1/2006, and then from 4/15/2006 to 1/1/2008 - I need to calculate how many total days they were employed between 2/1/2006 and 2/1/2007. I am hoping for something more elegant than the brute force of marching through each line for a total.

Thanks!

4. Annie's Dog Walker
Join Date
Nov 2004
Location
on the wrong server
Posts
8,842
Provided Answers: 6
try something like this...

Code:
```SELECT employeeid,SUM(DATEDIFF(dd,StartDate,EndDate))
FROM Mytable
WHERE etc....
Group By employeeid```
it is simple.

5. Registered User
Join Date
Nov 2003
Posts
54
Originally Posted by Thrasymachus
try something like this...

Code:
```SELECT employeeid,SUM(DATEDIFF(dd,StartDate,EndDate))
FROM Mytable
WHERE etc....
Group By employeeid```
it is simple.
Thanks for the help, but I think you are oversimplifying it - that does not take into account the multiple date ranges; One date range is their employment dates, one is for the query parameters. Your query will tell me thier total days worked, I need their total days worked during a supplied date range. I can build it to ma ke the necessary comparisons for each range, but I am looking for something more efficient as this will run against a large employee database, where very employee will have multiple employment date sets.

6. Annie's Dog Walker
Join Date
Nov 2004
Location
on the wrong server
Posts
8,842
Provided Answers: 6
you are right and I wrong. i was not reading too closely.

there is an elegant solution and I think George had a thread about a similar problem not too long ago. The answer is somewhere in ...

http://www.google.com/search?hl=en&q...erver+time+gap

.... but now it is time for lunch.

7. Registered User
Join Date
Apr 2008
Location
Iasi, Romania
Posts
579
Provided Answers: 3
I think you need something like:

create procedure TestDateRange(@dStartRange datetime, @dEndRange datetime)
as
select employeeid, sum(datediff(dd,
(case
when hire_date<@dStartRange then @dStartRange
else hire_date
end),
(case
when leave_date<@dEndRange then leave_date
else @dEndRange
end)))
from datetesttable
group by employeeid

Of course, you may want to take care of null values (on leave_date field, maybe?)

8. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
Provided Answers: 54
I don't think you can get a useful FTE number from this... You have date ranges, but I'd be really surprised if the employee worked every day in the range. At least for my purposes, you are tilting at windmills and whatever answer you get will mean nothing.

-PatP

9. Registered User
Join Date
Nov 2003
Posts
54
Originally Posted by Pat Phelan
I don't think you can get a useful FTE number from this... You have date ranges, but I'd be really surprised if the employee worked every day in the range. At least for my purposes, you are tilting at windmills and whatever answer you get will mean nothing.

-PatP
Our FTE calculation is based on a person working everyday; this is only part of the calculation. This number determines the number of days they worked which is compared to the possible number of days worked. We then use the generated ratios to adjust billing goals for each employee. While I appreciate your input, it was not very constructive and even a tad bit snotty given you did not have all of the information to make that comment.

10. Registered User
Join Date
Nov 2003
Posts
54
Originally Posted by aflorin27
I think you need something like:

create procedure TestDateRange(@dStartRange datetime, @dEndRange datetime)
as
select employeeid, sum(datediff(dd,
(case
when hire_date<@dStartRange then @dStartRange
else hire_date
end),
(case
when leave_date<@dEndRange then leave_date
else @dEndRange
end)))
from datetesttable
group by employeeid

Of course, you may want to take care of null values (on leave_date field, maybe?)
Thanks! Your solution did most of the work for me! Here is what I ended up using:

Code:
```select eeflxideb, sum(datediff(dd,
(case
when eedatebeg < @start then @start
else eedatebeg
end),
(case
when eedateend > @end then @end
when eedateend is null then @end
else eedateend
end))) days
from eemploy
where eestatus = 'Active'
and eedatebeg < @end
and (eedateend > @start or eedateend is null)
group by eeflxideb
order by days desc```
I added a bit to dal with null end dates, and also a where clause to exlcude lines that both begin and end before the given date range.

Thanks again for the help!
Last edited by cdols; 04-23-08 at 14:59.

11. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
Provided Answers: 54
Originally Posted by cdols
While I appreciate your input, it was not very constructive and even a tad bit snotty given you did not have all of the information to make that comment.
My purpose was to make you think before you spent time and energy on work that would probably be wasted.
Originally Posted by cdols
Our FTE calculation is based on a person working everyday; this is only part of the calculation.
When I saw you use FTE, I assumed that you meant FTE as required by US government mandated reporting which is quite different from what you are apparently calculating. Other (not related to required reporting) uses for the term FTE are normally explained in more detail.

I do find it amusing that you consider my response "snotty" because of information that you failed to provide, but I'm easily amused. As long as you got what you wanted, then life is good.

-PatP

12. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
Provided Answers: 54
As an observation, I think you'll have better luck with:
Code:
```SELECT eeflxideb, Sum(DateDiff(day,
CASE
WHEN @start < eedatebeg THEN eedatebeg
ELSE @start
END
,  CASE
WHEN eedateend > @end THEN eedateend
ELSE @end
END) AS days
FROM eemploy
WHERE 'Active' = eestatus
AND eedatebeg <= @end
AND @start <= Coalesce(eedateend, @start)
GROUP BY eefixideb
ORDER BY 2 DESC```
I think that the version you posted will have problems running at all because of the ORDER BY and that it won't produce correct results for cases that begin or end on your @start or @end because of the WHERE.

-PatP

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•