Calculating Overtime in select statement

Question

Post reply

Calculating Overtime in select statement

jjkky

Old Hand

Points: 318
More actions
November 27, 2007 at 7:12 am

#180854

Hi, I'm new to sql and was wondering if it is possible to calculate overtime hours based on daily total hours for a given pay period? I need to be able to determine in what dept the overtime occurred as well.
I've attached the stored procedure hoping someone may be able to help. Thanks in advance.
I should point out that everything worked until I added the "OTimes = " case statement.
CREATE PROCEDURE _spHourlyTotals
@staffid varchar(5),
@paydate datetime
AS
declare @OTMax decimal
set @RegMax = 40.0
select Dept,
sum(TotalHours) as PayTotal,
sum(case when (Type = '10') then TotalHours else 0 end) as RegTotal,
sum(case when (Type = '4') then TotalHours else 0 end) as PTOTotal,
sum(case when (Type = '8') then TotalHours else 0 end) as SickBankTotal,
sum(case when (Type not in ('10','4','8')) then TotalHours else 0 end) as OtherTotal
OTime = case when (select sum(TotalHours)
from tadetail
where ( (StaffID = @staffid) and (PayPeriod = @paydate ))) > @RegMax
then (sum(TotalHours) - @RegMax)
end
from
tadetail
where
StaffID = @staffid and
(PayPeriod = @paydate)
Group by
Dept

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

allen davidson SSCertifiable Points: 7042 More actions · Answer 1

What error message are you getting, it should be possible - probably a syntax probllem.

jjkky Old Hand Points: 318 More actions · Answer 2

Yes - I am getting a syntax error and it is on the following part of the code.

It doesn't like the "OTime" and it doesn't like the ">" sign.

Thanks.

OTime = case when (select sum(TotalHours)

from tadetail

where ( (StaffID = @staffid) and (PayPeriod = @paydate ))) > @RegMax

then (sum(TotalHours) - @RegMax)

end

allen davidson SSCertifiable Points: 7042 More actions · Answer 3

Can you post the actual error message, I'm guesssing here (because I can't see the message) but you might need to wrap a SUM( ) around your CASE.

Loner SSC-Insane Points: 21279 More actions · Answer 4

I don't think you can put a 'SELECT' statement inside a CASE statement.

allen davidson SSCertifiable Points: 7042 More actions · Answer 5

So something like

declare @OTMax decimal

set @RegMax = 40.0

select Dept,

sum(TotalHours) as PayTotal,

sum(case when (Type = '10') then TotalHours else 0 end) as RegTotal,

sum(case when (Type = '4') then TotalHours else 0 end) as PTOTotal,

sum(case when (Type = '8') then TotalHours else 0 end) as SickBankTotal,

sum(case when (Type not in ('10','4','8')) then TotalHours else 0 end) as OtherTotal

OTime = case when sum(TotalHours) > @RegMax then sum(TotalHours) - @RegMax

else 0

end

from

tadetail

where

StaffID = @staffid and

(PayPeriod = @paydate)

Group by

Dept

jjkky Old Hand Points: 318 More actions · Answer 6

Here is the error message I get:

Error 170: Line 17: Incorrect syntax near 'OTime'.

Line 19: Incorrect syntax near '>'.

I've seen other code with select statments inside a case statement so I don't think that is the problem. Thanks.

allen davidson SSCertifiable Points: 7042 More actions · Answer 7

allen davidson

SSCertifiable

Points: 7042

November 27, 2007 at 7:57 am

#754571

I'm missing a comma after as OtherTotal

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

Lynn Pettis

SSC Guru

Points: 442467

November 27, 2007 at 7:57 am

#754572

Looks like a missing comma (,) after OtherTotal.

jjkky Old Hand Points: 318 More actions · Answer 9

SSC Veteran - Thanks. That got rid of the syntax errors so I will test this out with data.

Thanks again....

jjkky Old Hand Points: 318 More actions · Answer 10

SSC Veteran - The reason I put the select statement in the OTime variable was to get the sum of total hrs. The current procedure only calculates total hours by department, therefore no overtime is being reported due to breakdown of departments. Hopefully with the info you have generously supplied, I should be able to figure the rest out. Thanks again.