sql command

[zsyed]

I would like to perform a simple math on SQL select statement. can some body suggest how to do this? I would like to have a column (indirectlabor - indleave - indsick - indstat) as ind.

for example. The following code does not work.

SELECT lmltimecardid as timecard,

SUM( case lmlTimecardType when 1 then lmllaborhours else 0 end) directlabor,
SUM( case lmlTimecardType when 2 then lmllaborhours else 0 end) indirectlabor,
SUM( case lmlTimecardType when 3 then lmllaborhours else 0 end) idlelabor,
SUM(case lmlIndirectLaborID when 'LEAVE' then lmllaborhours else 0 end) IndLeave,
SUM(case lmlIndirectLaborID when 'SICK' then lmllaborhours else 0 end) IndSick,
SUM(case lmlIndirectLaborID when 'STAT' then lmllaborhours else 0 end) IndStat,

(indirectlabor - indleave - indsick - indstat) as ind

FROM TIMECARDLINES GROUP BY LMLTIMECARDID

 

[briangriffin]

You just want the SQL to execute? The case statement syntax was wrong, but otherwise this should work in most platforms:

SELECT lmltimecardid as timecard,

SUM( case when lmlTimecardType = 1 then lmllaborhours else 0 end) as directlabor,

etc. etc.

You will not be able to sum the aliases like this:

(indirectlabor - indleave - indsick - indstat) as ind

You'll have to use the sum formulas:

SUM( case when lmlTimecardType = 2 then lmllaborhours else 0 end) -
SUM(case lmlIndirectLaborID when 'LEAVE' then lmllaborhours else 0 end)

etc. etc.

 

[zsyed]

yes, I was executing this SQL command. Infact both cases syntaxes working for me. like (case lmltimecardtype when 1) as well as (case when lmltimecardtype = 1). However , I kept the later one. Thanks for the idea of not using aliases that worked well.

Thanks,
zsyed