This following block of SQL does a calculation:
UPDATE labordata SET rmrtotal = t1.sumtotal FROM labordata INNER JOIN
(SELECT IsNull(SUM((((labordata.co
st * labordata.qty) * (mbillcode.rmrperc / 100.0)) * (1 - (mbillcode.discper / 100)))),0) as sumtotal,
labordata.sanum,labordata.
jobnum,lab
ordata.alt
num,labord
ata.docid,
labordata.
laborid,mb
illcode.on
field,mbil
lcode.fixe
d
FROM mbillcode INNER JOIN labordata ON
labordata.sanum = mbillcode.sanum AND labordata.jobnum = mbillcode.jobnum AND labordata.altnum = mbillcode.altnum AND labordata.docid = mbillcode.docid
AND labordata.fldr = 'none' and labordata.laborid = mbillcode.laborid
WHERE mbillcode.sanum = @sanum and mbillcode.jobnum = @jobnum and mbillcode.altnum = @altnum and mbillcode.docid = @docid AND mbillcode.onfield = 'Labor Hours Cost' AND mbillcode.fixed = 0
GROUP BY labordata.sanum,labordata.
jobnum,lab
ordata.alt
num,labord
ata.docid,
labordata.
laborid,mb
illcode.on
field,mbil
lcode.fixe
d)
t1 ON t1.sanum = labordata.sanum AND t1.jobnum = labordata.jobnum AND t1.altnum = labordata.altnum AND t1.docid = labordata.docid
WHERE labordata.sanum = @sanum AND labordata.jobnum = @jobnum AND labordata.altnum = @altnum AND labordata.docid = @docid AND labordata.laborid = t1.laborid AND t1.onfield = 'Labor Hours Cost' AND t1.fixed = 0 and labordata.fldr = 'none'
If there are no rows returned from the joins, I need sumtotal to = 0. I was thinking to do an IF @@ROWCOUNT after but maybe there is a better way?
IF @@ROWCOUNT = 0 SET etc etc....
Thanks.