Count Query with Months and Year
I am trying to write a query that will give me a month count of jobs completed per location.
I have a Locations Table, Workorder Table.
The Jobs table whole the unique workorder details and a date stamp.
Now some locations over a year may not have a workorder completed in every month. However I do not want these locations excluded
I have managed to write an outer join query which captures every location regardless whether a job has been done or not however I cannot get the results to show the months where no job has been done for that month. If i try a second outer join its says I cannot have two outer joins.
I am open to suggestions or any other approach I could consider.
My current query is below
PHP Code:
SELECT A.LOCATION,A.DESCRIPTION,A.REGION,B.MONTH,B.COUNT FROM
(SELECT LOCATIONS.LOCATION"LOCATION",LOCATIONS.DESCRIPTION"DESCRIPTION",LOCATIONS.LO1"REGION" FROM LOCATIONS)A,
(SELECT WORKORDER.LOCATION "LOCATION",LOCATIONS.DESCRIPTION"DESCRIPTION",LOCATIONS.LO1"REGION",TO_CHAR(WORKORDER.REPORTDATE,'MON-YYYY')"MONTH",COUNT(WORKORDER.WONUM)"COUNT"
FROM WORKORDER,LOCATIONS WHERE WORKORDER.LOCATION=LOCATIONS.LOCATION
AND WORKORDER.CAPEX='N'
AND WORKORDER.STATUS NOT IN ('CAN','ABORT')
AND REPORTDATE BETWEEN (:DATE_BEGIN) AND (:DATE_END +1)
GROUP BY WORKORDER.LOCATION,LOCATIONS.DESCRIPTION,LOCATIONS.LO1,TO_CHAR(WORKORDER.REPORTDATE,'MON-YYYY'),TO_CHAR(WORKORDER.REPORTDATE,'YYYY-MM')
ORDER BY WORKORDER.LOCATION, TO_CHAR(WORKORDER.REPORTDATE,'YYYY-MM'))B
WHERE A.LOCATION=B.LOCATION(+)
ORDER BY B.COUNT
