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='01bu0906006514q001' then ph.STOCK_UNITS_FREE else 0 end) as [S],
SUM(Case when pd.SKU_CODE='01bu0906006514q002' then ph.STOCK_UNITS_FREE else 0 end) as [M],
SUM(Case when pd.SKU_CODE='01bu0906006514q003' then ph.STOCK_UNITS_FREE else 0 end) as [L],
SUM(Case when pd.SKU_CODE='01bu0906006514q004' then ph.STOCK_UNITS_FREE else 0 end) as [XL],
SUM(Case when pd.SKU_CODE='01bu0906006514q005' 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_DETAILS 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

tosh2000Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Christopher GordonConnect With a Mentor Senior Developer AnalystCommented:
Might be a good use for a CTE,  try something like this....

with Query1 as
(
      SELECT     ph.BRANCH_ID ,SUM(Case when pd.SKU_CODE='01bu0906006514q001' then ph.STOCK_UNITS_FREE else 0 end) as [S],
      SUM(Case when pd.SKU_CODE='01bu0906006514q002' then ph.STOCK_UNITS_FREE else 0 end) as [M],
      SUM(Case when pd.SKU_CODE='01bu0906006514q003' then ph.STOCK_UNITS_FREE else 0 end) as [L],
      SUM(Case when pd.SKU_CODE='01bu0906006514q004' then ph.STOCK_UNITS_FREE else 0 end) as [XL],
      SUM(Case when pd.SKU_CODE='01bu0906006514q005' 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 as
(
      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_DETAILS 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
)

select      *
from      Query1
inner join Query2 on
      Query1.Branch_Id = Query2.Branch_Id
0
 
Christopher GordonSenior Developer AnalystCommented:
But if you decide to go this route, name the CTE's something better than Query1 and Query 2 :)
0
 
tosh2000Author Commented:
Thanks
0
All Courses

From novice to tech pro — start learning today.