June 27, 2016 at 7:58 am
Hello,
I have written 2 queries, and want to get combined result from them.
First query gives me purchases.
select
tblP.Product_Code,
tblC.DescriptionLanguage_1,
sum(tblPIID.QtyReceived1
+ tblPIID.QtyReceived2
+ tblPIID.QtyReceived3
+ tblPIID.QtyReceived4
+ tblPIID.QtyReceived5
+ tblPIID.QtyReceived6
+ tblPIID.QtyReceived7
+ tblPIID.QtyReceived8
+ tblPIID.QtyReceived9
+ tblPIID.QtyReceived10
+ tblPIID.QtyReceived11
+ tblPIID.QtyReceived12
+ tblPIID.QtyReceived13
+ tblPIID.QtyReceived14
+ tblPIID.QtyReceived15
+ tblPIID.QtyReceived16
+ tblPIID.QtyReceived17
+ tblPIID.QtyReceived18
+ tblPIID.QtyReceived19
+ tblPIID.QtyReceived20)
from tblPurchaseInvoiceItemDetail tblPIID
inner join tblPurchaseInvoiceItem tblPII on tblPIID.PIItemID = tblPII.PIItemID
inner join tblWarehouseProduct tblWP on tblPII.WarehouseProductID = tblWP.WarehouseProductID
inner join tblProduct tblP on tblWP.ProductID = tblP.Product_Id
inner join tblProductColor tblPC on tblP.Product_Id = tblPC.ProductID
inner join tblColor tblC on tblPC.ColorID = tblC.Color_Id
inner join tblProductColorStatusType tblpcst on tblpcst.ProductColorStatusTypeID = tblPII.ProductColorStatusTypeID and tblpcst.ProductColorID = tblpc.ProductColorID
WHERE Product_Code = '65004' group by tblP.Product_Code,tblC.DescriptionLanguage_1
"]
2nd Query Give me Sales Figures:
SELECT tblP.Product_Code, tblC.DescriptionLanguage_1,
-SUM (tblIID.QTYSHIPPED1 +
tblIID.QTYSHIPPED2 +
tblIID.QTYSHIPPED3 +
tblIID.QTYSHIPPED4 +
tblIID.QTYSHIPPED5 +
tblIID.QTYSHIPPED6 +
tblIID.QTYSHIPPED7 +
tblIID.QTYSHIPPED8 +
tblIID.QTYSHIPPED9 +
tblIID.QTYSHIPPED10 +
tblIID.QTYSHIPPED11 +
tblIID.QTYSHIPPED12 +
tblIID.QTYSHIPPED13 +
tblIID.QTYSHIPPED14 +
tblIID.QTYSHIPPED15 +
tblIID.QTYSHIPPED16 +
tblIID.QTYSHIPPED17 +
tblIID.QTYSHIPPED18 +
tblIID.QTYSHIPPED19 +
tblIID.QTYSHIPPED20 )
FROM tblInvoiceItemDetail tblIID
inner join tblInvoiceItem tblII on tblIID.SIItemID = tblII.SIItemID
inner join tblInvoice tblI on tblI.SIID = tblII.SIID
inner join tblWarehouseProduct tblWP on tblWP.WarehouseProductID = tblII.WarehouseProductID
inner join tblProduct tblP on tblP.Product_Id = tblWP.ProductID
inner join tblProductColor tblPC on tblP.Product_Id = tblPC.ProductID
inner join tblColor tblC on tblPC.ColorID = tblC.Color_Id
inner join tblProductColorStatusType tblpcst on tblpcst.ProductColorID = tblpc.ProductColorID
inner join tblWarehouseProductDetail tblWPD on tblWPD.WarehouseProductDetailID = tblIID.WarehouseProductDetailID
inner join tblProductColorDetail tblPCD on tblPCD.ProductcolorDetailId = tblWPD.ProductColorDetailID
and tblPCD.ProductColorID = tblPC.ProductColorID
where tblI.TxSystemTypeCode = 11 and tblP.Product_Code = '65004'
group by tblP.Product_Code, tblC.DescriptionLanguage_1"]
I want to Combine these 2 queries and get final result.
Please let me know if you need more explanation.
Any help would be appreciated.
Thanks
June 27, 2016 at 8:03 am
What's the final result you are expecting after combining the queries?
June 27, 2016 at 8:13 am
Actually it's for inventory calculation. First query give me purchases and I want to minus 2nd query which is sales. So basically what I want is Purchase(Query 1) - Sales (Query 2).
June 27, 2016 at 8:14 am
with MSCTE as (
select
tblP.Product_Code,
tblC.DescriptionLanguage_1,
sum(tblPIID.QtyReceived1
+ tblPIID.QtyReceived2
+ tblPIID.QtyReceived3
+ tblPIID.QtyReceived4
+ tblPIID.QtyReceived5
+ tblPIID.QtyReceived6
+ tblPIID.QtyReceived7
+ tblPIID.QtyReceived8
+ tblPIID.QtyReceived9
+ tblPIID.QtyReceived10
+ tblPIID.QtyReceived11
+ tblPIID.QtyReceived12
+ tblPIID.QtyReceived13
+ tblPIID.QtyReceived14
+ tblPIID.QtyReceived15
+ tblPIID.QtyReceived16
+ tblPIID.QtyReceived17
+ tblPIID.QtyReceived18
+ tblPIID.QtyReceived19
+ tblPIID.QtyReceived20) as totalQTYReceived
from tblPurchaseInvoiceItemDetail tblPIID
inner join tblPurchaseInvoiceItem tblPII on tblPIID.PIItemID = tblPII.PIItemID
inner join tblWarehouseProduct tblWP on tblPII.WarehouseProductID = tblWP.WarehouseProductID
inner join tblProduct tblP on tblWP.ProductID = tblP.Product_Id
inner join tblProductColor tblPC on tblP.Product_Id = tblPC.ProductID
inner join tblColor tblC on tblPC.ColorID = tblC.Color_Id
inner join tblProductColorStatusType tblpcst on tblpcst.ProductColorStatusTypeID = tblPII.ProductColorStatusTypeID and tblpcst.ProductColorID = tblpc.ProductColorID
WHERE Product_Code = '65004' group by tblP.Product_Code,tblC.DescriptionLanguage_1
)
SELECT tblP.Product_Code, tblC.DescriptionLanguage_1,
msCTE.totalQTYReceived,
-SUM (tblIID.QTYSHIPPED1 +
tblIID.QTYSHIPPED2 +
tblIID.QTYSHIPPED3 +
tblIID.QTYSHIPPED4 +
tblIID.QTYSHIPPED5 +
tblIID.QTYSHIPPED6 +
tblIID.QTYSHIPPED7 +
tblIID.QTYSHIPPED8 +
tblIID.QTYSHIPPED9 +
tblIID.QTYSHIPPED10 +
tblIID.QTYSHIPPED11 +
tblIID.QTYSHIPPED12 +
tblIID.QTYSHIPPED13 +
tblIID.QTYSHIPPED14 +
tblIID.QTYSHIPPED15 +
tblIID.QTYSHIPPED16 +
tblIID.QTYSHIPPED17 +
tblIID.QTYSHIPPED18 +
tblIID.QTYSHIPPED19 +
tblIID.QTYSHIPPED20 ) as TotalQtyShipped
FROM MSCTE
inner join tblInvoiceItemDetail tblIID on mscte.Produc t_code = tblP.Product_Code
and mscte.DiscriptionLanguage_1 tblC.DescriptionLanguage_1
inner join tblInvoiceItem tblII on tblIID.SIItemID = tblII.SIItemID
inner join tblInvoice tblI on tblI.SIID = tblII.SIID
inner join tblWarehouseProduct tblWP on tblWP.WarehouseProductID = tblII.WarehouseProductID
inner join tblProduct tblP on tblP.Product_Id = tblWP.ProductID
inner join tblProductColor tblPC on tblP.Product_Id = tblPC.ProductID
inner join tblColor tblC on tblPC.ColorID = tblC.Color_Id
inner join tblProductColorStatusType tblpcst on tblpcst.ProductColorID = tblpc.ProductColorID
inner join tblWarehouseProductDetail tblWPD on tblWPD.WarehouseProductDetailID = tblIID.WarehouseProductDetailID
inner join tblProductColorDetail tblPCD on tblPCD.ProductcolorDetailId = tblWPD.ProductColorDetailID
and tblPCD.ProductColorID = tblPC.ProductColorID
where tblI.TxSystemTypeCode = 11 and tblP.Product_Code = '65004'
group by tblP.Product_Code, tblC.DescriptionLanguage_1
June 27, 2016 at 8:24 am
Hi,
Thanks for reply but QtyReceived and QtyShipped are from totally different tables.
June 27, 2016 at 10:08 am
;with PurchaseCTE as (
select
tblP.Product_Code,
tblC.DescriptionLanguage_1,
sum(tblPIID.QtyReceived1
+ tblPIID.QtyReceived2
+ tblPIID.QtyReceived3
+ tblPIID.QtyReceived4
+ tblPIID.QtyReceived5
+ tblPIID.QtyReceived6
+ tblPIID.QtyReceived7
+ tblPIID.QtyReceived8
+ tblPIID.QtyReceived9
+ tblPIID.QtyReceived10
+ tblPIID.QtyReceived11
+ tblPIID.QtyReceived12
+ tblPIID.QtyReceived13
+ tblPIID.QtyReceived14
+ tblPIID.QtyReceived15
+ tblPIID.QtyReceived16
+ tblPIID.QtyReceived17
+ tblPIID.QtyReceived18
+ tblPIID.QtyReceived19
+ tblPIID.QtyReceived20) as totalQTYPurchase
from tblPurchaseInvoiceItemDetail tblPIID
inner join tblPurchaseInvoiceItem tblPII on tblPIID.PIItemID = tblPII.PIItemID
inner join tblWarehouseProduct tblWP on tblPII.WarehouseProductID = tblWP.WarehouseProductID
inner join tblProduct tblP on tblWP.ProductID = tblP.Product_Id
inner join tblProductColor tblPC on tblP.Product_Id = tblPC.ProductID
inner join tblColor tblC on tblPC.ColorID = tblC.Color_Id
inner join tblProductColorStatusType tblpcst on tblpcst.ProductColorStatusTypeID = tblPII.ProductColorStatusTypeID and tblpcst.ProductColorID = tblpc.ProductColorID
WHERE Product_Code = '65004' group by tblP.Product_Code,tblC.DescriptionLanguage_1 )
,
SaleCTE as (
SELECT tblP.Product_Code, tblC.DescriptionLanguage_1,
-SUM (tblIID.QTYSHIPPED1 +
tblIID.QTYSHIPPED2 +
tblIID.QTYSHIPPED3 +
tblIID.QTYSHIPPED4 +
tblIID.QTYSHIPPED5 +
tblIID.QTYSHIPPED6 +
tblIID.QTYSHIPPED7 +
tblIID.QTYSHIPPED8 +
tblIID.QTYSHIPPED9 +
tblIID.QTYSHIPPED10 +
tblIID.QTYSHIPPED11 +
tblIID.QTYSHIPPED12 +
tblIID.QTYSHIPPED13 +
tblIID.QTYSHIPPED14 +
tblIID.QTYSHIPPED15 +
tblIID.QTYSHIPPED16 +
tblIID.QTYSHIPPED17 +
tblIID.QTYSHIPPED18 +
tblIID.QTYSHIPPED19 +
tblIID.QTYSHIPPED20 ) as TotalQtySale
FROM tblInvoiceItemDetail tblIID
inner join tblInvoiceItem tblII on tblIID.SIItemID = tblII.SIItemID
inner join tblInvoice tblI on tblI.SIID = tblII.SIID
inner join tblWarehouseProduct tblWP on tblWP.WarehouseProductID = tblII.WarehouseProductID
inner join tblProduct tblP on tblP.Product_Id = tblWP.ProductID
inner join tblProductColor tblPC on tblP.Product_Id = tblPC.ProductID
inner join tblColor tblC on tblPC.ColorID = tblC.Color_Id
inner join tblProductColorStatusType tblpcst on tblpcst.ProductColorID = tblpc.ProductColorID
inner join tblWarehouseProductDetail tblWPD on tblWPD.WarehouseProductDetailID = tblIID.WarehouseProductDetailID
inner join tblProductColorDetail tblPCD on tblPCD.ProductcolorDetailId = tblWPD.ProductColorDetailID
and tblPCD.ProductColorID = tblPC.ProductColorID
where tblI.TxSystemTypeCode = 11 and tblP.Product_Code = '65004'
group by tblP.Product_Code, tblC.DescriptionLanguage_1
)
select PurchaseCTE.Produc t_code , SaleCTE.Product_Code,
PurchaseCTE.DiscriptionLanguage_1 , SaleCTE.DescriptionLanguage_1,
PurchaseCTE.totalQTYPurchase,
SaleCTE.TotalQtySale,
(PurchaseCTE.totalQTYPurchase - SaleCTE.TotalQtySale)
from PurchaseCTE
inner join SaleCTE on PurchaseCTE.Product_Code = PurchaseCTE.Product_code
and SaleCTE.DiscriptionLanguage_1 = PurchaseCTE.DescriptionLanguage_1
June 27, 2016 at 10:12 am
Thanks again for your reply, but I figured it out. I used CTE and Union to combine both queries. I know it's not best solution but don't have big dataset, so I think it will serve my purpose.
June 27, 2016 at 10:17 am
I think implementing the solution with CTE's is the best possible one; if not this, we need to think of using window functions if possible.
June 27, 2016 at 11:00 am
I did combine your both queries using CTE. Yes, union will also work, but CTE is much faster and efficient.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy