December 9, 2015 at 10:24 am
Ok, here's the data example
Status SubStatus Date
Closed TRANSFER_SP (referral closed - transferred to other SP) 2015-12-02 13:47:00.000
Pending BENEFITS (benefits verification) 2015-12-02 14:52:00.000
Pending NEW (direct or HUB referral received) 2015-12-02 14:51:00.000
I only want the most recent status and sub status to show based on the most recent date which would be:
Pending BENEFITS (benefits verification) 2015-12-02 14:52:00.000
I thought a Max() would work, but it doesn't because it's a unique row. Anyway around that?
December 9, 2015 at 10:32 am
cory.bullard76 (12/9/2015)
Ok, here's the data exampleStatus SubStatus Date
Closed TRANSFER_SP (referral closed - transferred to other SP) 2015-12-02 13:47:00.000
Pending BENEFITS (benefits verification) 2015-12-02 14:52:00.000
Pending NEW (direct or HUB referral received) 2015-12-02 14:51:00.000
I only want the most recent status and sub status to show based on the most recent date which would be:
Pending BENEFITS (benefits verification) 2015-12-02 14:52:00.000
I thought a Max() would work, but it doesn't because it's a unique row. Anyway around that?
Your output doesn't make sense to me. Are you only wanting to get the "second" row based on the datetime value?
I think you might need to post some consumable data and expected output. You can read more about how to post that by following the first link in my signature.
_______________________________________________________________
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/
December 9, 2015 at 10:35 am
Correct, I only want the 2nd row based on the date value
So, right now I'm getting back:
Patient_Status Patient_Sub_Status Status_Date patient_id
Closed Transfer 2015-12-02 13:47:00.000 111111
Pending Benefits 2015-12-02 14:52:00.000 222222
Pending New 2015-12-02 14:51:00.000 333333
I only want to return (based on the most recent date/time)
Patient_Status Patient_Sub_Status Status_Date patient_id
Pending Benefits 2015-12-02 14:52:00.000 222222
December 9, 2015 at 10:40 am
Reading your link now on how to post the data examples better
December 9, 2015 at 10:50 am
Like this?
select top 1
Patient_Status
, Patient_Sub_Status
, Status_Date
, patient_id
from YourTable
order by Status_Date desc
_______________________________________________________________
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/
December 9, 2015 at 10:55 am
But, wouldn't that just put it order? I only want the status with the most current date to display. The goal of the report is to show the last status of the day.
December 9, 2015 at 11:10 am
cory.bullard76 (12/9/2015)
But, wouldn't that just put it order? I only want the status with the most current date to display. The goal of the report is to show the last status of the day.Sean Lange (12/9/2015)
top 1
December 9, 2015 at 11:19 am
Missed that part! Sorry. I'll give it a try
December 9, 2015 at 11:37 am
That worked Sean....thanks!
December 9, 2015 at 2:17 pm
Crap, I thought it worked (technically it did)....but, I didn't take into account multiple patient ids.....meaning, I need the latest status (based on date) for each patient ID. Right now it's giving the latest status for all IDs
December 9, 2015 at 2:24 pm
I got it! Here is what I did:
select top 1 with ties
column name,
column name
from.....
order by row_number() over (partition by Patient_ID order by VisitDate desc)
December 9, 2015 at 2:40 pm
Neat solution ?
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply