Update statement with SUM - 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)

Update statement with SUM

Author

Topic

BDesigns
Starting Member

12 Posts

Posted - 2006-12-15 : 09:29:38

Hey all, i'm trying to use the SUM function within my update and keep getting errors whenever I run my powerbuilder data window. Here is my code:


UPDATE 	c
SET 	value1_39 = '07',
	amount1_39 = (SELECT SUM(bl.amount))
from	#ClaimMaster c
join	Billing_Ledger bl
on	c.clinical_transaction_no = bl.clinical_transaction_no
where	bl.coverage_plan_id = 'MEDICARE'
	and bl.type = 'DB'
	and bl.subtype = 'ND'
	and bl.denial_code in ('1','35','96')

"bl.amount" is the column that will have various values such as 400, 400 and I need to combine the two which would equal 800. Any ideas?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-12-15 : 09:39:42

Do the SUM of Billing_Ledger.amount grouped by Billing_Ledger.clinical_transaction_no in a derived table and join that to #ClaimMaster.

CODO ERGO SUM

BDesigns
Starting Member

12 Posts

Posted - 2006-12-15 : 09:40:25

I have been playing around with this, but seems redundant, basically just repeating the statement within my SELECT for the SUM on bl.amount:


UPDATE 	c
SET 	value1_39 = '07',
	amount1_39 = (SELECT SUM(bl.amount) from #ClaimMaster c
join	Billing_Ledger bl
on	c.clinical_transaction_no = bl.clinical_transaction_no 
where	bl.coverage_plan_id = 'MEDICARE'
	and bl.type = 'DB'
	and bl.subtype = 'ND'
	and bl.denial_code in ('1','35','96'))
from	#ClaimMaster c
join	Billing_Ledger bl
on	c.clinical_transaction_no = bl.clinical_transaction_no
where	bl.coverage_plan_id = 'MEDICARE'
	and bl.type = 'DB'
	and bl.subtype = 'ND'
	and bl.denial_code in ('1','35','96')

But this seems to be doing the same thing???

JoeNak
Constraint Violating Yak Guru

292 Posts

Posted - 2006-12-15 : 09:42:32

How about...

Update c
Set value1_39 = '07'
, amount1_39 = b.amount
From #ClaimMaster c
Inner Join (
Select clinical_transaction_no, sum(amount) as amount
From Billing_Ledger
Where coverage_plan_id = 'MEDICARE'
And type = 'DB'
And subtype = 'ND'
And denial_code in ('1', '35', '96')
Group By clinical_transaction_no) b On c.clinical_transaction_no = b.clinical_transaction_no

jess
Starting Member

17 Posts

Posted - 2006-12-15 : 13:14:32

Hey BDesigns,
Those table and column names are awefully familiar ... I take it we use the same software package...
Where are you located?

BDesigns
Starting Member

12 Posts

Posted - 2006-12-15 : 13:51:41

quote:
Originally posted by jess

Hey BDesigns,
Those table and column names are awfully familiar ... I take it we use the same software package...
Where are you located?

Denver, CO actually, what's the name of the software package you use??

jess
Starting Member

17 Posts

Posted - 2006-12-15 : 16:20:13

quote:
Originally posted by BDesigns

Denver, CO actually, what's the name of the software package you use??

I'm in OH and use Psych Consult. What company do you work for?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources