I am using a mysql case for some calculation and my demand is that if the output is negative then i will show 0 in the place of the negative column.Otherwise the positive value.
Let me post the query:
select
concat(jo.title,' (', CCP.name, ')'), PL.analyst, PL.consultant,
PL.csm,
(CASE
WHEN PL.productType like 'Staffing' THEN (SELECT ((DATEDIFF(PL.dateEnd, PL.dateClientEffective)) -((WEEK(PL.dateEnd) - WEEK(PL.dateClientEffective)) * 2) - (case when weekday(PL.dateEnd) = 6 then 1 else 0 end) - (case when weekday(PL.dateClientEffective) = 5 then 1 else 0 end)) as DifD) * 8 * (PL.clientBillRate-PL.payRate) ELSE (PL.salary*PL.fee)END) Value
please check this, haven't tested yet.
Note: this might not be the best solution
select concat(jo.title,' (', CCP.name, ')'), PL.analyst, PL.consultant, PL.csm,
if(
(CASE WHEN PL.productType like 'Staffing' THEN (SELECT ((DATEDIFF(PL.dateEnd, PL.dateClientEffective)) -((WEEK(PL.dateEnd) - WEEK(PL.dateClientEffective)) * 2) -
(case when weekday(PL.dateEnd) = 6 then 1 else 0 end) -
(case when weekday(PL.dateClientEffective) = 5 then 1 else 0 end)) as DifD) * 8 * (PL.clientBillRate-PL.payRate) ELSE (PL.salary*PL.fee)END)
< 0,0,
(CASE WHEN PL.productType like 'Staffing' THEN (SELECT ((DATEDIFF(PL.dateEnd, PL.dateClientEffective)) -((WEEK(PL.dateEnd) - WEEK(PL.dateClientEffective)) * 2) -
(case when weekday(PL.dateEnd) = 6 then 1 else 0 end) -
(case when weekday(PL.dateClientEffective) = 5 then 1 else 0 end)) as DifD) * 8 * (PL.clientBillRate-PL.payRate) ELSE (PL.salary*PL.fee)END)
) Value