August 24, 2021 at 3:15 pm
I need to combine these two query's to get shipped and received for each year,month in one query. Some months we may not have orders but do have receipts others no receipts but orders for some items.
SELECT Datepart(mm, omporderdate) AS mth ,
Datepart(yyyy, omporderdate) AS yr ,
omlpartid,
Sum(omlquantityshipped) AS shipped
FROM salesorders
LEFT JOIN salesorderlines
ON omlsalesorderid = ompsalesorderid
where omlPartID in
(
SELECT imppartid
FROM parts
WHERE imppartclassid IN ( 'FGI' ,
'FGP' )
AND imppartid NOT LIKE'%DC%' )
and omporderdate >='01-01-2018'
GROUP BY Datepart(yyyy, omporderdate) ,
Datepart(mm, omporderdate) ,
omlpartid
ORDER BY Datepart(yyyy, omporderdate),
Datepart(mm, omporderdate),
omlpartid
/**************************************************/
SELECT Datepart(mm, rmlcreateddate) AS Mth ,
Datepart(yyyy, rmlcreateddate) AS YR ,
rmlpartid ,
Sum(rmlpurchasequantityreceived) AS received
FROM receipts
LEFT OUTER JOIN receiptlines
ON rmlreceiptid = rmpreceiptid
WHERE rmlpartid IN
(
SELECT imppartid
FROM parts
WHERE imppartclassid IN ( 'FGI' ,
'FGP' )
AND imppartid NOT LIKE'%DC%' )
and rmlCreatedDate >='01-01-2018'
GROUP BY Datepart(yyyy, rmlcreateddate) ,
Datepart(mm, rmlcreateddate) ,
rmlpartid
August 24, 2021 at 3:21 pm
SELECT ...
FROM query1
UNION ALL
SELECT...
FROM query2
August 24, 2021 at 3:49 pm
I think FULL OUTER JOIN is what you need here:
SELECT
COALESCE(sales.mth, receipts.mth) AS mth,
COALESCE(sales.yr, receipts.yr) AS yr,
COALESCE(sales.omlpartid, receipts.rmlpartid) AS partid,
sales.shipped AS shipped,
receipts.received AS received
FROM (
SELECT Datepart(mm, omporderdate) AS mth ,
Datepart(yyyy, omporderdate) AS yr ,
omlpartid,
Sum(omlquantityshipped) AS shipped
FROM dbo.salesorders
LEFT JOIN dbo.salesorderlines
ON omlsalesorderid = ompsalesorderid
where omlPartID in
(
SELECT imppartid
FROM dbo.parts
WHERE imppartclassid IN ( 'FGI' ,
'FGP' )
AND imppartid NOT LIKE'%DC%' )
and omporderdate >='01-01-2018'
GROUP BY Datepart(yyyy, omporderdate) ,
Datepart(mm, omporderdate) ,
omlpartid
) AS sales
FULL OUTER JOIN (
SELECT Datepart(mm, rmlcreateddate) AS Mth ,
Datepart(yyyy, rmlcreateddate) AS YR ,
rmlpartid ,
Sum(rmlpurchasequantityreceived) AS received
FROM dbo.receipts
LEFT OUTER JOIN dbo.receiptlines
ON rmlreceiptid = rmpreceiptid
WHERE rmlpartid IN
(
SELECT imppartid
FROM dbo.parts
WHERE imppartclassid IN ( 'FGI' ,
'FGP' )
AND imppartid NOT LIKE'%DC%' )
and rmlCreatedDate >='01-01-2018'
GROUP BY Datepart(yyyy, rmlcreateddate) ,
Datepart(mm, rmlcreateddate) ,
rmlpartid
) AS receipts ON
sales.mth = receipts.mth AND
sales.yr = receipts.yr AND
sales.omlpartid = receipts.rmlpartid
ORDER BY COALESCE(sales.mth, receipts.mth),
COALESCE(sales.yr, receipts.yr),
COALESCE(sales.omlpartid, receipts.rmlpartid)
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 24, 2021 at 5:15 pm
Perfect, It works great Thanks
August 24, 2021 at 6:31 pm
You're welcome. Thanks for the feedback.
If you would mark it as the answer, it could help people looking at this later, as they will then know the q has been resolved/answered.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply