Complex Query -- Help needed

Question

Complex Query -- Help needed

MikeMahony

SSC Eights!

Points: 891
More actions
July 16, 2003 at 8:18 pm

#82101

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

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

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

NPeeters SSChampion Points: 12554 More actions · Answer 1

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 .

NPeeters SSChampion Points: 12554 More actions · Answer 2

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

David Burrows SSC Guru Points: 65143 More actions · Answer 3

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.

MikeMahony SSC Eights! Points: 891 More actions · Answer 4

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'

MikeMahony SSC Eights! Points: 891 More actions · Answer 5

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!

David Burrows SSC Guru Points: 65143 More actions · Answer 6

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.