How to check negative values in mysql and decide the output after that

Question

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

Now if the value is negative, then it should show 0 else will show the original value

Answer 1

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

How to check negative values in mysql and decide the output after that

Sign up