November 19, 2010 at 8:30 am
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?
November 19, 2010 at 8:51 am
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
November 19, 2010 at 9:06 am
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)
November 19, 2010 at 9:09 am
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
November 21, 2010 at 7:05 pm
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
Change is inevitable... Change for the better is not.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply