January 15, 2013 at 5:33 pm
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_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/4/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.
Any help is appreciated. Hope to achieve it using sql statements. Thanks.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply