#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2011
    Posts
    6
    Rep Power
    0

    Help with SQL command grouping


    Hi,

    I have 3 tables: Deliveries, IssuedWarehouse, ReturnedStock.

    Deliveries:
    ID
    OrderNumber
    Material
    Width
    Gauge
    DelKG

    IssuedWarehouse:
    OrderNumber
    IssuedKG

    ReturnedStock:
    OrderNumber
    IssuedKG

    What I'd like to do is group all the orders by Material, Width and Gauge and then sum the amount delivered, issued to the warehouse and issued back to stock. This is the SQL that is really quite close:

    SELECT tblFILM07_DELIVERIES.Material, tblFILM07_DELIVERIES.Width, tblFILM07_DELIVERIES.Gauge, Count(tblFILM07_DELIVERIES.OrderNo) AS [Orders Placed], Sum(tblFILM07_DELIVERIES.DeldQtyKilos) AS [KG Delivered], Sum(WarehouseFilmIissued.[Qty Issued]) AS [Film Issued], Sum([Film Retns].[Qty Issued]) AS [Film Returned], [KG Delivered]-[Film Issued]+[Film Returned] AS [Qty Remaining]

    FROM (tblFILM07_DELIVERIES INNER JOIN WarehouseFilmIissued ON tblFILM07_DELIVERIES.OrderNo = WarehouseFilmIissued.[Order No From]) INNER JOIN [Film Retns] ON tblFILM07_DELIVERIES.OrderNo = [Film Retns].[Order No From]

    GROUP BY tblFILM07_DELIVERIES.Material, tblFILM07_DELIVERIES.Width, tblFILM07_DELIVERIES.Gauge, tblFILM07_DELIVERIES.ActDelDate
    HAVING (((tblFILM07_DELIVERIES.ActDelDate) Between [start date] And [end date]))

    ORDER BY tblFILM07_DELIVERIES.Material;

    This groups the products almost perfectly. However if you take a look at the results:

    Material Width Gauge Orders Placed Delivered Qnty Kilos Film Issued Film Returned Qty Remaining
    COEX-GLOSS 590 75 1 534 500 124 158
    COEX-MATT 1080 80 1 4226 4226 52 52
    CPP 660 38 8 6720 2768 1384 5336
    CPP 666 47 1 5677 5716 536 497
    CPP 690 65 2 1232 717 202 717
    CPP 760 38 3 3444 1318 510 2636
    CPP 770 38 4 4316 3318 2592 3590
    CPP 786 38 2 672 442 212 442
    CPP 800 47 1 1122 1122 116 116
    CPP 810 47 1 1127 1134 69 62
    CPP 810 47 2 2250 1285 320 1285
    CPP 1460 38 12 6540 4704 2442 4278
    LD 975 75 1 502 502 182 182
    LDPE 450 50 1 252 252 50 50
    LDPE 520 70 1 250 250 95 95
    LDPE 570 65 2 504 295 86 295
    LDPE 570 65 2 508 278 48 278
    LDPE 620 50 1 252 252 67 67
    LDPE 660 50 1 256 256 62 62
    LDPE 670 75 1 248 248 80 80
    LDPE 690 47 1 476 476 390 390
    LDPE 790 38 2 2104 1122 140 1122
    LDPE 790 50 1 286 286 134 134
    LDPE 790 50 1 250 250 125 125
    LDPE 810 30 1 4062 4062 100 100
    LDPE 843 33 1 408 408 835 835
    LDPE 850 80 1 412 412 34 34
    LDPE 855 30 1 740 740 83 83
    LDPE 880 60 1 304 304 130 130
    LDPE 900 70 2 1000 650 500 850
    LDPE 1017 60 1 1056 1056 174 174
    OPP 25 1100 1 381 381 95 95
    OPP 1000 30 2 1358 1112 300 546
    OPP 1000 30 1 1492 1491 100 101
    OPP 1200 20 1 418 417 461 462
    PET 760 12 3 1227 1876 132 -517

    You'll see that there are some materials that have the same width and gauge yet they are not grouped. I think this is because the delivered qty is different on the orders. For example:

    Material Width Gauge Orders Placed Delivered Qnty Kilos Film Issued Film Returned Qty Remaining
    LDPE 620 50 1 252 252 67 67
    LDPE 660 50 1 256 256 62 62

    I would like these two row's to be grouped. They have the same material, width and gauge but the delivered qty is different therefore it hasn't grouped it.

    Can anyway help me group these strange rows?

    Thanks in advance,

    Paul.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    Code:
    SELECT del.Material
         , del.Width
         , del.Gauge
         , COUNT(del.OrderNo) AS [Orders Placed]
         , SUM(del.DeldQtyKilos) AS [KG Delivered]
         , iss.[Film Issued]
         , ret.[Film Returned]
         , SUM(del.DeldQtyKilos) -
               iss.[Film Issued] +
               ret.[Film Returned] AS [Qty Remaining]
      FROM (
           tblFILM07_DELIVERIES AS del
    LEFT OUTER 
      JOIN ( SELECT [Order No From]
                  , SUM([Qty Issued]) AS [Film Issued]
               FROM WarehouseFilmIissued 
             GROUP
                 BY [Order No From] ) AS iss
        ON iss.[Order No From] = del.OrderNo 
           )
    LEFT OUTER 
      JOIN ( SELECT [Order No From]
                  , SUM([Qty Issued]) AS [Film Returned]
               FROM [Film Retns] 
             GROUP
                 BY [Order No From] ) AS ret
        ON ret.[Order No From] = del.OrderNo
     WHERE del.ActDelDate Between [start date] And [end date]
    GROUP 
        BY del.Material
         , del.Width
         , del.Gauge
         , iss.[Film Issued]
         , ret.[Film Returned]
    ORDER 
        BY del.Material
    when i got to your HAVING clause, i realized this wasn't for microsoft sql server, but instead, for microsoft access

    so i've moved the thread to the Databases forum
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2011
    Posts
    6
    Rep Power
    0
    Thank you very much. That's very helpful.

    Paul.

IMN logo majestic logo threadwatch logo seochat tools logo