can't get the total right

[Terris]

the statement below does not sum in the else part of the case statement so my totals are off with only 'P' flabtypes being summed. Help!?
SELECT FEMPNO, fcearncode,
CASE WHEN Max(Ladetail.flabtype) = 'P' THEN sum(Ladetail.Fchrglab * 60)
else sum(DateDiff(ss, Ladetail.fsdatetime, Ladetail.fedatetime))
END AS LABSeconds
FROM ladetail
Where LADETAIL.FDATE >= { d '2003-05-12' } and LADETAIL.FDATE <= { d '2003-05-18' }
group by fempno, fcearncode
order by fempno, fcearncode

 

[swampBoogie]

Well, I'm not sure what you are after

SELECT FEMPNO, fcearncode,
sum(case WHEN Ladetail.flabtype = 'P' THEN  
Ladetail.Fchrglab * 60)
else DateDiff(ss, Ladetail.fsdatetime, Ladetail.fedatetime)
END AS LABSeconds
FROM ladetail
Where LADETAIL.FDATE >= { d '2003-05-12' } 
and LADETAIL.FDATE <= { d '2003-05-18' }
group by fempno,  fcearncode
order by fempno,  fcearncode

If not, give some sample data and the expected result

 

[Terris]

swampboogie (love the name)
I tried this format and got the following error
Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near ')'.
I am trying to sum up total working seconds for employees that are barcoding into our ERP system (evetually to create a text file to share with the payroll system). As you can see there are two types of labor accumulations based on the flabtype field and for whatever reason the datediff will not work in the case statement. Here is some sample data wher fp=fpaytype and fe=fcearcode in this particular example I am only getting the 'P' types for a total seconds of 27360. The fchrglab is in minutes I am converting to seconds for closest number to actual worked.
fchrglab fempno fp fe fsdatetime fedatetime
.00 0630 1 2003-05-13 06:30:00.000 2003-05-13 07:39:00.000
188.6667 0630 P 1 2003-05-13 07:39:00.000 2003-05-13 15:15:00.000
78.6667 0630 P 1 2003-05-13 07:39:00.000 2003-05-13 11:35:00.000
188.6667 0630 P 1 2003-05-13 07:39:00.000 2003-05-13 15:15:00.000
.0000 0630 1 2003-05-14 06:26:00.000 2003-05-14 11:07:00.000

 

[ClaireHCM]

how about modifying the group by to
....
group by fempno,fcearncode,fp
order by fempno,fcearncode,fp

 

[Terris]

but them amounts need to be combined into one record. There needs to be one record containine total time for each employee, each earning code. when you add the group by fp it works great except how do I then combine the two

 

[ClaireHCM]

Terris,

actually,I still cant understand what is the meaning of your requirement.
If you want to add the sum of 'P' and the sum of '' together.Why you need to deal it with case??You could directly sum the whole column ,right?

If not,then why you need to sum them up?

Could you be more specific?

 

[Terris]

'p' type items also have start and end date/times but it is calculated differently. the 'p' represents a parallel labor calculation so they clock in and out but the minutes are all jobs running divided by the number of jobs so I cannot just add the columns

 

[plantj]

swampBoogie's answer is ok I think but it had a typo. Try the following.

SELECT FEMPNO, fcearncode,
sum(case WHEN Ladetail.flabtype = 'P' THEN
Ladetail.Fchrglab * 60
else DateDiff(ss, Ladetail.fsdatetime, Ladetail.fedatetime)
END) AS LABSeconds
FROM ladetail
Where LADETAIL.FDATE >= { d '2003-05-12' }
and LADETAIL.FDATE <= { d '2003-05-18' }
group by fempno, fcearncode
order by fempno, fcearncode

 

[Terris]

That did it you guys ROCK, cannot tell you the grief this has caused me (not to mention the few extra grays)
Thanks again!