Results 1 to 12 of 12
  1. #1
    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. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    you want to use the DATEDIFF function. Please see the documentation in Books Online.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    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. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    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.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Nov 2003
    Posts
    54
    Quote 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. #6
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    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.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  7. #7
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    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. #8
    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. #9
    Join Date
    Nov 2003
    Posts
    54
    Quote 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. #10
    Join Date
    Nov 2003
    Posts
    54
    Quote 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. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote 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.
    Quote 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. #12
    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
  •