Is This possible to do ?

Question

Post reply

Is This possible to do ?

Leeland

SSCrazy Eights

Points: 8959
More actions
April 20, 2005 at 8:05 am

#66040

Hello All,

I am working on a view that needs to select the second most recent date. The reason being to monitor previous discharge dates as a warning sign.

here is a code example
authid     claimid   memid            dischargedate
---------------- ------------ ---------- -------------------
100542          05083000006 MB00112864 2005-01-10 00:00:00
100542          05083000006 MB00112864 2005-01-10 00:00:00
100542          05083000006 MB00112864 2005-01-10 00:00:00
100542          05083000006 MB00112864 2005-01-10 00:00:00
100542          05083000006 MB00112864 2005-01-10 00:00:00
100359          NULL             MB00112864 2005-03-12 00:00:00
100393          NULL            MB00112864 2005-03-16 00:00:00
100319          NULL            MB00112864 2005-01-09 00:00:00
100456          NULL             MB00112864 2005-02-02 00:00:00
100323          NULL            MB00112864 2005-03-09 00:00:00
each person can have multiple items under each authid...which are not shown due to space. The one I am concerned about would be the last one for the current record 100323 because it is the active record . However I also need to list the previous date that they were discharged which would be 2005-02-02 00:00:00 for evaluation purposes.
I know this probably sounds confusing...out of this record set the only record that will be showing would be 100323 cause that is the record that is the most current...however I need to somehow get the previous records discharge date 2005-02-02 00:00:00.
I thought about doing some sort of max(case statement) but wasnt' sure...if anyone has a thought as to how I should approach this I would appreciate it.

Thank you,

Lee

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

noeld SSC Guru Points: 96590 More actions · Answer 1

Is this what you are after ?

select d1.authid, d1.memid, (select max(dischargedate)

from discharges d

where Subq.memid = d.memid

and

d.dischargedate < SubQ.MaxDate) as PrevDate

From

discharges d1

join

(select memid, Max(dischargedate) MaxDate

from discharges

group by memid) Subq

on Subq.memid = d1.memid and Subq.MaxDate = d1.dischargedate

hth

* Noel

Wayne Lawton Hall of Fame Points: 3532 More actions · Answer 2

And another variation...

CREATE TABLE TestTbl (

AuthID INT,

ClaimID VARCHAR(20),

MemID VARCHAR(20),

DischargeDate DateTime

)

GO

INSERT INTO TestTbl (AuthID, ClaimID, MemID, DischargeDate)

SELECT 100542, '05083000006', 'MB00112864', '2005-01-10 00:00:00' UNION

SELECT 100359, NULL , 'MB00112864', '2005-03-12 00:00:00' UNION

SELECT 100393, NULL , 'MB00112864', '2005-03-16 00:00:00' UNION

SELECT 100319, NULL , 'MB00112864', '2005-01-09 00:00:00' UNION

SELECT 100456, NULL , 'MB00112864', '2005-02-02 00:00:00' UNION

SELECT 100323, NULL , 'MB00112864', '2005-03-09 00:00:00'

GO

SELECT TOP 1 T1.MemID, T1.DischargeDate, T2.DischargeDate, DATEDIFF(DAY, T2.DischargeDate, T1.DischargeDate)

FROM TestTbl T1

INNER JOIN TestTbl T2 ON T2.MemID = T1.MemID AND T2.DischargeDate < T1.DischargeDate

WHERE T1.MemID = 'MB00112864' AND T1.DischargeDate = '2005-03-09 00:00:00'

ORDER BY T1.DischargeDate DESC, T2.DischargeDate DESC

Hope this helps

Wayne

Leeland SSCrazy Eights Points: 8959 More actions · Answer 3

Noel,

This puts me in the right direction...all the fields I am going to be working with come out of the same table...REFERRAL...only the memid comes from the member table.

so discharge date, memid, authid, and claimid are all in the REFERRAL TABLE. Memid is in the MEMBER table.

So I was hoping I could do a select statement and alias the REFERRAL TABLE...where I would select the max discharge date for a particular authid...then alias the table and select the max(discharge date) < the max date....

That sounds right doesn't it ?

Thanks guys for the pointers...much appreciated...I will update the thread when I get it working

Lee

noeld SSC Guru Points: 96590 More actions · Answer 4

I used the name DISCHARGES for the lack of one supplied by you.

But yeah, feel free to replace it and you will be good to go

hth

* Noel

Leeland SSCrazy Eights Points: 8959 More actions · Answer 5

Well I am sure there is a good reason that this isn't working

SELECT ISNULL(MAX(referral.dischargedate), '')

FROM

referral

HAVING

ISNULL(MAX(referral.dischargedate), '') < (SELECT ISNULL(MAX(referral_reference.dischargedate), '')

FROM referral as referral_reference

JOIN referral (NOLOCK) on

referral_reference.memid = referral.memid)

What I was hoping to do is do a sub query that would find the max discharge date for a person based on referencing the table to itself.

Then taking that date and using to select the next closest date for the previous date.

The code runs but I get nothing back..all the fields I need for this come from the same table REFERRAL.

Am I on the right track at all ?

Thanks for the help guys...I am off for the day...got a Dr.s appointment for the kid he gets the 6 months shots

Lee

noeld SSC Guru Points: 96590 More actions · Answer 6

Lee,

Let me simplify your life a little:

Create a view with Referal and Referal_Reference that looks like the example you posted. Then go from there

Cheers!

* Noel

Leeland SSCrazy Eights Points: 8959 More actions · Answer 7

so basically create a view that would pull the two fields in from the same table ?

like this

select referral.authid, referral.memid, max(referral.dischargedate) as max_date

from referral

group by referral.memid

then in my other view use the field max_date for the testing...

I will check back in tomorrow

Thanks again

Lee

Leeland SSCrazy Eights Points: 8959 More actions · Answer 8

Well I finally got it to work with the help of a co-worker...here is the code

previous_discharge_date = (SELECT MAX(ISNULL(referral_ref.dischargedate, '')) as previous_discharge_date

FROM

referral as referral_ref

WHERE

referral_ref.dischargedate <>'2078-12-31 00:00:00'

AND referral.memid = referral_ref.memid),

I was making it way more complex than it needed to be...

Thanks to everyone for the help

Leeland