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
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.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
ORDER BY B.COUNT
Last edited by tamilselvan; 03-22-2005 at 07:35 PM.
Try OUTER JOIN.
Are you getting an error here?
Last edited by tamilselvan; 03-22-2005 at 07:39 PM.
Does not do much other than give me a normal join and not returning my locations where there a null value for count. I used the inline query with outer join to counter this but with this i can't get months to also outerjoin and display (in my original posted query).
One way i thought of doing this was generating an inline query that would pair each month with each location and then do the outer join however with over a 1000 locations and 12 months in a year this is not efficient.
Should Look like something like this
1000 Jan-04 1
1000 Feb-04 5
1000 Mar-04 0
1001 Jan-04 2
Click Here to Expand Forum to Full Width