Regular Hours and OverTime Hours 1

[JW61]

I currently have the following SQl statement:

SELECT A.empnum, A.weeknumber, A.date, A.SumOfhours, (Select Sum(sumofHours) From Table1 B Where B.[empnum]=A.[empnum] and b.[weeknumber]=a.[weeknumber]) AS TotWk
FROM Table1 AS A ORDER BY A.empnum, A.date;

Returns:
empnum weeknumber date SumOfhours TotWk
025217 02 1/7/2008 800 4940
025217 02 1/8/2008 1160 4940
025217 02 1/9/2008 890 4940
025217 02 1/10/2008 1160 4940
025217 02 1/11/2008 930 4940

What I would also like is to break the amount of hours into Reg & OT based on 40 hrs:
empnum weeknumber date SumOfhours TotWk Reg OT
025217 02 1/7/2008 800 4940 800 0
025217 02 1/8/2008 1160 4940 1160 0
025217 02 1/9/2008 890 4940 890 0
025217 02 1/10/2008 1160 4940 1150 10
025217 02 1/11/2008 930 4940 0 930

Is this possible using a query?

 

[PHV]

You may try this:

SELECT A.empnum, A.weeknumber, A.date, A.SumOfhours, B.TotWk, Sum(C.SumOfhours) AS Run
, IIf(Run<=4000,A.SumOfhours,IIf(Run-4000<=A.SumOfhours,A.SumOfhours-Run+4000,0)) AS Reg
, IIf(Run>4000,IIf(Run-4000<=A.SumOfhours,Run-4000,A.SumOfhours),0) AS OT
FROM (Table1 AS A
INNER JOIN (SELECT empnum, weeknumber, Sum(SumOfhours) AS TotWk FROM Table1 GROUP BY empnum, weeknumber
) AS B ON A.empnum = B.empnum AND A.weeknumber = B.weeknumber)
INNER JOIN Table1 AS C ON A.empnum = C.empnum AND A.weeknumber = C.weeknumber AND A.date >= C.date
GROUP BY A.empnum, A.weeknumber, A.date, A.SumOfhours, B.TotWk

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MoLaker]

Certainly there are ways to do this kind of thing, but probably not with the way your data is constructed. It does not appear that you have any way to determine what is overtime hours except by total hours and that seems to be inconsistent, at least from your example. You show 1160 hours with no overtime on 1/8/2008 and 10 hours of overtime for the same amount of total hours on 1/10/2008. There may be a legitimate reason for this, but it does not appear to be defined in your data. Consequently, unless you have much more detail in your data than your example shows, I don't see how you could do this with a query.

 

[PHV]

MoLaker,
My understanding is that overtime begins after 4000 hours the week.
Look at may previous reply.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MoLaker]

I guess, but the rows of data seem to be employee and week specific. There are only 168 hours in a week. So, where does 4000 hours come in? Does this mean an employee draws no overtime until he works 4000 hours? Then for every week following, it is all overtime? I guess I don't understand what he is doing. Maybe I ought to go back to bed.

 

[JW61]

Sorry... time is hundreds without decimals... 4000 = 40.00. 1160 = 11.60 hrs.

Yes OT is only calculated for any time worked over 4000 (40 hrs) for the week.

PHV... You are da'man. A cut and paste of your code from above works perfectly.

Many thanks. And a STAR.

 

[MoLaker]

Now it finally makes sense. Thanks for the explanation.

"Retired Programmer", that is. So, please be patient.