How to conditional IIF in query.

[contiw]

SELECT COUNT(*) as reccount,
SUM(j.duration*#session.payInter.translatorRate#) as tot2pay,
SUM(j.duration) as totduration
FROM jobsDb j
WHERE j.linguistId = #linguistId#
AND j.status = 'completed'
AND j.interInvoiceBilled is not null
AND j.interInvoicePaid is null

j.duration could be a value less than 1 (fraction of hour)
in that case it need to be converted to 1
otherwise use the original j.duration value.

Thanks in advance.

 

[SQLScholar]

try something like

sum(if(j.duration<1,1,j.duration)

So this says do the sum, but if j.duration is less then one, then make the figure you add to the sum one.... else make it the original figure.

Hope this works

Dan

----------------------------------------
There are 2 types of computer, the prototype and the obsolete!!