Using a calculated field in a left outer join

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

Using a calculated field in a left outer join

Author

Topic

3OfHarts
Starting Member

7 Posts

Posted - 2009-08-07 : 16:54:13

I am creating a calculated field and I need to be able to use this in a left outer join to only pull those with an amount > 1. ACCOUNT_BALANCE and MEMO_BALANCE are coming from the balance_by_entity view. Any help would be greatly appreciated.

select distinct
SUM(ACCOUNT_BALANCE + MEMO_BALANCE) CALC_BALANCE,
SUM(ACCOUNT_BALANCE + MEMO_BALANCE) - SUM(RPRAUTH_AMOUNT) CALC_BAL2,
SUM(RPRAUTH_AMOUNT) RPRAUTH_AMOUNT
from tzrstsf
join AT_AR_BALANCE_BY_ENTITY on pidm_key = tzrstsf_pidm
left outer join rprauth on rprauth_pidm = tzrstsf_pidm and rprauth_term_code = '200980'
and calc_bal2 > 1
GROUP BY tzrstsf_pidm,account_balance,memo_balance,rprauth_amount
order by tzrstsf_pidm

Michelle Harts
Volunteer State Comm Coll
Gallatin, Tn

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-08-07 : 17:25:11

[code]
select tzrstsf_pidm,
SUM(ACCOUNT_BALANCE + MEMO_BALANCE) CALC_BALANCE,
SUM(ACCOUNT_BALANCE + MEMO_BALANCE) - SUM(RPRAUTH_AMOUNT) CALC_BAL2,
SUM(RPRAUTH_AMOUNT) RPRAUTH_AMOUNT
from tzrstsf
join AT_AR_BALANCE_BY_ENTITY
on pidm_key = tzrstsf_pidm
left outer join
rprauth
on rprauth_pidm = tzrstsf_pidm
and rprauth_term_code = '200980'
~~and calc_bal2 > 1~~
GROUP BY
tzrstsf_pidm,account_balance,memo_balance,rprauth_amount
HAVING SUM(ACCOUNT_BALANCE + MEMO_BALANCE) - SUM(RPRAUTH_AMOUNT) > 1
order by
tzrstsf_pidm
[/code]

3OfHarts
Starting Member

7 Posts

Posted - 2009-08-10 : 10:26:26

Doing it the way you recommend is going to only give me people who have a record in both tzrstsf and rprauth. I want to keep everyone in tzrstsf whether or not they have a rprauth record, and if they do have a record in rprauth I only want them if the balance is greater than 1. That is why I was trying to figure out how to do it within the left outer join.

Michelle Harts
Volunteer State Comm Coll
Gallatin, Tn

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-08-10 : 14:11:35

show your table schema and a few sample records

3OfHarts
Starting Member

7 Posts

Posted - 2009-08-12 : 11:33:07

I added the having statement you gave me to the left outer join and it appears to working better now.

left outer join rprauth on rprauth_pidm = tzrstsf_pidm and rprauth_term_code = '200980'
and exists (select rprauth_pidm from rprauth
where rprauth_pidm = at_ar_balance_by_entity.pidm_key
and rprauth_pidm = tzrstsf_pidm
group by rprauth_pidm
HAVING SUM(ACCOUNT_BALANCE + MEMO_BALANCE) - SUM(RPRAUTH_AMOUNT) > 0)

Thanks so much.

Michelle Harts
Volunteer State Comm Coll
Gallatin, Tn

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-08-12 : 11:40:15

Great

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources