Complex Query -- Help needed

Question

Post reply

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: 12358 More actions · Answer 1

NPeeters

SSChampion

Points: 12358

July 17, 2003 at 2:09 am

#465782

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: 12358 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: 65111 More actions · Answer 3

Why not simply the query, like so

SELECT	Regions.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:

SELECT Regions.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

MikeMahony

SSC Eights!

Points: 891

July 17, 2003 at 7:53 am

#465844

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: 65111 More actions · Answer 6

David Burrows

SSC Guru

Points: 65111

July 17, 2003 at 7:54 am

#465845

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.