February 16, 2014 at 7:34 am
Hi,
What I'm trying to achieve is a result set where each distinct dbpatid has a Paid column of 1 or 0 based on whether the sum of the first select = the sum of the second select
select dbpatid,
case when
(select distinct(dbpatid), sum(LdgAmt) from epsallcontracts group by dbpatid) =
(select distinct(patientid), sum(LedgerAmount) from epstransactions group by patientid)
then 1 else 0 end as Paid
from epsallcontracts
when I run it get the errors
Msg 116, Level 16, State 1, Line 8
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
Msg 116, Level 16, State 1, Line 10
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
I've looked at the exist statement and this seems to have confused me even more although bringing a result set back that is just dbpatid's where the sum of both selects is equal is also ok.
thanks,
thanks,
February 16, 2014 at 12:14 pm
Maybe something like this...
WITH cte AS
(
SELECT DISTINCT dbpatid
FROM epsallcontracts
)
SELECT dbpatid,
CASE WHEN
(
(SELECT SUM(LdgAmt) FROM epsallcontracts ec WHERE ec.dbpatid = cte.dbpatid)
=
(SELECT SUM(LedgerAmount) FROM epstransactions et WHERE et.patientid = cte.dbpatid)
) THEN 1 ELSE 0 END AS Paid
FROM cte;
___________________________
Do Not Optimize for Exceptions!
February 16, 2014 at 5:29 pm
Another option ... though without some sample data it is difficult to test
select c.dbpatid
,case when c.LdgAmt = t.LedgerAmount then 1 else 0 end as Paid
from (select dbpatid, sum(LdgAmt) LdgAmt from epsallcontracts group by dbpatid) c
cross apply (select sum(x.LedgerAmount) LedgerAmount from epstransactions x where c.dbpatid = x.patientid) t
February 18, 2014 at 1:29 am
thanks guys
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply