Extracting from linked tables into pivot

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,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 styl
e).
> Thanking you in advance.
> Kind regards,|||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 understan
d
> your problem) your summation of the quantity should be an extension of wha
t
> 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) el
se 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
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:
>|||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
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
"CyberFox" wrote:
> John,
> I've tried the sub-query option, but it didn't do what I was hoping for. L
et
> 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:
>