tosh2000
asked on
how do I join grouped query results?
I am trying to join 2 query results so the each row shows the branchID, sales total and stock level by size. These 2 query's work on the own.
query 1:
SELECT ph.BRANCH_ID ,SUM(Case when pd.SKU_CODE='01bu090600651 4q001' then ph.STOCK_UNITS_FREE else 0 end) as [S],
SUM(Case when pd.SKU_CODE='01bu090600651 4q002' then ph.STOCK_UNITS_FREE else 0 end) as [M],
SUM(Case when pd.SKU_CODE='01bu090600651 4q003' then ph.STOCK_UNITS_FREE else 0 end) as [L],
SUM(Case when pd.SKU_CODE='01bu090600651 4q004' then ph.STOCK_UNITS_FREE else 0 end) as [XL],
SUM(Case when pd.SKU_CODE='01bu090600651 4q005' then ph.STOCK_UNITS_FREE else 0 end) as [XXL]
FROM RMIS.dbo.product_detail AS pd INNER JOIN RMIS.dbo.PRODUCT_HISTORY AS ph ON ph.SKU_ID = pd.SKU_ID
WHERE (ph.WEEK_SELECTOR = 1100230) GROUP BY ph.BRANCH_ID
query2
SELECT SUM(CASE WHEN pmh.TXN_DATE_TIME > (GETDATE() - 360) THEN units ELSE 0 END) AS Last360,
SUM(CASE WHEN pmh.TXN_DATE_TIME > (GETDATE() - 90) THEN units ELSE 0 END) AS Last90,
SUM(CASE WHEN pmh.TXN_DATE_TIME > (GETDATE() - 30) THEN units ELSE 0 END) AS Last30,
SUM(CASE WHEN pmh.TXN_DATE_TIME > (GETDATE() - 7) THEN units ELSE 0 END) AS Last7, pmh.BRANCH_ID
FROM RMIS.dbo.PM_TRANS_HEADER AS pmh INNER JOIN RMIS.dbo.PM_TRANS_SKU_DETA ILS AS pmskd ON pmh.RECEIPT_ID = pmskd.RECEIPT_ID
INNER JOIN RMIS.dbo.product_detail AS pd ON pd.SKU_ID = pmskd.SKU_ID
WHERE (pmh.FLAG = 1) AND (pmh.TRANSACTION_VOID = 0) AND (pmskd.FLAG = 1) AND (pd.SKU_CODE LIKE '01bu0906006514q%')
GROUP BY pmh.BRANCH_ID
query 1:
SELECT ph.BRANCH_ID ,SUM(Case when pd.SKU_CODE='01bu090600651
SUM(Case when pd.SKU_CODE='01bu090600651
SUM(Case when pd.SKU_CODE='01bu090600651
SUM(Case when pd.SKU_CODE='01bu090600651
SUM(Case when pd.SKU_CODE='01bu090600651
FROM RMIS.dbo.product_detail AS pd INNER JOIN RMIS.dbo.PRODUCT_HISTORY AS ph ON ph.SKU_ID = pd.SKU_ID
WHERE (ph.WEEK_SELECTOR = 1100230) GROUP BY ph.BRANCH_ID
query2
SELECT SUM(CASE WHEN pmh.TXN_DATE_TIME > (GETDATE() - 360) THEN units ELSE 0 END) AS Last360,
SUM(CASE WHEN pmh.TXN_DATE_TIME > (GETDATE() - 90) THEN units ELSE 0 END) AS Last90,
SUM(CASE WHEN pmh.TXN_DATE_TIME > (GETDATE() - 30) THEN units ELSE 0 END) AS Last30,
SUM(CASE WHEN pmh.TXN_DATE_TIME > (GETDATE() - 7) THEN units ELSE 0 END) AS Last7, pmh.BRANCH_ID
FROM RMIS.dbo.PM_TRANS_HEADER AS pmh INNER JOIN RMIS.dbo.PM_TRANS_SKU_DETA
INNER JOIN RMIS.dbo.product_detail AS pd ON pd.SKU_ID = pmskd.SKU_ID
WHERE (pmh.FLAG = 1) AND (pmh.TRANSACTION_VOID = 0) AND (pmskd.FLAG = 1) AND (pd.SKU_CODE LIKE '01bu0906006514q%')
GROUP BY pmh.BRANCH_ID
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
But if you decide to go this route, name the CTE's something better than Query1 and Query 2 :)
ASKER
Thanks