Running total with "treshold value" detection..?

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.
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):
selectdatepart(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
Jj wrote:
> 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.
>
|||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.
"Mike Hodgson" <mwh_junk@.hotmail.com> wrote in message
news:eXfyF4JFFHA.3780@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> 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
>
> Jj wrote: