Complex Query -- Help needed

  • I am trying to write a query that will return the following information sorted by region:

    Region description

    Total orders for the period selected

    Total "ASAP" orders

    Total "NON-ASAP" orders

    Orders with quote time <= 50 minutes

    Orders with quote time >50 and <=60 minutes

    Orders with quote time >60 and <=70 minutes

    Orders with quote time >70 and <=80 minutes

    Orders with quote time >80 and <=90 minutes

    I have the following query:

    SELECT Regions.Description,

    COUNT(oh.id) AS TotalOrders,

    (SELECT COUNT(d.id)

    FROM (SELECT OrderHeader.id FROM orderheader

    Where (OrderHeader.OrderType = 1)

    AND (orderheader.date_delivery >= '03/01/2003')

    AND (orderheader.date_delivery <= '03/31/2003')

    AND (orderheader.companyid = 1154)

    AND (orderheader.timedorder is NULL or orderheader.timedorder = 0)

    AND orderheader.time_targetdelivery <= '3:00:00 PM') AS d) AS TotalASAP,

    (SELECT COUNT(d.id)

    FROM (SELECT orderheader.id FROM orderheader

    Where (OrderHeader.OrderType = 1)

    AND (orderheader.date_delivery >= '03/01/2003')

    AND (orderheader.date_delivery <= '03/31/2003')

    AND (orderheader.companyid = 1154)

    AND (orderheader.timedorder = -1)

    AND orderheader.time_targetdelivery <= '3:00:00 PM') AS d) AS TotalNOASAP,

    (SELECT COUNT(d.id)

    FROM (SELECT orderheader.id FROM orderheader

    Where (OrderHeader.OrderType = 1)

    and isnull(quote_minutes,0) <=59

    AND(orderheader.ordertype = 1 )

    AND (orderheader.date_delivery >= '03/01/2003')

    AND (orderheader.date_delivery <= '03/31/2003')

    AND (orderheader.companyid = 1154)

    AND (orderheader.timedorder is NULL or orderheader.timedorder = 0)

    AND orderheader.time_targetdelivery <= '3:00:00 PM') AS d) AS fiftymins,

    (SELECT COUNT(d.id)

    FROM (SELECT orderheader.id FROM orderheader

    Where quote_minutes >= 60

    And quote_minutes <= 69

    AND(orderheader.ordertype = 1 )

    AND (orderheader.date_delivery >= '03/01/2003')

    AND (orderheader.date_delivery <= '03/31/2003')

    AND (orderheader.companyid = 1154)

    AND (orderheader.timedorder is NULL or orderheader.timedorder = 0)

    AND orderheader.time_targetdelivery <= '3:00:00 PM') AS d) AS sixtymins,

    (SELECT COUNT(d.id)

    FROM (SELECT orderheader.id FROM orderheader

    Where quote_minutes >= 70

    And quote_minutes <= 79

    AND(orderheader.ordertype = 1 )

    AND (orderheader.date_delivery >= '03/01/2003')

    AND (orderheader.date_delivery <= '03/31/2003')

    AND (orderheader.companyid = 1154)

    AND (orderheader.timedorder is NULL or orderheader.timedorder = 0)

    AND orderheader.time_targetdelivery <= '3:00:00 PM') AS d) AS seventymins,

    (SELECT COUNT(d.id) FROM (SELECT orderheader.id FROM orderheader

    Where quote_minutes >= 80 And quote_minutes <= 89

    AND(orderheader.ordertype = 1 )

    AND (orderheader.date_delivery >= '03/01/2003')

    AND (orderheader.date_delivery <= '03/31/2003')

    AND (orderheader.companyid = 1154)

    AND (orderheader.timedorder is NULL or orderheader.timedorder = 0)

    AND orderheader.time_targetdelivery <= '3:00:00 PM') AS d) AS eightymins,

    (SELECT COUNT(d.id)FROM (SELECT orderheader.id FROM orderheader

    Where quote_minutes >= 90

    AND(orderheader.ordertype = 1 )

    AND (orderheader.date_delivery >= '03/01/2003')

    AND (orderheader.date_delivery <= '03/31/2003')

    AND (orderheader.companyid = 1154)

    AND (orderheader.timedorder is NULL or orderheader.timedorder=0)

    AND orderheader.time_targetdelivery <= '3:00:00 PM') AS d) AS plusninetymins

    FROM OrderHeader OH

    LEFT OUTER JOIN Address ON Address.ID=OH.AddressID

    LEFT OUTER JOIN Zones ON Zones.ID = Address.Zone

    LEFT OUTER JOIN Regions ON Regions.ID = Zones.RegionID

    Where (oh.OrderType = 1)

    AND (oh.date_delivery >= '03/01/2003')

    AND (oh.date_delivery <= '03/31/2003')

    AND (oh.companyid = 1154)

    AND (oh.time_targetdelivery <= '3:00:00 PM')

    GROUP BY Regions.ID, Regions.Description

    ORDER BY Regions.Description

    However, the above query returns all the regions and has their total order count correct, but it returns the same row for every other item in the query (ie. the total ASAP for all regions, etc.). How can I get this query modified so that it returns the information I'm looking for broken down by region?

    Thanks!

    Mike

  • You should add a link between the main query and all of the correlated subqueries. Probably on the Region_ID field...

    
    
    SELECT Region_id, Region_Description,
    (SELECT Count(d.id) FROM ...
    WHERE <inner select>.Region_id = Region_id)

    Maybe think about making a join. (See next post .

  • An alternative would be to build all of the totals you want in a single query, and then to join all of the queries together on the Region_id field. I think this will improve performance.

    One of the subqueries would be :

    
    
    SELECT Region_id, COUNT(*) as Number
    FROM orderheader
    Where (OrderHeader.OrderType = 1)
    AND (orderheader.date_delivery >= '03/01/2003')
    AND (orderheader.date_delivery <= '03/31/2003')
    AND (orderheader.companyid = 1154)
    AND (orderheader.timedorder is NULL
    or orderheader.timedorder = 0)
    AND orderheader.time_targetdelivery <= '3:00:00 PM'
    GROUP BY Region_ID)AS TotalASAP

    Note the GROUP BY clause. Next, you add all of the subqueries to your main FROM clause and join everything together.

    
    
    SELECT MIN(Main.Region_Description),
    Count(*) As TotalOrders,
    TotalASAP.Number
    FROM OrderHeader Main
    INNER JOIN (SELECT ...) AS TotalASAP
    ON Main.Region_ID = TotalASAP.Region_ID
    INNER JOIN (SELECT ...) AS TotalNoASAP
    ON Main.Region_ID = TotalNoASAP.Region_ID
    GROUP BY Main.Region_ID
  • Why not simply the query, like so

    SELECTRegions.Description, 
    
    COUNT(oh.id) AS TotalOrders,
    SUM(CASE WHEN (ISNULL(oh.timedorder,0) = 0)
    THEN 1 ELSE 0 END) AS TotalASAP,
    SUM(CASE WHEN (oh.timedorder = -1)
    THEN 1 ELSE 0 END) AS TotalNOASAP,
    SUM(CASE WHEN (isnull(oh.quote_minutes,0) <=59) AND ISNULL(oh.timedorder,0)
    THEN 1 ELSE 0 END) AS fiftymins,
    SUM(CASE WHEN (oh.quote_minutes >= 60 AND oh.quote_minutes <= 69) AND ISNULL(oh.timedorder,0)
    THEN 1 ELSE 0 END) AS sixtymins,
    SUM(CASE WHEN (oh.quote_minutes >= 70 AND oh.quote_minutes <= 79) AND ISNULL(oh.timedorder,0)
    THEN 1 ELSE 0 END) AS seventymins,
    SUM(CASE WHEN (oh.quote_minutes >= 80 And oh.quote_minutes <= 89) AND ISNULL(oh.timedorder,0)
    THEN 1 ELSE 0 END) AS eightymins,
    SUM(CASE WHEN (oh.quote_minutes >= 90 AND ISNULL(oh.timedorder,0)
    THEN 1 ELSE 0 END) AS plusninetymins,
    FROM OrderHeader oh
    LEFT OUTER JOIN Address ON Address.ID=OH.AddressID
    LEFT OUTER JOIN Zones ON Zones.ID = Address.Zone
    LEFT OUTER JOIN Regions ON Regions.ID = Zones.RegionID
    Where (oh.OrderType = 1)
    AND (oh.date_delivery >= '03/01/2003')
    AND (oh.date_delivery <= '03/31/2003')
    AND (oh.companyid = 1154)
    AND (oh.time_targetdelivery <= '3:00:00 PM')
    GROUP BY Regions.ID, Regions.Description
    ORDER BY Regions.Description

    Far away is close at hand in the images of elsewhere.
    Anon.

  • When I try this I get an error:

    SELECTRegions.Description, COUNT(oh.id) AS TotalOrders, SUM(CASE WHEN (ISNULL(oh.timedorder,0) = 0) THEN 1 ELSE 0 END) AS TotalASAP, SUM(CASE WHEN (oh.timedorder = -1) THEN 1 ELSE 0 END) AS TotalNOASAP, SUM(CASE WHEN (isnull(oh.quote_minutes,0) <=59) AND ISNULL(oh.timedorder,0)THEN 1 ELSE 0 END) AS fiftymins, SUM(CASE WHEN (oh.quote_minutes >= 60 AND oh.quote_minutes <= 69) AND ISNULL(oh.timedorder,0)THEN 1 ELSE 0 END) AS sixtymins, SUM(CASE WHEN (oh.quote_minutes >= 70 AND oh.quote_minutes <= 79) AND ISNULL(oh.timedorder,0)THEN 1 ELSE 0 END) AS seventymins, SUM(CASE WHEN (oh.quote_minutes >= 80 And oh.quote_minutes <= 89) AND ISNULL(oh.timedorder,0)THEN 1 ELSE 0 END) AS eightymins, SUM(CASE WHEN (oh.quote_minutes >= 90 AND ISNULL(oh.timedorder,0)THEN 1 ELSE 0 END) AS plusninetymins, FROM OrderHeader oh LEFT OUTER JOIN Address ON Address.ID=OH.AddressID LEFT OUTER JOIN Zones ON Zones.ID = Address.Zone LEFT OUTER JOIN Regions ON Regions.ID = Zones.RegionID Where (oh.OrderType = 1) AND (oh.date_delivery >= '03/01/2003') AND (oh.date_delivery <= '03/31/2003') AND (oh.companyid = 1154) AND (oh.time_targetdelivery <= '3:00:00 PM') GROUP BY Regions.ID, Regions.DescriptionORDER BY Regions.Description

    The erorr is INCORRECT SYNTAX NEAR THE KEYWORD 'THEN'

  • I fixed the error and the CASE way works great! Thanks alot to everyone who replied. I learned alot more about how to create these complex queries. I appreciate all your help!

  • The problem is in the last case (could not test this). It needs an extra bracket

    SUM(CASE WHEN (oh.quote_minutes >= 90 AND ISNULL(oh.timedorder,0))
    
    THEN 1 ELSE 0 END) AS plusninetymins,

    Far away is close at hand in the images of elsewhere.
    Anon.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply