Now i have derive this in my target table. Lets say PASS_M is Unique. And PASS_M + Enter_DT will give u the pk. How can i base on this table, derive a new column call FirstEntry, while this column dictates what is the first entry datetime that belongs to this consecutive group.
This is what i have:
PASS_M ENTER_DT CONSECUTIVE_D
Boo 5/1/2012 11:55:00 PM 1
Boo 5/2/2012 11:30:00 PM 2
Boo 5/4/2012 10:30:00 AM 1
Boo 5/4/2012 01:30:00 PM 1
LIAW 4/30/2012 11:48:52 PM 1
LIAW 5/1/2012 00:11:07 AM 2
LIAW 5/1/2012 11:59:07 AM 2
LIAW 5/1/2012 4:42:02 AM 2
LIAW 5/2/2012 1:10:09 AM 3
LIAW 5/2/2012 1:43:06 AM 3
LIAW 5/4/2012 2:17:47 AM 1
LIAW 5/5/2012 3:00:00 AM 1
This is what i want:
PASS_M ENTER_DT CONSECUTIVE_D FirstEntry
Boo 5/1/2012 11:55:00 PM 1 5/1/2012 11:55:00 PM
Boo 5/2/2012 11:30:00 PM 2 5/1/2012 11:55:00 PM
Boo 5/4/2012 10:30:00 AM 1 5/4/2012 10:30:00 AM
Boo 5/4/2012 01:30:00 PM 1 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 AM 2 4/30/2012 11:48:52 PM
LIAW 5/1/2012 11:59:07 AM 2 4/30/2012 11:48:52 PM
LIAW 5/1/2012 4:42:02 AM 2 4/30/2012 11:48:52 PM
LIAW 5/2/2012 1:10:09 AM 3 4/30/2012 11:48:52 PM
LIAW 5/2/2012 1:43:06 AM 3 4/30/2012 11:48:52 PM
LIAW 5/4/2012 2:17:47 AM 1 5/4/2012 2:17:47 AM
LIAW 5/4/2012 3:00:00 AM 1 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.
Any help is appreciated. Hope to achieve it using sql statements. Thanks.