DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Count Query with Months and Year

Threaded View

  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.

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