Running total with "treshold value" detection..?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

J?j? - 17 Feb 2005 01:19 GMT

Hi,

I have to create a query which evalaute, for each year, the employees who
reached a particular number of absences and when this value is reached.

I have 1 000 000 records in my absence table.

my table is like this:
DateID, EmployeeID, ActivityID, AbsenceDurationInDays

I want a list of 1 date by year by employee and by activity when the runin
absenceduration total reached 5 days.
Also, I'll evaluate the same formula but by quarter instead-of year and the
target value is 3 days.
AbsenceDurationInDays is a floating value like:
0.5 = half a day (or 4hours of work)

any guide?
any sample query anywhere?
how to setup my indexes?

I'll store the result in a table using DTS, and I'll evaluate this query 1
time a week.

thanks for your help.

Jerome.

Reply to this Message

Mike Hodgson - 17 Feb 2005 03:08 GMT

Not sure I understand exactly what you're after but if I guess correctly
you want a query like this (assuming dateID is a smalldatetime or a
datetime):

select    datepart(year, DateID) as [Year],
    EmployeeID,
    ActivityID,
    sum(AbsenceDurationInDays) as RunningTotal
from AbsencesTable
group by datepart(year, DateID), EmployeeID, ActivityID
having sum(AbsenceDurationInDays) >= 5

If you want to alter the query to deal with quarters instead of years
just change the first argument from "year" to "quarter". If you want to
change the threshold just change the "5" value in the HAVING clause to
whatever you want it to be.

Cheers
Mike

Jéjé wrote:

> Hi,
>
[quoted text clipped - 23 lines]
>
> Jerome.

Reply to this Message

J?j? - 17 Feb 2005 03:20 GMT

but this query doesn't return the date when the runningtotal reached the 5
value.

For example, if an employee has 10 days of absences on the same activity
from the 1st feb. to the 10st feb.
then the expected result is the 5th feb. when the employee has cumulated 5
days of absences.

> Not sure I understand exactly what you're after but if I guess correctly
> you want a query like this (assuming dateID is a smalldatetime or a
[quoted text clipped - 43 lines]
>>
>> Jerome.

Reply to this Message