Professional Web Applications Themes

Help with complex query - Microsoft SQL / MS SQL Server

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 for the region Total "NON-ASAP" orders for the region Orders with quote time <= 50 minutes for the region Orders with quote time >50 and <=60 minutes for the region Orders with quote time >60 and <=70 minutes for the region Orders with quote time >70 and <=80 minutes for the region Orders with quote time >80 and <=90 minutes for the region I have the following query: SELECT Regions.Description, COUNT(oh.id) AS TotalOrders, ...

  1. #1

    Default Help with complex query

    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 for the region
    Total "NON-ASAP" orders for the region
    Orders with quote time <= 50 minutes for the region
    Orders with quote time >50 and <=60 minutes for the region
    Orders with quote time >60 and <=70 minutes for the region
    Orders with quote time >70 and <=80 minutes for the region
    Orders with quote time >80 and <=90 minutes for the region

    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
    Mike Guest

  2. #2

    Default Re: Help with complex query

    By the way, here are some table relationships and what some of the
    data would look like:

    OrderHeader table:

    Contains an addressid field
    Contains Quote_Minutes field
    Contains a timedorder field (timed orders are NO-ASAP and the value
    would be -1)

    The address table:

    Contains a zoneid field

    Zones table:

    Contains a regionid field

    Regions table:

    Contains the region description

    To get a region I must link the OrderHeader to the address to the zone
    to the region.

    Sample data from OrderHeader:

    AddressID = 38488
    TimedOrder = 0
    Quote_Minutes = 53

    Sample data from address:

    ID = 38488
    ZoneID = 12

    Sample data from zone table:

    ID = 12
    RegionID = 19

    Sample data from region table:

    ID = 19
    Description = Huntington Beach/Fountain Valley

    Hope that helps!
    Mike Guest

  3. #3

    Default Re: Help with complex query

    Mike
    Please post DDL + sample data.




    "Mike" <com> wrote in message
    news:google.com... 


    Uri Guest

  4. #4

    Default Re: Help with complex query

    A rough outline of how it should look:


    SELECT Regions.Description,
    COUNT(oh.id) AS TotalOrders,
    COUNT(CASE WHEN COALESCE(oh.timedorder,0) = 0) THEN 0 END) AS TotalASAP,
    COUNT(CASE WHEN oh..timedorder = -1 THEN oh..timedorder END) AS TotalNOASAP,
    COUNT(CASE WHEN COALESCE(quote_minutes,0) <=59 THEN
    COALESCE(quote_minutes,0) END AS fiftymins,
    COUNT(CASE WHEN quote_minutes >= 60 And quote_minutes <= 69 THEN
    quote_minutes END AS sixty_minutes

    etc...


    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "Mike" <com> wrote in message
    news:google.com... 


    Jacco Guest

Similar Threads

  1. Complex Query Question
    By RonPCCSI in forum Coldfusion Database Access
    Replies: 3
    Last Post: July 21st, 07:41 PM
  2. Help! Complex query
    By RuBot in forum Coldfusion Database Access
    Replies: 5
    Last Post: March 7th, 12:47 AM
  3. Help for quite complex query.
    By _andrea.l in forum MySQL
    Replies: 3
    Last Post: January 1st, 04:22 PM
  4. Fairly Complex Query
    By Mike Davies in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 1st, 06:19 PM
  5. A complex query
    By Venkatesan M in forum Microsoft SQL / MS SQL Server
    Replies: 5
    Last Post: July 1st, 02:27 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139