Hello.
I have an sql statement as follows
select Job_No,
IFNULL((select sum(Paying_Rate*Working_Hrs) from labour where Job_No = job.Job_No),0) as Labsubtotal,
IFNULL((select sum(Quantity*Unit_Price+(Quantity*Unit_Price*+Additional_Cost/100)) from material where Job_No = job.Job_No),0) as Matsubtotal,
[b]IFNULL((select sum(Timesheet_Hrs) from timesheet where Job_No = job.Job_No),0) as TSH,
IFNULL((select sum(Working_Hrs) from labour where Job_No = job.Job_No),0) as JSH,[/b]
(select Labsubtotal*0.175) as Labvat,
(select Matsubtotal*0.175) as Matvat,
(select Matsubtotal + Labsubtotal) as subtotal,
(select Labsubtotal*0.175 + Matsubtotal*0.175 ) as vat,
(select Labsubtotal*1.175 + Matsubtotal*1.175) as TOTAL,
job.`Order_Date`,
job.`File_No`,
job.`Contract`,
job.`Order_Site_Address`,
job.`Job_Description`,
job.`Invoice_Tax_Date`,
job.`CustomerRef`,
job.`Customer_Name`,
customer.Billing_Address
From `job`
left join customer
on(job.Customer_Name=customer.Customer_Name)
The two lines I have highlighted are TSH which is Time Sheet Hours and JSH which is Job Sheet Hours. At the end of a day, the engineer will return there job sheets with the hours worked on the back i.e. 2 hours. At the beginning of a week, the engineer will submit there weekly time sheets they might have put 3 hours for the job they have submitted as two hours.
Is there a way of automatically looking at the job and if the hours are different show the text in a different colour? maybe red if the TSH hours are < or > JSH or Green if the TSH are = to JSH?
Cheers
Paul