Join Queries - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

Join Queries

Author

Topic

dewacorp.alliances

452 Posts

Posted - 2003-01-08 : 23:01:31

I had these 2 queries:

1) SELECT ACCNBRI,
SUM(BAL_AMT1) AS YTD_Actual
FROM GLF_LDG_ACCT_PBAL
WHERE LDG_NAME='03GLACT'
GROUP BY ACCNBRI

Output:
ACCNBRI; YTD_ACTUAL
Data...; Data..

2) SELECT ACCNBRI,
SUM(BAL_AMT1) AS Annual_Budget
FROM GLF_LDG_ACCT_PBAL
WHERE (LDG_NAME='03GLBUD' OR LDG_NAME='03GLMAN')
GROUP BY ACCNBRI

Output:
ACCNBRI; Annual_Budget
Data..; Data...

So ... I want to join these queries into one and if the one of the ACCNBRI has 2 values for YTD_ACTUAL and Annul_Budget becoming like this:

ACCNBRI; YTD_ACTUAL; Annual_Budget

Any help will be good. thanks

nr
SQLTeam MVY

12543 Posts

Posted - 2003-01-08 : 23:12:32

SELECT ACCNBRI ,
YTD_Actual = sum(case when LDG_NAME='03GLACT' then BAL_AMT1 else 0 end) ,
Annual_Budget = sum(case when LDG_NAME='03GLBUD' OR LDG_NAME='03GLMAN' then BAL_AMT1 else 0 end)
FROM GLF_LDG_ACCT_PBAL
GROUP BY ACCNBRI

or
select coalesce(a.ACCNBRI, b.ACCNBRI) ,
YTD_Actual = coalesce (a.YTD_Actual,0) ,
Annual_Budget = coalesce(b.Annual_Budget,0)
from
(SUM(BAL_AMT1) AS YTD_Actual
FROM GLF_LDG_ACCT_PBAL
WHERE LDG_NAME='03GLACT'
GROUP BY ACCNBRI) as a
full outer join
(SELECT ACCNBRI,
SUM(BAL_AMT1) AS Annual_Budget
FROM GLF_LDG_ACCT_PBAL
WHERE (LDG_NAME='03GLBUD' OR LDG_NAME='03GLMAN')
GROUP BY ACCNBRI) as b
on a.ACCNBRI = b.ACCNBRI

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources