December 17, 2013 at 5:35 am
Hi there,
I have this sql
select distinct epstransactions.PatientID, sum(epstransactions.LedgerAmount)
from epstransactions
inner join EPSReferralKPIs on epstransactions.PatientID = EPSReferralKPIs.dbPatID
group by epstransactions.PatientID
order by patientid
Whilst selecting distinct patientID's I also wish to create a posAmount and NegAmount from the LedgerAmount column as well
eg
ID Tot Neg Pos
56867 100 -200 300
56868 0 -300 300
56869 -100 -300 200
December 17, 2013 at 6:03 am
Try this. You shouldn't need 'distinct' where the same column is in 'group by'.
--== TEST DATA ==--
if object_id('tempdb..#epstransactions') is not null drop table #epstransactions
if object_id('tempdb..#EPSReferralKPIs') is not null drop table #EPSReferralKPIs
create table #epstransactions
(
PatientID Int,
LedgerAmount Int
)
create table #EPSReferralKPIs
(
dbPatID Int
)
insert #epstransactions values ( 56867, -200 )
insert #epstransactions values ( 56867, 300 )
insert #epstransactions values ( 56868, 300 )
insert #epstransactions values ( 56868, -300 )
insert #epstransactions values ( 56869, 200 )
insert #epstransactions values ( 56869, -300 )
insert #EPSReferralKPIs values ( 56867 )
insert #EPSReferralKPIs values ( 56868 )
insert #EPSReferralKPIs values ( 56869 )
--== CODE ==--
select T.PatientID,
Tot = sum(T.LedgerAmount),
Pos = sum(case when T.LedgerAmount > 0 then T.LedgerAmount else 0 end),
Neg = sum(case when T.LedgerAmount < 0 then T.LedgerAmount else 0 end)
from #epstransactions T
inner join #EPSReferralKPIs KPI on T.PatientID = KPI.dbPatID
group by T.PatientID
order by T.patientid
December 17, 2013 at 6:06 am
Thanks Laurie, I'd literally just arrived at that myself too!
Is it possible to mark questions as answered on this forum?
December 17, 2013 at 6:13 am
No, but people can tell. Sometimes someone posts a better answer anyway.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply