May 22, 2012 at 10:54 am
I've got this query close to what I'm looking for. In the same query, I'd like to have 3 extra columns that will put a "1" in those columns that have a charge > 0. For example: If EDCharges is 500, then I'd like to have an EDCharges2 column produce a 1 in that column. Same with NICUCharges and PICU/Peds. Seems not to work when I case an Alias column. Thanks for any help.
SELECT
P.[encounterkey],p.[VisitID],p.[DivisionCode],
SUM(CASE WHEN [departmentnumber] IN(091111,011111,181101,131111) THEN Chargeamount ELSE '' END)AS 'EDCharges',
SUM(CASE WHEN [Departmentnumber] IN(013121,093111,133111) THEN Chargeamount ELSE '' END) AS 'NICUCharges',
SUM(CASE WHEN [Departmentnumber] IN(013021,013001,093001) THEN Chargeamount ELSE '' END) AS 'PICU/Peds'
FROM [database1] as P
LEFT OUTER JOIN [database2] as C
ON P.[EncounterKey]= c.[AccountKey]
where dbo.fn_getage(PatientDOB,AdmissionDateTime)<18
and (p.DischargeDateTime > '2011-10-01' and p.DischargeDateTime<'2011-10-31')
and p.DivisionCode in ('1','2','6','E')
GROUP BY p.encounterkey,p.VisitID, p.DivisionCode
order by p.EncounterKey
May 22, 2012 at 11:13 am
I can't test this, because I don't have your tables, but it should look something like:
case when SUM(CASE WHEN [departmentnumber] IN(091111,011111,181101,131111) THEN Chargeamount ELSE '' END) > 0 THEN 1 ELSE 0 END AS EDCharges1
Try that, see if you get an error.
Also, you have it doing a sum on an empty string. That works, because SQL Server will implicitly convert the empty string to a numeric 0 for you, but it's a poor practice to force implicit conversions when you could just as easily put a 0 in there in the first place. More CPU overhead, and can create odd errors, the way you have it now.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 22, 2012 at 2:11 pm
thank you for the code..I'll give that a try. Do you have better suggestion for the sum? I'm trying to add all the department code charges, and if there were none, to leave blank. I ripped that structure off another thread in this "newbie" forum.
May 22, 2012 at 2:24 pm
boehnc (5/22/2012)
thank you for the code..I'll give that a try. Do you have better suggestion for the sum? I'm trying to add all the department code charges, and if there were none, to leave blank. I ripped that structure off another thread in this "newbie" forum.
How about this?
SUM(CASE WHEN [departmentnumber] IN(091111,011111,181101,131111) THEN Chargeamount ELSE 0 END)AS 'EDCharges',
I guess Gus already explained the empty string. 🙂
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply