Extracting from linked tables into pivot

microsoft.public.sqlserver.programming

13 Jan 2006 9:56 AM

CyberFox

Hi there,
I've managed to come this far and I need a bit of help to finalise. I'm
extracting records correctly using the following:

select substring(D.MStockCode,1,3) as Style,
    sum(case month(D.MLineShipDate)
        when 1 then D.MBackOrderQty*substring(D.MStockCode,17,1) else 0
        end) as Jan,
    sum(case month(D.MLineShipDate)
        when 2 then D.MBackOrderQty*substring(D.MStockCode,17,1) else 0
        end) as Feb,
    sum(case month(D.MLineShipDate)
        when 3 then D.MBackOrderQty*substring(D.MStockCode,17,1) else 0
        end) as Mar,
    sum(case month(D.MLineShipDate)
        when 4 then D.MBackOrderQty*substring(D.MStockCode,17,1) else 0
        end) as Apr,
    sum(case month(D.MLineShipDate)
        when 5 then D.MBackOrderQty*substring(D.MStockCode,17,1) else 0
        end) as May,
    sum(case month(D.MLineShipDate)
        when 6 then D.MBackOrderQty*substring(D.MStockCode,17,1) else 0
        end) as Jun
from SorDetail D LEFT JOIN
    SorMaster M ON M.SalesOrder = D.SalesOrder LEFT JOIN
    InvWarehouse W ON D.MStockCode = W.StockCode
where (M.OrderStatus = '1' or M.OrderStatus = 'S') and D.MBackOrderQty > 0
group by substring(D.MStockCode,1,3)
order by substring(D.MStockCode,1,3)

What I need to add is the quantity on hand from the InvWarehouse table. My
stock codes look something like this: 002-0300-10-WW-01 where the first 3
digits indicate the style (as in my code). In the InvWarehouse table there
are the same codes with a quantity on hand, and what I need is to sum the
total quantity on hand per style and add this field to the pivot (per style).

Thanking you in advance.
Kind regards,

13 Jan 2006 2:00 PM

John Bell

Hi

You are alredy joining to the InvWarehouse table therefore (if I understand
your problem) your summation of the quantity should be an extension of what
you have!

select substring(D.MStockCode,1,3) as Style,
    sum(case month(D.MLineShipDate)
        when 1 then D.MBackOrderQty*CAST(substring(D.MStockCode,17,1) AS int) else 0
        end) as Jan,
    sum(case month(D.MLineShipDate)
        when 1 then w.quantity else 0
        end) as JanQuantity,
....

from SorDetail D LEFT JOIN
    SorMaster M ON M.SalesOrder = D.SalesOrder LEFT JOIN
    InvWarehouse W ON D.MStockCode = W.StockCode
where (M.OrderStatus = '1' or M.OrderStatus = 'S') and D.MBackOrderQty > 0
group by substring(D.MStockCode,1,3)
order by substring(D.MStockCode,1,3)

If this is not the case, Posting DDL and example data would help see
http://www.aspfaq.com/etiquette.asp?id=5006 also the expected results from
the sample data would be beneficial.

John

Show quote

"CyberFox" wrote:

> Hi there,
> I've managed to come this far and I need a bit of help to finalise. I'm
> extracting records correctly using the following:
>
> select substring(D.MStockCode,1,3) as Style,
>     sum(case month(D.MLineShipDate)
>         when 1 then D.MBackOrderQty*substring(D.MStockCode,17,1) else 0
>         end) as Jan,
>     sum(case month(D.MLineShipDate)
>         when 2 then D.MBackOrderQty*substring(D.MStockCode,17,1) else 0
>         end) as Feb,
>     sum(case month(D.MLineShipDate)
>         when 3 then D.MBackOrderQty*substring(D.MStockCode,17,1) else 0
>         end) as Mar,
>     sum(case month(D.MLineShipDate)
>         when 4 then D.MBackOrderQty*substring(D.MStockCode,17,1) else 0
>         end) as Apr,
>     sum(case month(D.MLineShipDate)
>         when 5 then D.MBackOrderQty*substring(D.MStockCode,17,1) else 0
>         end) as May,
>     sum(case month(D.MLineShipDate)
>         when 6 then D.MBackOrderQty*substring(D.MStockCode,17,1) else 0
>         end) as Jun
> from SorDetail D LEFT JOIN
>     SorMaster M ON M.SalesOrder = D.SalesOrder LEFT JOIN
>     InvWarehouse W ON D.MStockCode = W.StockCode
> where (M.OrderStatus = '1' or M.OrderStatus = 'S') and D.MBackOrderQty > 0
> group by substring(D.MStockCode,1,3)
> order by substring(D.MStockCode,1,3)
>
> What I need to add is the quantity on hand from the InvWarehouse table. My
> stock codes look something like this: 002-0300-10-WW-01 where the first 3
> digits indicate the style (as in my code). In the InvWarehouse table there
> are the same codes with a quantity on hand, and what I need is to sum the
> total quantity on hand per style and add this field to the pivot (per style).
>
> Thanking you in advance.
> Kind regards,

16 Jan 2006 6:11 AM

CyberFox

Hi John,
The summation of the quantities is not date-dependent. Let me explain
exactly what I want:
The InvWarehouse table has a quantity on hand per stock item (this is the
quantity in stock at the current time, and is not date-dependent at all).
What I want to show is the stock item (actually the style number, which is
the first 3 digits of the stock item), it's quantity on hand (the stock
item's), and the outstanding sales orders per style (date-dependent).
Hope this clarifies.
Rgds,

Show quote

"John Bell" wrote:

> Hi
>
> You are alredy joining to the InvWarehouse table therefore (if I understand
> your problem) your summation of the quantity should be an extension of what
> you have!
>
> select substring(D.MStockCode,1,3) as Style,
>     sum(case month(D.MLineShipDate)
>         when 1 then D.MBackOrderQty*CAST(substring(D.MStockCode,17,1) AS int) else 0
>         end) as Jan,
>     sum(case month(D.MLineShipDate)
>         when 1 then w.quantity else 0
>         end) as JanQuantity,
> ...
>
> from SorDetail D LEFT JOIN
>     SorMaster M ON M.SalesOrder = D.SalesOrder LEFT JOIN
>     InvWarehouse W ON D.MStockCode = W.StockCode
> where (M.OrderStatus = '1' or M.OrderStatus = 'S') and D.MBackOrderQty > 0
> group by substring(D.MStockCode,1,3)
> order by substring(D.MStockCode,1,3)
>
> If this is not the case, Posting DDL and example data would help see
> http://www.aspfaq.com/etiquette.asp?id=5006 also the expected results from
> the sample data would be beneficial.
>
> John
>
>
> "CyberFox" wrote:
>
> > Hi there,
> > I've managed to come this far and I need a bit of help to finalise. I'm
> > extracting records correctly using the following:
> >
> > select substring(D.MStockCode,1,3) as Style,
> >     sum(case month(D.MLineShipDate)
> >         when 1 then D.MBackOrderQty*substring(D.MStockCode,17,1) else 0
> >         end) as Jan,
> >     sum(case month(D.MLineShipDate)
> >         when 2 then D.MBackOrderQty*substring(D.MStockCode,17,1) else 0
> >         end) as Feb,
> >     sum(case month(D.MLineShipDate)
> >         when 3 then D.MBackOrderQty*substring(D.MStockCode,17,1) else 0
> >         end) as Mar,
> >     sum(case month(D.MLineShipDate)
> >         when 4 then D.MBackOrderQty*substring(D.MStockCode,17,1) else 0
> >         end) as Apr,
> >     sum(case month(D.MLineShipDate)
> >         when 5 then D.MBackOrderQty*substring(D.MStockCode,17,1) else 0
> >         end) as May,
> >     sum(case month(D.MLineShipDate)
> >         when 6 then D.MBackOrderQty*substring(D.MStockCode,17,1) else 0
> >         end) as Jun
> > from SorDetail D LEFT JOIN
> >     SorMaster M ON M.SalesOrder = D.SalesOrder LEFT JOIN
> >     InvWarehouse W ON D.MStockCode = W.StockCode
> > where (M.OrderStatus = '1' or M.OrderStatus = 'S') and D.MBackOrderQty > 0
> > group by substring(D.MStockCode,1,3)
> > order by substring(D.MStockCode,1,3)
> >
> > What I need to add is the quantity on hand from the InvWarehouse table. My
> > stock codes look something like this: 002-0300-10-WW-01 where the first 3
> > digits indicate the style (as in my code). In the InvWarehouse table there
> > are the same codes with a quantity on hand, and what I need is to sum the
> > total quantity on hand per style and add this field to the pivot (per style).
> >
> > Thanking you in advance.
> > Kind regards,

16 Jan 2006 11:57 AM

John Bell

Hi

DDL, Example data and expected output would have eliminated any ambiguity
when you post. These are untested:

If you just want to sum the QuantityInHand values using the same where
clause as your main query then you can do that with

SELECT substring(D.MStockCode,1,3) as Style,
    sum(case month(D.MLineShipDate)
        when 1 then D.MBackOrderQty*CAST(substring(D.MStockCode,17,1) AS int)
else 0
        end) as Jan,
....
SUM(D.QuantityInHand) AS InHand
from SorDetail D LEFT JOIN
    SorMaster M ON M.SalesOrder = D.SalesOrder LEFT JOIN
    InvWarehouse W ON D.MStockCode = W.StockCode
where (M.OrderStatus = '1' or M.OrderStatus = 'S')
and D.MBackOrderQty > 0
group by substring(D.MStockCode,1,3)
order by substring(D.MStockCode,1,3)

If you don't want that restriction then a subquery may be needed:
SELECT substring(D.MStockCode,1,3) as Style,
    sum(case month(D.MLineShipDate)
        when 1 then D.MBackOrderQty*CAST(substring(D.MStockCode,17,1) AS int)
else 0
        end) as Jan,
....
( SELECT SUM(I.QuantityInHand) FROM InvWarehouse I WHERE
substring(D.MStockCode,1,3) = substring(I.MStockCode,1,3) ) AS InHand
from SorDetail D LEFT JOIN
    SorMaster M ON M.SalesOrder = D.SalesOrder LEFT JOIN
    InvWarehouse W ON D.MStockCode = W.StockCode
where (M.OrderStatus = '1' or M.OrderStatus = 'S')
and D.MBackOrderQty > 0
group by substring(D.MStockCode,1,3)
order by substring(D.MStockCode,1,3)

or possibly using derived tables and joining them
SELECT A.Style, A.Jan, A.Feb,.... B.Total
FROM
( SELECT substring(D.MStockCode,1,3) as Style,
    sum(case month(D.MLineShipDate)
        when 1 then D.MBackOrderQty*CAST(substring(D.MStockCode,17,1) AS int)
else 0
        end) as Jan,
....
from SorDetail D LEFT JOIN
    SorMaster M ON M.SalesOrder = D.SalesOrder LEFT JOIN
    InvWarehouse W ON D.MStockCode = W.StockCode
where (M.OrderStatus = '1' or M.OrderStatus = 'S')
and D.MBackOrderQty > 0
group by substring(D.MStockCode,1,3) ) A
JOIN
( SELECT substring(D.MStockCode,1,3) as Style,
SUM ( D.QuantityInHand ) AS InHand
from SorDetail D LEFT JOIN
    SorMaster M ON M.SalesOrder = D.SalesOrder LEFT JOIN
    InvWarehouse W ON D.MStockCode = W.StockCode
where (M.OrderStatus = '1' or M.OrderStatus = 'S')
and D.MBackOrderQty > 0
group by substring(D.MStockCode,1,3) ) B ON A.Style = B.Style
ORDER BY A.Style

John
Show quote

"CyberFox" wrote:

> Hi John,
> The summation of the quantities is not date-dependent. Let me explain
> exactly what I want:
> The InvWarehouse table has a quantity on hand per stock item (this is the
> quantity in stock at the current time, and is not date-dependent at all).
> What I want to show is the stock item (actually the style number, which is
> the first 3 digits of the stock item), it's quantity on hand (the stock
> item's), and the outstanding sales orders per style (date-dependent).
> Hope this clarifies.
> Rgds,
>
> "John Bell" wrote:
>
> > Hi
> >
> > You are alredy joining to the InvWarehouse table therefore (if I understand
> > your problem) your summation of the quantity should be an extension of what
> > you have!
> >
> > select substring(D.MStockCode,1,3) as Style,
> >     sum(case month(D.MLineShipDate)
> >         when 1 then D.MBackOrderQty*CAST(substring(D.MStockCode,17,1) AS int) else 0
> >         end) as Jan,
> >     sum(case month(D.MLineShipDate)
> >         when 1 then w.quantity else 0
> >         end) as JanQuantity,
> > ...
> >
> > from SorDetail D LEFT JOIN
> >     SorMaster M ON M.SalesOrder = D.SalesOrder LEFT JOIN
> >     InvWarehouse W ON D.MStockCode = W.StockCode
> > where (M.OrderStatus = '1' or M.OrderStatus = 'S') and D.MBackOrderQty > 0
> > group by substring(D.MStockCode,1,3)
> > order by substring(D.MStockCode,1,3)
> >
> > If this is not the case, Posting DDL and example data would help see
> > http://www.aspfaq.com/etiquette.asp?id=5006 also the expected results from
> > the sample data would be beneficial.
> >
> > John
> >
> >
> > "CyberFox" wrote:
> >
> > > Hi there,
> > > I've managed to come this far and I need a bit of help to finalise. I'm
> > > extracting records correctly using the following:
> > >
> > > select substring(D.MStockCode,1,3) as Style,
> > >     sum(case month(D.MLineShipDate)
> > >         when 1 then D.MBackOrderQty*substring(D.MStockCode,17,1) else 0
> > >         end) as Jan,
> > >     sum(case month(D.MLineShipDate)
> > >         when 2 then D.MBackOrderQty*substring(D.MStockCode,17,1) else 0
> > >         end) as Feb,
> > >     sum(case month(D.MLineShipDate)
> > >         when 3 then D.MBackOrderQty*substring(D.MStockCode,17,1) else 0
> > >         end) as Mar,
> > >     sum(case month(D.MLineShipDate)
> > >         when 4 then D.MBackOrderQty*substring(D.MStockCode,17,1) else 0
> > >         end) as Apr,
> > >     sum(case month(D.MLineShipDate)
> > >         when 5 then D.MBackOrderQty*substring(D.MStockCode,17,1) else 0
> > >         end) as May,
> > >     sum(case month(D.MLineShipDate)
> > >         when 6 then D.MBackOrderQty*substring(D.MStockCode,17,1) else 0
> > >         end) as Jun
> > > from SorDetail D LEFT JOIN
> > >     SorMaster M ON M.SalesOrder = D.SalesOrder LEFT JOIN
> > >     InvWarehouse W ON D.MStockCode = W.StockCode
> > > where (M.OrderStatus = '1' or M.OrderStatus = 'S') and D.MBackOrderQty > 0
> > > group by substring(D.MStockCode,1,3)
> > > order by substring(D.MStockCode,1,3)
> > >
> > > What I need to add is the quantity on hand from the InvWarehouse table. My
> > > stock codes look something like this: 002-0300-10-WW-01 where the first 3
> > > digits indicate the style (as in my code). In the InvWarehouse table there
> > > are the same codes with a quantity on hand, and what I need is to sum the
> > > total quantity on hand per style and add this field to the pivot (per style).
> > >
> > > Thanking you in advance.
> > > Kind regards,

16 Jan 2006 12:27 PM

CyberFox

John,
I've tried the sub-query option, but it didn't do what I was hoping for. Let
me simplify and give you some examples of my data, if you don't mind:

SorDetail table:
Itemcode                    BackOrderQty      OrderDate
002-0200-10-WW-02      100                  01/01/06
002-0200-11-WW-02      150                  02/01/06
002-0200-12-WW-02      150                  01/02/06
010-0300-16-ED-03         100                 01/01/06
010-0300-16-MK-01         200                01/01/06
010-0300-16-TR-02         100                 01/03/06

InvWarehouse table
ItemCode                    QtyOnHand
002-0200-10-WW-02    2000
002-0200-11-WW-02    1400
002-0200-12-WW-02    1500
010-0300-16-ED-03       1000
010-0300-16-MK-01      1000
010-0300-16-TR-02       1000

I need the following (considering that the style = first 3 digits of the
item codes)
Style          QtyOnHand          JanOrders       FebOrders     Mar
002             4900                   250                 150              0
010             3000                   300                  0
100

Thank you very much for your help so far...
Rgds,

Show quote

"John Bell" wrote:

> Hi
>
> DDL, Example data and expected output would have eliminated any ambiguity
> when you post. These are untested:
>
> If you just want to sum the QuantityInHand values using the same where
> clause as your main query then you can do that with
>
> SELECT substring(D.MStockCode,1,3) as Style,
>     sum(case month(D.MLineShipDate)
>         when 1 then D.MBackOrderQty*CAST(substring(D.MStockCode,17,1) AS int)
> else 0
>         end) as Jan,
> ...
> SUM(D.QuantityInHand) AS InHand
> from SorDetail D LEFT JOIN
>     SorMaster M ON M.SalesOrder = D.SalesOrder LEFT JOIN
>     InvWarehouse W ON D.MStockCode = W.StockCode
> where (M.OrderStatus = '1' or M.OrderStatus = 'S')
> and D.MBackOrderQty > 0
> group by substring(D.MStockCode,1,3)
> order by substring(D.MStockCode,1,3)
>
> If you don't want that restriction then a subquery may be needed:
> SELECT substring(D.MStockCode,1,3) as Style,
>     sum(case month(D.MLineShipDate)
>         when 1 then D.MBackOrderQty*CAST(substring(D.MStockCode,17,1) AS int)
> else 0
>         end) as Jan,
> ...
> ( SELECT SUM(I.QuantityInHand) FROM InvWarehouse I WHERE
> substring(D.MStockCode,1,3) = substring(I.MStockCode,1,3) ) AS InHand
> from SorDetail D LEFT JOIN
>     SorMaster M ON M.SalesOrder = D.SalesOrder LEFT JOIN
>     InvWarehouse W ON D.MStockCode = W.StockCode
> where (M.OrderStatus = '1' or M.OrderStatus = 'S')
> and D.MBackOrderQty > 0
> group by substring(D.MStockCode,1,3)
> order by substring(D.MStockCode,1,3)
>
> or possibly using derived tables and joining them
> SELECT A.Style, A.Jan, A.Feb,.... B.Total
> FROM
> ( SELECT substring(D.MStockCode,1,3) as Style,
>     sum(case month(D.MLineShipDate)
>         when 1 then D.MBackOrderQty*CAST(substring(D.MStockCode,17,1) AS int)
> else 0
>         end) as Jan,
> ...
> from SorDetail D LEFT JOIN
>     SorMaster M ON M.SalesOrder = D.SalesOrder LEFT JOIN
>     InvWarehouse W ON D.MStockCode = W.StockCode
> where (M.OrderStatus = '1' or M.OrderStatus = 'S')
> and D.MBackOrderQty > 0
> group by substring(D.MStockCode,1,3) ) A
> JOIN
> ( SELECT substring(D.MStockCode,1,3) as Style,
> SUM ( D.QuantityInHand ) AS InHand
> from SorDetail D LEFT JOIN
>     SorMaster M ON M.SalesOrder = D.SalesOrder LEFT JOIN
>     InvWarehouse W ON D.MStockCode = W.StockCode
> where (M.OrderStatus = '1' or M.OrderStatus = 'S')
> and D.MBackOrderQty > 0
> group by substring(D.MStockCode,1,3) ) B ON A.Style = B.Style
> ORDER BY A.Style
>
> John
> "CyberFox" wrote:
>
> > Hi John,
> > The summation of the quantities is not date-dependent. Let me explain
> > exactly what I want:
> > The InvWarehouse table has a quantity on hand per stock item (this is the
> > quantity in stock at the current time, and is not date-dependent at all).
> > What I want to show is the stock item (actually the style number, which is
> > the first 3 digits of the stock item), it's quantity on hand (the stock
> > item's), and the outstanding sales orders per style (date-dependent).
> > Hope this clarifies.
> > Rgds,
> >
> > "John Bell" wrote:
> >
> > > Hi
> > >
> > > You are alredy joining to the InvWarehouse table therefore (if I understand
> > > your problem) your summation of the quantity should be an extension of what
> > > you have!
> > >
> > > select substring(D.MStockCode,1,3) as Style,
> > >     sum(case month(D.MLineShipDate)
> > >         when 1 then D.MBackOrderQty*CAST(substring(D.MStockCode,17,1) AS int) else 0
> > >         end) as Jan,
> > >     sum(case month(D.MLineShipDate)
> > >         when 1 then w.quantity else 0
> > >         end) as JanQuantity,
> > > ...
> > >
> > > from SorDetail D LEFT JOIN
> > >     SorMaster M ON M.SalesOrder = D.SalesOrder LEFT JOIN
> > >     InvWarehouse W ON D.MStockCode = W.StockCode
> > > where (M.OrderStatus = '1' or M.OrderStatus = 'S') and D.MBackOrderQty > 0
> > > group by substring(D.MStockCode,1,3)
> > > order by substring(D.MStockCode,1,3)
> > >
> > > If this is not the case, Posting DDL and example data would help see
> > > http://www.aspfaq.com/etiquette.asp?id=5006 also the expected results from
> > > the sample data would be beneficial.
> > >
> > > John
> > >
> > >
> > > "CyberFox" wrote:
> > >
> > > > Hi there,
> > > > I've managed to come this far and I need a bit of help to finalise. I'm
> > > > extracting records correctly using the following:
> > > >
> > > > select substring(D.MStockCode,1,3) as Style,
> > > >     sum(case month(D.MLineShipDate)
> > > >         when 1 then D.MBackOrderQty*substring(D.MStockCode,17,1) else 0
> > > >         end) as Jan,
> > > >     sum(case month(D.MLineShipDate)
> > > >         when 2 then D.MBackOrderQty*substring(D.MStockCode,17,1) else 0
> > > >         end) as Feb,
> > > >     sum(case month(D.MLineShipDate)
> > > >         when 3 then D.MBackOrderQty*substring(D.MStockCode,17,1) else 0
> > > >         end) as Mar,
> > > >     sum(case month(D.MLineShipDate)
> > > >         when 4 then D.MBackOrderQty*substring(D.MStockCode,17,1) else 0
> > > >         end) as Apr,
> > > >     sum(case month(D.MLineShipDate)
> > > >         when 5 then D.MBackOrderQty*substring(D.MStockCode,17,1) else 0
> > > >         end) as May,
> > > >     sum(case month(D.MLineShipDate)
> > > >         when 6 then D.MBackOrderQty*substring(D.MStockCode,17,1) else 0
> > > >         end) as Jun
> > > > from SorDetail D LEFT JOIN
> > > >     SorMaster M ON M.SalesOrder = D.SalesOrder LEFT JOIN
> > > >     InvWarehouse W ON D.MStockCode = W.StockCode
> > > > where (M.OrderStatus = '1' or M.OrderStatus = 'S') and D.MBackOrderQty > 0
> > > > group by substring(D.MStockCode,1,3)
> > > > order by substring(D.MStockCode,1,3)
> > > >
> > > > What I need to add is the quantity on hand from the InvWarehouse table. My
> > > > stock codes look something like this: 002-0300-10-WW-01 where the first 3
> > > > digits indicate the style (as in my code). In the InvWarehouse table there
> > > > are the same codes with a quantity on hand, and what I need is to sum the
> > > > total quantity on hand per style and add this field to the pivot (per style).
> > > >
> > > > Thanking you in advance.
> > > > Kind regards,

16 Jan 2006 2:25 PM

John Bell

Hi

This post is inconsistent with the tables/columns that you have posted
previously so it is even more confusing, make sure that you read
http://www.aspfaq.com/etiquette.asp?id=5006 and post something usable.

With:

CREATE TABLE SorDetail ( Itemcode char(17),                   BackOrderQty
int,     OrderDate datetime)
CREATE TABLE InvWarehouse ( Itemcode char(17),                   QtyOnHand
int )

INSERT INTO SorDetail ( Itemcode, BackOrderQty, OrderDate)
SELECT '002-0200-10-WW-02',      100,                  '20060101'
UNION ALL SELECT '002-0200-11-WW-02',      150,                 '20060102'
UNION ALL SELECT '002-0200-12-WW-02',      150,                  '20060201'
UNION ALL SELECT '010-0300-16-ED-03',         100,                '20060101'
UNION ALL SELECT '010-0300-16-MK-01',         200,                '20060101'
UNION ALL SELECT '010-0300-16-TR-02',         100,                 '20060103'

INSERT INTO InvWarehouse ( Itemcode, QtyOnHand )
SELECT '002-0200-10-WW-02',    2000
UNION ALL SELECT '002-0200-11-WW-02',    1400
UNION ALL SELECT '002-0200-12-WW-02',    1500
UNION ALL SELECT '010-0300-16-ED-03',       1000
UNION ALL SELECT '010-0300-16-MK-01',      1000
UNION ALL SELECT '010-0300-16-TR-02',       1000

My query:
SELECT substring(D.Itemcode,1,3) as Style,
    sum(case month(D.OrderDate)
        when 1 then D.BackOrderQty
else 0
        end) as Jan,
    sum(case month(D.OrderDate)
        when 2 then D.BackOrderQty
else 0
        end) as Feb,
    sum(case month(D.OrderDate)
        when 3 then D.BackOrderQty
else 0
        end) as Mar,
( SELECT SUM(I.QtyOnHand) FROM InvWarehouse I WHERE
substring(D.Itemcode,1,3) = substring(I.Itemcode,1,3) ) AS InHand
from SorDetail D
LEFT JOIN InvWarehouse W ON D.Itemcode = W.Itemcode
WHERE D.BackOrderQty > 0
group by substring(D.Itemcode,1,3)
order by substring(D.Itemcode,1,3)

seems to give exaclty what you required, although it gives me an error if I
change the column order, so using:

SELECT A.Style, B.QtyOnHand, A.Jan, A.Feb, A.Mar
FROM
    ( SELECT SUBSTRING(D.Itemcode,1,3) as Style,
        SUM(CASE MONTH(D.OrderDate)
            WHEN 1 THEN D.BackOrderQty
    ELSE 0
            END) AS Jan,
        SUM(CASE MONTH(D.OrderDate)
            WHEN 2 THEN D.BackOrderQty
    ELSE 0
            END) AS Feb,
        SUM(CASE MONTH(D.OrderDate)
            WHEN 3 THEN D.BackOrderQty
    ELSE 0
            END) AS Mar
    FROM SorDetail D
    LEFT JOIN InvWarehouse W ON D.Itemcode = W.Itemcode
    WHERE D.BackOrderQty > 0
    GROUP BY SUBSTRING(D.Itemcode,1,3) ) A
LEFT JOIN ( SELECT SUBSTRING(i.Itemcode,1,3) as Style,
        SUM(I.QtyOnHand) AS QtyOnHand
        FROM InvWarehouse I
        GROUP BY SUBSTRING(I.Itemcode,1,3) ) B ON A.Style = B.Style
ORDER BY A.Style

may be a better option.

John
Show quote

"CyberFox" wrote:

> John,
> I've tried the sub-query option, but it didn't do what I was hoping for. Let
> me simplify and give you some examples of my data, if you don't mind:
>
> SorDetail table:
> Itemcode                    BackOrderQty      OrderDate
> 002-0200-10-WW-02      100                  01/01/06
> 002-0200-11-WW-02      150                  02/01/06
> 002-0200-12-WW-02      150                  01/02/06
> 010-0300-16-ED-03         100                 01/01/06
> 010-0300-16-MK-01         200                01/01/06
> 010-0300-16-TR-02         100                 01/03/06
>
> InvWarehouse table
> ItemCode                    QtyOnHand
> 002-0200-10-WW-02    2000
> 002-0200-11-WW-02    1400
> 002-0200-12-WW-02    1500
> 010-0300-16-ED-03       1000
> 010-0300-16-MK-01      1000
> 010-0300-16-TR-02       1000
>
> I need the following (considering that the style = first 3 digits of the
> item codes)
> Style          QtyOnHand          JanOrders       FebOrders     Mar
> 002             4900                   250                 150              0
> 010             3000                   300                  0
> 100
>
> Thank you very much for your help so far...
> Rgds,
>
> "John Bell" wrote:
>
> > Hi
> >
> > DDL, Example data and expected output would have eliminated any ambiguity
> > when you post. These are untested:
> >
> > If you just want to sum the QuantityInHand values using the same where
> > clause as your main query then you can do that with
> >
> > SELECT substring(D.MStockCode,1,3) as Style,
> >     sum(case month(D.MLineShipDate)
> >         when 1 then D.MBackOrderQty*CAST(substring(D.MStockCode,17,1) AS int)
> > else 0
> >         end) as Jan,
> > ...
> > SUM(D.QuantityInHand) AS InHand
> > from SorDetail D LEFT JOIN
> >     SorMaster M ON M.SalesOrder = D.SalesOrder LEFT JOIN
> >     InvWarehouse W ON D.MStockCode = W.StockCode
> > where (M.OrderStatus = '1' or M.OrderStatus = 'S')
> > and D.MBackOrderQty > 0
> > group by substring(D.MStockCode,1,3)
> > order by substring(D.MStockCode,1,3)
> >
> > If you don't want that restriction then a subquery may be needed:
> > SELECT substring(D.MStockCode,1,3) as Style,
> >     sum(case month(D.MLineShipDate)
> >         when 1 then D.MBackOrderQty*CAST(substring(D.MStockCode,17,1) AS int)
> > else 0
> >         end) as Jan,
> > ...
> > ( SELECT SUM(I.QuantityInHand) FROM InvWarehouse I WHERE
> > substring(D.MStockCode,1,3) = substring(I.MStockCode,1,3) ) AS InHand
> > from SorDetail D LEFT JOIN
> >     SorMaster M ON M.SalesOrder = D.SalesOrder LEFT JOIN
> >     InvWarehouse W ON D.MStockCode = W.StockCode
> > where (M.OrderStatus = '1' or M.OrderStatus = 'S')
> > and D.MBackOrderQty > 0
> > group by substring(D.MStockCode,1,3)
> > order by substring(D.MStockCode,1,3)
> >
> > or possibly using derived tables and joining them
> > SELECT A.Style, A.Jan, A.Feb,.... B.Total
> > FROM
> > ( SELECT substring(D.MStockCode,1,3) as Style,
> >     sum(case month(D.MLineShipDate)
> >         when 1 then D.MBackOrderQty*CAST(substring(D.MStockCode,17,1) AS int)
> > else 0
> >         end) as Jan,
> > ...
> > from SorDetail D LEFT JOIN
> >     SorMaster M ON M.SalesOrder = D.SalesOrder LEFT JOIN
> >     InvWarehouse W ON D.MStockCode = W.StockCode
> > where (M.OrderStatus = '1' or M.OrderStatus = 'S')
> > and D.MBackOrderQty > 0
> > group by substring(D.MStockCode,1,3) ) A
> > JOIN
> > ( SELECT substring(D.MStockCode,1,3) as Style,
> > SUM ( D.QuantityInHand ) AS InHand
> > from SorDetail D LEFT JOIN
> >     SorMaster M ON M.SalesOrder = D.SalesOrder LEFT JOIN
> >     InvWarehouse W ON D.MStockCode = W.StockCode
> > where (M.OrderStatus = '1' or M.OrderStatus = 'S')
> > and D.MBackOrderQty > 0
> > group by substring(D.MStockCode,1,3) ) B ON A.Style = B.Style
> > ORDER BY A.Style
> >
> > John
> > "CyberFox" wrote:
> >
> > > Hi John,
> > > The summation of the quantities is not date-dependent. Let me explain
> > > exactly what I want:
> > > The InvWarehouse table has a quantity on hand per stock item (this is the
> > > quantity in stock at the current time, and is not date-dependent at all).
> > > What I want to show is the stock item (actually the style number, which is
> > > the first 3 digits of the stock item), it's quantity on hand (the stock
> > > item's), and the outstanding sales orders per style (date-dependent).
> > > Hope this clarifies.
> > > Rgds,
> > >
> > > "John Bell" wrote:
> > >
> > > > Hi
> > > >
> > > > You are alredy joining to the InvWarehouse table therefore (if I understand
> > > > your problem) your summation of the quantity should be an extension of what
> > > > you have!
> > > >
> > > > select substring(D.MStockCode,1,3) as Style,
> > > >     sum(case month(D.MLineShipDate)
> > > >         when 1 then D.MBackOrderQty*CAST(substring(D.MStockCode,17,1) AS int) else 0
> > > >         end) as Jan,
> > > >     sum(case month(D.MLineShipDate)
> > > >         when 1 then w.quantity else 0
> > > >         end) as JanQuantity,
> > > > ...
> > > >
> > > > from SorDetail D LEFT JOIN
> > > >     SorMaster M ON M.SalesOrder = D.SalesOrder LEFT JOIN
> > > >     InvWarehouse W ON D.MStockCode = W.StockCode
> > > > where (M.OrderStatus = '1' or M.OrderStatus = 'S') and D.MBackOrderQty > 0
> > > > group by substring(D.MStockCode,1,3)
> > > > order by substring(D.MStockCode,1,3)
> > > >
> > > > If this is not the case, Posting DDL and example data would help see
> > > > http://www.aspfaq.com/etiquette.asp?id=5006 also the expected results from
> > > > the sample data would be beneficial.
> > > >
> > > > John
> > > >
> > > >
> > > > "CyberFox" wrote:
> > > >
> > > > > Hi there,
> > > > > I've managed to come this far and I need a bit of help to finalise. I'm
> > > > > extracting records correctly using the following:
> > > > >
> > > > > select substring(D.MStockCode,1,3) as Style,
> > > > >     sum(case month(D.MLineShipDate)
> > > > >         when 1 then D.MBackOrderQty*substring(D.MStockCode,17,1) else 0
> > > > >         end) as Jan,
> > > > >     sum(case month(D.MLineShipDate)
> > > > >         when 2 then D.MBackOrderQty*substring(D.MStockCode,17,1) else 0
> > > > >         end) as Feb,
> > > > >     sum(case month(D.MLineShipDate)
> > > > >         when 3 then D.MBackOrderQty*substring(D.MStockCode,17,1) else 0
> > > > >         end) as Mar,
> > > > >     sum(case month(D.MLineShipDate)
> > > > >         when 4 then D.MBackOrderQty*substring(D.MStockCode,17,1) else 0
> > > > >         end) as Apr,
> > > > >     sum(case month(D.MLineShipDate)
> > > > >         when 5 then D.MBackOrderQty*substring(D.MStockCode,17,1) else 0
> > > > >         end) as May,
> > > > >     sum(case month(D.MLineShipDate)
> > > > >         when 6 then D.MBackOrderQty*substring(D.MStockCode,17,1) else 0
> > > > >         end) as Jun
> > > > > from SorDetail D LEFT JOIN
> > > > >     SorMaster M ON M.SalesOrder = D.SalesOrder LEFT JOIN
> > > > >     InvWarehouse W ON D.MStockCode = W.StockCode
> > > > > where (M.OrderStatus = '1' or M.OrderStatus = 'S') and D.MBackOrderQty > 0
> > > > > group by substring(D.MStockCode,1,3)
> > > > > order by substring(D.MStockCode,1,3)
> > > > >
> > > > > What I need to add is the quantity on hand from the InvWarehouse table. My
> > > > > stock codes look something like this: 002-0300-10-WW-01 where the first 3
> > > > > digits indicate the style (as in my code). In the InvWarehouse table there
> > > > > are the same codes with a quantity on hand, and what I need is to sum the
> > > > > total quantity on hand per style and add this field to the pivot (per style).
> > > > >
> > > > > Thanking you in advance.
> > > > > Kind regards,