Joining 2 t-sql statemnts into 1

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

SQL Server 2008 Forums

Transact-SQL (2008)

Joining 2 t-sql statemnts into 1

Author

Topic

jmesparzag
Starting Member

5 Posts

Posted - 2010-07-21 : 17:59:11

Hello, I just can't find the way to get correct results using 1 t-sql statement. I've managed to get the results I want from two different queries, but I need to get it to work in 1 statement... any ideas? Thank you!

SELECT A.Name AS Account,
ISNULL(SUM([Out].Ammount),0) AS 'Outflows'
FROM Accounts AS A
LEFT JOIN Transactions AS [Out] ON A.ID=[Out].AccountOrigin
AND [Out].CompanyOrigin = 12
AND [Out].Created >= '2009-01-01'
AND [Out].Created <= '2011-01-01'
GROUP BY A.Name

SELECT A.Name AS Account,
ISNULL(SUM([In].Ammount),0) AS 'Inflows'
FROM Accounts AS A
LEFT JOIN Transactions AS [In] ON A.ID=[In].AccountDestination
AND [In].CompanyDestination = 12
AND [In].Created >= '2009-01-01'
AND [In].Created <= '2011-01-01'
GROUP BY A.Name

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-07-21 : 18:04:08

SELECT A.Name AS Account,
ISNULL(SUM([Out].Ammount),0) AS 'Outflows'
FROM Accounts AS A
LEFT JOIN Transactions AS [Out] ON A.ID=[Out].AccountOrigin
AND [Out].CompanyOrigin = 12
AND [Out].Created >= '2009-01-01'
AND [Out].Created <= '2011-01-01'
GROUP BY A.Name
UNION ALL
SELECT A.Name AS Account,
ISNULL(SUM([In].Ammount),0) AS 'Inflows'
FROM Accounts AS A
LEFT JOIN Transactions AS [In] ON A.ID=[In].AccountDestination
AND [In].CompanyDestination = 12
AND [In].Created >= '2009-01-01'
AND [In].Created <= '2011-01-01'
GROUP BY A.Name

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

jmesparzag
Starting Member

5 Posts

Posted - 2010-07-21 : 18:09:56

Thank you Tara, but that won't work because I need 3 columns (Account, Inflows & Outflows). I've tried this structure and some of its variations, but can't get it to work:

SELECT A.Name AS Account,
ISNULL(SUM([In].Ammount),0) AS 'Inflows',
ISNULL(SUM([Out].Ammount),0) AS 'Outflows'
FROM Accounts AS A

LEFT JOIN Transactions AS [Out] ON A.ID=[Out].AccountOrigin
AND [Out].CompanyOrigin = 12
AND [Out].Created >= '2009-01-01'
AND [Out].Created <= '2011-01-01'

LEFT JOIN Transactions AS [In] ON A.ID=[In].AccountDestination
AND [In].CompanyDestination = 12
AND [In].Created >= '2009-01-01'
AND [In].Created <= '2011-01-01'

GROUP BY A.Name

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2010-07-21 : 18:52:09

Please try the query belwo and see if it works....it shud

SELECT A.Name AS Account,
ISNULL(SUM([Out].Ammount),0) AS 'Outflows'
null as inflows,
FROM Accounts AS A
LEFT JOIN Transactions AS [Out] ON A.ID=[Out].AccountOrigin
AND [Out].CompanyOrigin = 12
AND [Out].Created >= '2009-01-01'
AND [Out].Created <= '2011-01-01'
GROUP BY A.Name
UNION ALL
SELECT A.Name AS Account,
null as outflows,
ISNULL(SUM([In].Ammount),0) AS 'Inflows'
FROM Accounts AS A
LEFT JOIN Transactions AS [In] ON A.ID=[In].AccountDestination
AND [In].CompanyDestination = 12
AND [In].Created >= '2009-01-01'
AND [In].Created <= '2011-01-01'
GROUP BY A.Name

jmesparzag
Starting Member

5 Posts

Posted - 2010-07-21 : 19:30:11

Thanks akpaga, great idea! I worked out this solution, can you think of a more efficient one?

DECLARE @Flows AS TABLE
(
Account varchar(30),
Outflows decimal(11,2),
Inflows decimal(11,2)
)

INSERT INTO @Flows
(Account,
Outflows,
Inflows)
SELECT A.Name AS Account,
ISNULL(SUM([Out].Ammount),0) AS 'Outflows'
0 AS 'Inflows',
FROM Accounts AS A
LEFT JOIN Transactions AS [Out] ON A.ID=[Out].AccountOrigin
AND [Out].CompanyOrigin = 12
AND [Out].Created >= '2009-01-01'
AND [Out].Created <= '2011-01-01'
GROUP BY A.Name
UNION
SELECT A.Name AS Account,
0 as 'Outflows',
ISNULL(SUM([In].Ammount),0) AS 'Inflows'
FROM Accounts AS A
LEFT JOIN Transactions AS [In] ON A.ID=[In].AccountDestination
AND [In].CompanyDestination = 12
AND [In].Created >= '2009-01-01'
AND [In].Created <= '2011-01-01'
GROUP BY A.Name

SELECT Account,
SUM(Outflows),
SUM(Inflows)
FROM @Flows
GROUP BY Account

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2010-07-22 : 18:47:29

THIS LOOKS GOOD TO ME

parody
Posting Yak Master

111 Posts

Posted - 2010-07-23 : 09:09:16

how about something like this (not tested, bhut you get the gist)

WITH TransactionsCTE AS
(
SELECT
'Out' AS Flows
,AccountOrigin AS ID
,Amount
FROM Transactions
WHERE Created >= '2009-01-01'
AND Created <= '2011-01-01'
AND CompanyOrigin = 12
UNION ALL
SELECT
'In' AS Flows
,AccountDestination AS ID
,Amount
FROM Transactions
WHERE Created >= '2009-01-01'
AND Created <= '2011-01-01'
AND CompanyDestination = 12
)

SELECT
A.Name AS Account,
ISNULL(SUM(CASE WHEN Flows = 'Out' THEN [Trans].Amount ELSE 0 END),0) AS 'Outflows'
,ISNULL(SUM(CASE WHEN Flows = 'In' THEN [Trans].Amount ELSE 0 END),0) AS 'Outflows'
FROM Accounts AS A
LEFT JOIN TransactionsCTE AS [Trans]
ON A.ID = [Trans].ID
GROUP BY A.Name

parody
Posting Yak Master

111 Posts

Posted - 2010-07-23 : 09:11:59

Ooops, typo on 2nd sum alias. also might be neater to have the predicates in main query:

WITH TransactionsCTE AS
(
SELECT
'Out' AS Flows
,AccountOrigin AS ID
,Amount
FROM Transactions
UNION ALL
SELECT
'In' AS Flows
,AccountDestination AS ID
,Amount
FROM Transactions

)

SELECT
A.Name AS Account,
ISNULL(SUM(CASE WHEN Flows = 'Out' THEN [Trans].Amount ELSE 0 END),0) AS 'Outflows'
,ISNULL(SUM(CASE WHEN Flows = 'In' THEN [Trans].Amount ELSE 0 END),0) AS 'Inflows'
FROM Accounts AS A
LEFT JOIN TransactionsCTE AS [Trans]
ON A.ID = [Trans].ID
WHERE [Trans].Created >= '2009-01-01'
AND [Trans].Created <= '2011-01-01'
AND [Trans].CompanyDestination = 12
GROUP BY A.Name

parody
Posting Yak Master

111 Posts

Posted - 2010-07-23 : 09:12:59

or change predicate to additional join criteria depending on results requried...

jmesparzag
Starting Member

5 Posts

Posted - 2010-07-23 : 11:22:24

I agree with your post, parody. It is much better to use a with clause than a table variable, I tested it and it worked great!

jmesparzag
Starting Member

5 Posts

Posted - 2010-07-23 : 11:31:44

Actually, the tested statement was:

WITH Flows(Account,Outflows,Inflows)
AS
(
SELECT A.Name,
ISNULL(SUM([Out].Ammount),0)
NULL,
FROM Accounts AS A
LEFT JOIN Transactions AS [Out] ON A.ID=[Out].AccountOrigin
AND [Out].CompanyOrigin = 12
AND [Out].Created >= '2009-01-01'
AND [Out].Created <= '2011-01-01'
GROUP BY A.Name
UNION
SELECT A.Name,
NULL,
ISNULL(SUM([In].Ammount),0)
FROM Accounts AS A
LEFT JOIN Transactions AS [In] ON A.ID=[In].AccountDestination
AND [In].CompanyDestination = 12
AND [In].Created >= '2009-01-01'
AND [In].Created <= '2011-01-01'
GROUP BY A.Name
)

SELECT Account,
SUM(Outflows) AS Outflows,
SUM(Inflows) AS Inflows
FROM Flows
GROUP BY Account

parody
Posting Yak Master

111 Posts

Posted - 2010-07-23 : 11:45:14

cool. Could be a subquery instead of a cte if you really need it in one statement.

I would suggest the union remains on the transactions table only and join onto that which should give better performance, check the execution plan of each.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources