troubleshooting Question

Join 2 result sets, grouped by date and by hour

Avatar of AD1080
AD1080 asked on
Microsoft SQL Server
2 Comments1 Solution53 ViewsLast Modified:
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) 
ASKER CERTIFIED SOLUTION
Sharath S
Data Engineer

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 2 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros