April 21, 2011 at 11:42 am
Attached is the partial results. I can't display all the data from the return for many reasons. I'm interested in the last row that is highlighted. My preference would be to capture the newest, or most recent (Cgi.End_Date), date and if duplicated, grab the highest Cgi.Charge_Item_Id. Any ideas?
SELECT Pat.First_name + ' ' + Pat.Last_name AS 'Patient Name'
,Pat.Patient_id AS 'Patient ID'
,Pcs.Service_code AS 'Service Code'
,Pcs.Order_description AS 'Service Description'
,Cgi.Charge_item_id AS 'Charge Item Id'
,Cgi.Chg_code_code AS 'Charge Code'
,Cgc.Description AS 'Charge Code Description'
,Pcd.Discharge_date AS 'Service Discharge Date'
,Cgi.End_date AS 'Last Charge End Date'
,Pay.Org_id + ' - ' + Org.Name AS 'Payer Data (Code/Date)'
FROM Chg_item Cgi
JOIN Chg_codes Cgc
ON Cgi.Chg_code_code = Cgc.Code
JOIN Payer Pay
ON Cgi.Payer_id = Pay.Org_id
JOIN Organizations Org
ON Pay.Org_id = Org.Org_id
JOIN Pt_case Ptc
ON Cgi.Case_id = Ptc.Case_id
JOIN Pt_case_service Pcs
ON Ptc.Case_id = Pcs.Case_id
JOIN Pt_case_svc_discharge Pcd
ON Pcs.Sys_id = Pcd.Pt_case_service_sys_id
JOIN Patient Pat
ON Ptc.Patient_id = Pat.Patient_id
WHERE Cgi.Chg_code_code LIKE '%(DR)'
AND Cgi.Is_credit = 'F'
AND Cgi.Dont_bill_flag = 'F'
AND Cgi.Subject_to_per_diem = 'F'
AND Cgi.Kit_chg_item_sys_id IS NULL
AND Cgi.End_date + 1 >= Pcd.Discharge_date
ORDER BY
Pat.Patient_id
,Pcs.Order_description
,Cgi.Charge_item_id
April 21, 2011 at 2:33 pm
My first thought would be to change your ORDER BY clause. For example,
ORDER BY
Pat.Patient_id,
Pcs.Order_description,
Cgi.End_Date DESC,
Cgi.Charge_item_id DESC
Donalith
edited to add:
CELKO's idea of adding ROW_NUM() OVER (PARTITION BY Pat.Patient_id, Pcs.Order_Description ORDER BY Pat.Patient_id, Pcs.Order_description, Cgi.End_Date DESC, Cgi.Charge_item_id DESC) as rownum.
Make it a cte and select all of it where rownum = 1. That should give you a good start to your problem.
April 21, 2011 at 3:12 pm
EDIT: If Celko mentioned any of this, sorry, I pretty much simply skip his posts at this point.
What you'll want to do is JOIN on a subselect of SELECT Date, MAX(ID) AS maxID FROM tble to force only the most recent entry by date to be used.
In your case you'd use this as another join:
JOIN
(SELECT
ci.End_Date,
MAX( ci.Charge_Item_ID) AS MaxID
FROM
Chg_Item AS ci
GROUP BY
ci.End_Date
) AS drv
ONcgi.End_Date = drv.End_date
AND cgi.Charge_Item_ID = drv.Charge_Item_ID
This forces you to only use the most recent ID per date. There's another method using Cross Apply but you'd need to review indexing for it to get the performance.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
April 21, 2011 at 3:27 pm
Good SQL programmers would never use aliases to do display formatting. You are still writing 1950's COBOL. You even put a comma at the start of a line – that is a punch card technique!
In my selects I almost always start the list of columns with a comma. This makes commenting out a section much much easier if I need to do some trouble shooting. If I am dealing with a query that will be used in reporting serives I also use alot of aliases. This makes tieing often non specific column names to very specific report headers alot easier and makes my life far less complex when I need to look at the report a year from now. If that makes you bad at SQL at as the previous poster suggests than I am right there with you and quite proud of it. My boss loves that my bad sql habits give me an ability to make very quick changes on code.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply