SQL Update Statement
From: Stewart (Stewart_at_discussions.microsoft.com)
Date: 08/28/04
- Next message: Craig: "Set the selected record in a List Box"
- Previous message: William \(Bill\) Vaughn: "Re: Procedure with Table Var Returns State Closed"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 27 Aug 2004 19:01:01 -0700
Hello,
I need some assistance with this SQL Statement. I have a table called
CLCHARGE that contains a list of charges made to my patients. I am
attempting to make an update statement that I can run every night as a SQL
job that write's off an amount that the patient owe's meeting certain
criteria. The criteria is all contained in a single record. I need to query
that record, retrieve a value from it, and insert that value into a column
named "writeoff" in the same record.
Let me attempt to explain the data in the table.
Table Name - CLCHARGE
CHGID - Int(Unique Charge Identifier) - Primary Key
ACCOUNT - Int(Account Number)
chgamount - decimal(Amount charged to a patient)
chgallowed - decimal(maximum amount that we can charge a patient)
Billed - varchar(value that specifies what the bill status is)
status - varchar(if this charge is active)
writeoff - decimal(Amount written off)
XACDate - Date of Transaction
denycode -varchar(If there was a deny code on the patient)
payins1 - decimal(First Insurance Payment)
payins2 - decimal(Second Insurance Payment)
payguar - decimal(Guaranteed Pay)
adjust - decimal(Adjustment made to the account)
UPDATE CLCHARGE
SET writeoff =
(SELECT ACCOUNT, SUM(chgamount-chgallowed) as "wo" FROM CLCHARGE
GROUP BY ACCOUNT)
FROM CLCHARGE
WHERE CHGID = (SELECT CHGID FROM CLCHARGE as c2 WHERE BILLED = 'b' and
status = 'y' and chgamount <> chgallowed and writeoff=0 and XACDATE >
'2003-12-31' and denycode ='' and status = 'y' and chgallowed <> 0
GROUP BY CHGID
HAVING SUM(chgamount-(payins1 + payins2 + payguar + adjust + writeoff))
> 0)
Now let me explain the SQL Statment:
There are two SELECT statements included in this UPDATE Statement.
The first determines what the writeoff amount is. This function works
properly, sort of. If I execute this statement without including the
"ACCOUNT" and "GROUP BY ACCOUNT" text, then it simply subtracts the amounts
that are between the two fields and adds the difference up for a huge total.
When I leave ACCOUNT and GROUP BY ACCOUNT, it works fine, except for the fact
that I have an extra field that I do not want. (ACCOUNT)
The second statement selects each charge ID (CHGID) that meets that specific
criteria. Again, this part works fine independantly. When I combine them
into this update statement, I get an error that states:
"Only one expression can be specified in the select list when the subquery
is not introduced with EXISTS."
How can I get my statement to update each records "writeoff" amount with the
sum value specified in the first SELECT statement when the record meets the
Second statement's criteria?
Thanks,
Stewart
- Next message: Craig: "Set the selected record in a List Box"
- Previous message: William \(Bill\) Vaughn: "Re: Procedure with Table Var Returns State Closed"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
|
