How can I combine 3 different Sqls in one group but in different columns (Sql Server Stored Procedure)
I have 3 different queries in a stored procedure in Sql server. I need to combine the grouping of results by "Date, Command, Account" and having columns:
(Query1.NumberUnits + Query2.NumberUnits) AS TotalUnits,
(Query2.NumberCartons) AS TotalCartons,
(Query3.TotalPallets) AS TotalPallets
My Sqls are a little tricky so I couldn't post here so as not to make it too complicated, but I need a command like Merge or Union all or even temporary tables , but I don't know how to use in this case.
Request 1
╔═══════════╦════════╦═══════════╦════════════════╦═════════════╗
║ Date ║ TeamId ║ AccountId ║ TransactionQty ║ NumberUnits ║
╠═══════════╬════════╬═══════════╬════════════════╬═════════════╣
║ 8/12/2014 ║ 4 ║ 1989 ║ 4 ║ 4 ║
╚═══════════╩════════╩═══════════╩════════════════╩═════════════╝
Request 2
╔═══════════╦════════╦═══════════╦════════════════╦═══════════════╦═════════════╗
║ Date ║ TeamId ║ AccountId ║ TransactionQty ║ NumberCartons ║ NumberUnits ║
╠═══════════╬════════╬═══════════╬════════════════╬═══════════════╬═════════════╣
║ 8/12/2014 ║ 4 ║ 1989 ║ 6 ║ 6 ║ 1 ║
╚═══════════╩════════╩═══════════╩════════════════╩═══════════════╩═════════════╝
Request 3
╔═══════════╦════════╦═══════════╦══════════════╗
║ Date ║ TeamId ║ AccountId ║ TotalPallets ║
╠═══════════╬════════╬═══════════╬══════════════╣
║ 8/12/2014 ║ 5 ║ 2000 ║ 2 ║
║ 9/12/2014 ║ 4 ║ 1989 ║ 1 ║
╚═══════════╩════════╩═══════════╩══════════════╝
Query result
╔═══════════╦════════╦═══════════╦════════════╦══════════════╦══════════════╗
║ Date ║ TeamId ║ AccountId ║ TotalUnits ║ TotalCartons ║ TotalPallets ║
╠═══════════╬════════╬═══════════╬════════════╬══════════════╬══════════════╣
║ 8/12/2014 ║ 4 ║ 1989 ║ 5 ║ 6 ║ 0 ║
║ 8/12/2014 ║ 5 ║ 2000 ║ 0 ║ 0 ║ 2 ║
║ 9/12/2014 ║ 4 ║ 1989 ║ 0 ║ 0 ║ 1 ║
╚═══════════╩════════╩═══════════╩════════════╩══════════════╩══════════════╝
+3
source to share
2 answers
You can do this with full outer join
or with union all
and group by
. Here's the method union all
:
with q1 as (<query1>),
q2 as (<query2>),
q3 as (<query3>)
select date, TeamId, AccountId,
sum(NumberUnits) as TotalUnits,
sum(NumberCartons) as TotalCartons,
sum(TotalPallets) as TotalPallets
from ((select date, TeamId, AccountId, NumberUnits, 0 as NumberCartons, 0 as TotalPallets
from q1
) union all
(select date, TeamId, AccountId, NumberUnits, NumberCartons, 0 as TotalPallets
from q2
) union all
(select date, TeamId, AccountId, 0 as NumberUnits, 0 as NumberCartons, TotalPallets
from q3
)
) qqq
group by date, TeamId, AccountId
order by date, TeamId, AccountId;
+3
source to share
Create table
DECLARE @q1 TABLE ([Date] DATE, TeamId INT, AccountId INT, TransactionQty INT, NumberUnits INT)
DECLARE @q2 TABLE ([Date] DATE, TeamId INT, AccountId INT, TransactionQty INT, NumberCartons INT, NumberUnits INT)
DECLARE @q3 TABLE ([Date] DATE, TeamId INT, AccountId INT, TotalPallets INT)
Sample data
INSERT INTO @q1 VALUES ('8/12/2014', 4, 1989, 4, 4)
INSERT INTO @q2 VALUES ('8/12/2014', 4, 1989, 6, 6, 1)
INSERT INTO @q3 VALUES ('8/12/2014', 5, 2000, 2)
,('9/12/2014', 4, 1989, 1)
Query
SELECT [Date], TeamId, AccountId,
ISNULL(SUM(NumberUnits), 0) AS TotalUnits,
ISNULL(SUM(NumberCartons), 0),
ISNULL(SUM(TotalPallets), 0)
FROM (
SELECT [Date], TeamId, AccountId, NULL AS NumberCartons, NumberUnits, NULL AS TotalPallets FROM @q1
UNION ALL
SELECT [Date], TeamId, AccountId, NumberCartons, NumberUnits, NULL FROM @q2
UNION ALL
SELECT [Date], TeamId, AccountId, NULL, NULL, TotalPallets FROM @q3
) AS t
GROUP BY [Date], TeamId, AccountId
+2
source to share