Please help me writing the sql query .
I have got a DataBase table with fields and Data like this
------------------
job_id hours invoice_id
-------------------
1 2 null(MEANS NOT INVOICED)
1 3 2(MEANS INVOICED)
2 4 3(MEANS INVOICED)
2 5 null(MEANS NOT INVOICED)
---------------------
Now I want to show a report which shows HOW many hours are invoiced and not invoiced
on a JOB. The Report should be some thing like this
-----------------------
Job_Id Hours_Invoiced Hours_Not_Invoiced
1 3 2
2 4 5
------------------------
Please help me writing the sql query to return data like above
ThanksAssuming Invoice_id is a number greater than or equal to zero, use IsNull to convert null Invoice_id's to -1 and then use that to segregate the values. Something like:
SELECT
job_Id,
Sum( CASE WHEN IsNull(invoice_id,-1) >= 0 THEN hours ELSE NULL END) as Hour_Invoiced,
Sum( CASE WHEN IsNull(invoice_id,-1) = -1 THEN hours ELSE NULL END) as Hours_Not_Invoiced
FROM YourTable
Group BY Job_Id
Order BY JobId
