Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 256
  • Last Modified:

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

0
tosh2000
Asked:
tosh2000
  • 2
1 Solution
 
Christopher GordonSenior 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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now