Count Query with Months and Year
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Count Query with Months and Year

  1. #1
    Join Date
    Mar 2004
    Posts
    55

    Question 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.LOCATIONTO_CHAR(WORKORDER.REPORTDATE,'YYYY-MM'))B
    WHERE A
    .LOCATION=B.LOCATION(+)
    ORDER BY B.COUNT 
    Last edited by tamilselvan; 03-22-2005 at 07:35 PM.

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Try OUTER JOIN.
    FROM WORKORDER,LOCATIONS
    WHERE WORKORDER.LOCATION(+)=LOCATIONS.LOCATION

    Are you getting an error here?

    Tamil
    Last edited by tamilselvan; 03-22-2005 at 07:39 PM.

  3. #3
    Join Date
    Mar 2004
    Posts
    55
    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

    Location
    1000 Jan-04 1
    1000 Feb-04 5
    1000 Mar-04 0
    .............................
    .............................

    1001 Jan-04 2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width