Join 2 result sets, grouped by date and by hour

AD1080
AD1080 used Ask the Experts™
on
Hi,  

We are comparing sales by hour for our store, and I am currently using 2 separate queries.  

I'd like to combine these results into one result set, by making the 2 queries derived tables, and joining them.   So the second result set in the attached screenshot would be additional columns added to the first result set.   I was having a hard time getting this to work correctly.  

Thanks in advance for a solution on this. Screen Shot

USE STORESQL


SELECT 
		
		--I.F22,
		--R.F1041 AS ITEM_DESCR,
		--R.F01 AS ITEM_NO,
		 DATEPART(HOUR, R.F253) as SalesHour,
		 CONVERT(DATE,R.F253) AS Sales_Day,
         SUM(R.F65) AS TotalSales,
		 SUM(R.F64) AS UnitSales
FROM 

	SAL_REG_SAV R
	JOIN SAL_HDR_SAV H ON 
		R.F1505 = H.F1505
		AND R.F1056 = H.F1056
		AND R.F1057 = H.F1057
		AND R.F1032 = H.F1032
	JOIN POS_TAB P ON R.F01 = P.F01
	JOIN OBJ_TAB I ON R.F01 = I.F01


WHERE
R.F1505 = '12-14-2018'
AND R.F1056 = '047'

GROUP BY  DATEPART(HOUR, R.F253), CONVERT(DATE,R.F253)
ORDER BY CONVERT(DATE,R.F253), DATEPART(HOUR, R.F253)


SELECT 
		
		--I.F22,
		--R.F1041 AS ITEM_DESCR,
		--R.F01 AS ITEM_NO,
		 DATEPART(HOUR, R.F253) as SalesHour,
		 CONVERT(DATE,R.F253) AS Sales_Day,
         SUM(R.F65) AS TotalSales,
		 SUM(R.F64) AS UnitSales
FROM 

	SAL_REG_SAV R
	JOIN SAL_HDR_SAV H ON 
		R.F1505 = H.F1505
		AND R.F1056 = H.F1056
		AND R.F1057 = H.F1057
		AND R.F1032 = H.F1032
	JOIN POS_TAB P ON R.F01 = P.F01
	JOIN OBJ_TAB I ON R.F01 = I.F01


WHERE
R.F1505 = '12-15-2018'
AND R.F1056 = '047'

GROUP BY  DATEPART(HOUR, R.F253), CONVERT(DATE,R.F253)
ORDER BY CONVERT(DATE,R.F253), DATEPART(HOUR, R.F253) 

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Data Engineer
Commented:
try this.
USE STORESQL

;WITH CTE1 AS (
SELECT 
		
		--I.F22,
		--R.F1041 AS ITEM_DESCR,
		--R.F01 AS ITEM_NO,
		 DATEPART(HOUR, R.F253) as SalesHour,
		 CONVERT(DATE,R.F253) AS Sales_Day,
         SUM(R.F65) AS TotalSales,
		 SUM(R.F64) AS UnitSales
FROM 

	SAL_REG_SAV R
	JOIN SAL_HDR_SAV H ON 
		R.F1505 = H.F1505
		AND R.F1056 = H.F1056
		AND R.F1057 = H.F1057
		AND R.F1032 = H.F1032
	JOIN POS_TAB P ON R.F01 = P.F01
	JOIN OBJ_TAB I ON R.F01 = I.F01


WHERE
R.F1505 = '12-14-2018'
AND R.F1056 = '047'

GROUP BY  DATEPART(HOUR, R.F253), CONVERT(DATE,R.F253)
),
CTE2 AS (
SELECT 
		
		--I.F22,
		--R.F1041 AS ITEM_DESCR,
		--R.F01 AS ITEM_NO,
		 DATEPART(HOUR, R.F253) as SalesHour,
		 CONVERT(DATE,R.F253) AS Sales_Day,
         SUM(R.F65) AS TotalSales,
		 SUM(R.F64) AS UnitSales
FROM 

	SAL_REG_SAV R
	JOIN SAL_HDR_SAV H ON 
		R.F1505 = H.F1505
		AND R.F1056 = H.F1056
		AND R.F1057 = H.F1057
		AND R.F1032 = H.F1032
	JOIN POS_TAB P ON R.F01 = P.F01
	JOIN OBJ_TAB I ON R.F01 = I.F01


WHERE
R.F1505 = '12-15-2018'
AND R.F1056 = '047'

GROUP BY  DATEPART(HOUR, R.F253), CONVERT(DATE,R.F253)
)
SELECT c1.*, c2.Sales_Day, c2.TotalSales,c2.UnitSales 
  FROM CTE1 c1
  JOIN CTE2 c2 ON c1.SalesHour = c2.SalesHour
ORDER BY SalesHour

Open in new window

Author

Commented:
Thanks that worked.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial