This is rare... after 8 hrs no one replied, maybe i am too demanding or the scenario is not clear enough? Therefore i made up another example:
This is what i have:
PASS_MENTER_DT CONSECUTIVE_D
Boo 5/1/2012 11:55:00 PM1
Boo 5/2/2012 11:30:00 PM2
Boo 5/4/2012 10:30:00 AM1
Boo 5/4/2012 01:30:00 PM1
LIAW 4/30/2012 11:48:52 PM 1
LIAW 5/1/2012 00:11:07 AM2
LIAW 5/1/2012 11:59:07 AM2
LIAW 5/1/2012 4:42:02 AM2
LIAW 5/2/2012 1:10:09 AM3
LIAW 5/2/2012 1:43:06 AM3
LIAW 5/4/2012 2:17:47 AM1
LIAW 5/5/2012 3:00:00 AM1
This is what i want:
PASS_MENTER_DT CONSECUTIVE_D FirstEntry
Boo 5/1/2012 11:55:00 PM1 5/1/2012 11:55:00 PM
Boo 5/2/2012 11:30:00 PM2 5/1/2012 11:55:00 PM
Boo 5/4/2012 10:30:00 AM1 5/4/2012 10:30:00 AM
Boo 5/4/2012 01:30:00 PM1 5/4/2012 10:30:00 AM
LIAW 4/30/2012 11:48:52 PM 1 4/30/2012 11:48:52 PM
LIAW 5/1/2012 00:11:07 AM2 4/30/2012 11:48:52 PM
LIAW 5/1/2012 11:59:07 AM2 4/30/2012 11:48:52 PM
LIAW 5/1/2012 4:42:02 AM2 4/30/2012 11:48:52 PM
LIAW 5/2/2012 1:10:09 AM3 4/30/2012 11:48:52 PM
LIAW 5/2/2012 1:43:06 AM3 4/30/2012 11:48:52 PM
LIAW 5/4/2012 2:17:47 AM1 5/4/2012 2:17:47 AM
LIAW 5/5/2012 3:00:00 AM1 5/4/2012 2:17:47 AM
An logic i have in mind is to:
((take the entry_dt minus away the number of consecutive days) + 1)to derive the date of the firstEntry, then from there i will derive the time by getting the earliest time with that same date. However this logic is flawed, as wont work for numerous same consecutive days.
Anybody know what i am talking about?