Help with combining two results sets

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help with combining two results sets

Author

Topic

steve.greig
Starting Member

6 Posts

Posted - 2014-06-03 : 07:30:39

Hi everyone. My first post so be gentle...

So i have these 2 queries with the included results sets:

What commands could I use to take the TotalBlueCircle Column from the 2nd Results Set and have it included next to the TotalRugby column in the 1st results set??

Do i need to do a UNION or use Sub Queries or something else?

It must be possible...

Any help would be much appreciated.

Thanks!

Steve

steve.greig
Starting Member

6 Posts

Posted - 2014-06-03 : 07:51:22

I can paste the code in text if its more useful for anyone...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-06-03 : 08:17:25

paste the code, please.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-06-03 : 08:23:17

[code]DECLARE @Date DATE = GETDATE();

SELECT c.CustomerCode,
SUM(CASE WHEN p.ProductCode = 'RUGBY001' THEN ol.Quantity ELSE 0 END) AS TotalRugby,
SUM(CASE WHEN p.ProductCode = 'CEM004' THEN ol.Quantity ELSE 0 END) AS TotalBlueCircle
FROM dbo.OrderHeader AS oh
INNER JOIN dbo.OrderLine AS ol ON ol.OrderID = oh.OrderID
INNER JOIN dbo.Customer AS c ON c.CustomerID = oh.CustomerID
INNER JOIN dbo.Product AS p ON p.ProductID = ol.ProductID
AND p.ProductCode IN ('CEM004', 'RUGBY001')
WHERE oh.Deleted = 0
AND oh.DateTimeCreated > DATEADD(MONTH, -12, @Date)
GROUP BY c.CustomerCode
ORDER BY c.CustomerCode;[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

steve.greig
Starting Member

6 Posts

Posted - 2014-06-03 : 08:24:18

GO
SELECT DISTINCT C.CustomerCode, SUM (OL.Quantity) AS TotalRugby FROM OrderHeader AS OH
JOIN OrderLine AS OL ON OH.OrderID = OL.OrderID
JOIN Customer AS C ON OH.CustomerID = C.CustomerID
JOIN Product AS P ON OL.ProductID = P.ProductID
WHERE OH.Deleted = 0 AND P.ProductCode = 'RUGBY001' AND OH.DateTimeCreated > DATEADD(m, -12, GETDATE()) AND OH.OrderType = 1
GROUP BY C.CustomerCode
ORDER BY C.CustomerCode ASC

GO
SELECT C.CustomerCode, SUM (OL.Quantity) AS TotalBlueCircle FROM OrderHeader AS OH
JOIN OrderLine AS OL ON OH.OrderID = OL.OrderID
JOIN Customer AS C ON OH.CustomerID = C.CustomerID
JOIN Product AS P ON OL.ProductID = P.ProductID
WHERE OH.Deleted = 0 AND P.ProductCode = 'CEM004' AND OH.DateTimeCreated > DATEADD(m, -12, GETDATE()) AND OH.OrderType = 1
GROUP BY C.CustomerCode
ORDER BY C.CustomerCode ASC

I amended it slightly to simplify it...

steve.greig
Starting Member

6 Posts

Posted - 2014-06-03 : 08:29:46

That's it!!

Thank you so much. I've been trying for ages to get that!

Now all I have to do is understand what your version is doing!

Appreciate the help! I'm a bit of a newbie so this is good learning for me!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources