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