selecting 'next min() date' from a historic recordset

Question

Post reply

selecting 'next min() date' from a historic recordset

Oblio Leitch

SSCrazy

Points: 2137
More actions
November 19, 2010 at 8:30 am

#157327

Source:
KEYDATE ST RE
1012002-05-18MNEN
1012002-05-20PAEX
1012002-11-22MNEN
1012002-11-27MNMV
1012002-11-28MNMV
1012002-12-09WIMV
1012002-12-13MNMV
1012003-01-13RAMV
1012003-01-14WIMV
1012003-01-22MNMV
1012003-02-06RAMV
1012003-02-07MNMV
1012003-02-08RAMV
1012003-02-09WIMV
1012003-02-21MNMV
1012003-03-11MNMV
1012003-03-12MNMV
1012003-03-13PAMV
1012003-03-14MNMV
1012003-03-17WIMV
1012003-04-04WIMV
1012003-04-21OKMV
1012003-04-21OKNULL
1012003-08-25IRMV
1012004-08-23PAMV
1012005-02-19PAEX
1012005-03-06MNEN
1012005-03-07RAMV
1012005-03-09WIMV
1012005-03-14WIMV
1012005-04-22PAMV
1012005-04-25MNMV
1012005-04-25MNNULL
1012005-06-08IRMV
1012007-01-23PAMV
1012007-02-22PAEX
GOAL:
KEYBEGIN END
1012002-05-182002-05-20
1012002-11-222003-03-13
1012003-03-142004-08-23
1012005-03-062005-04-22
1012005-04-252007-01-23
So, my selection criteria: any 'EN' is a begin date, any 'PA' or 'EX' is an end date. So far, pretty easy. The hard part is that the first [non-end-date] record that follows a 'PA' record shall be considered a begin date. That's what selects 3/14/2003 and 4/25/2005. How do I make that selection?

Viewing 5 posts - 1 through 4 (of 4 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

Are you familiar with writing inline subqueries?

Would look something like this:

select *,

(select min([DATE])

from dbo.MyTable as MT2

where [DATE] > MyTable.[DATE]

and (ST = 'PA' or RE = 'EX') as EndDate

from dbo.MyTable

where RE = 'EN'

order by [DATE];

- 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

Oblio Leitch SSCrazy Points: 2137 More actions · Answer 2

Well, yes, I've worked at it from that angle, too. However, your query is only picking up the 'EN' dates, not the ones that are begin dates by virtue of having followed a 'PA'. That's the trouble I'm having - marking new begins. I suspect I'll have to make multiple passes at the data, marking data by processing with different criteria. (ie, select it into a temp table; mark all the begins and ends, then mark the calculated begins, then select it out with a join/match)

GSquared SSC Guru Points: 260824 More actions · Answer 3

So, what you need is a list of the dates that immediately follow a PA, whether they are EN or not?

That's easy enough. Just select all the PAs, and use the subquery method to get the next date for each one, without the second half the Where clause in the subquery.

- 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

Jeff Moden SSC Guru Points: 1004190 More actions · Answer 4

Also, take a look at the article at the first link in my signature below. It'll help you get coded answers on your next post.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)