copying sequences based in timestamp

Database
Enthusiast

copying sequences based in timestamp

Dear All

I have a base table as follows





LOGON_SESSION Activity_Time Activity_Date Activity_Type_ID
1 5:59:06 3/12/2012 1
? 5:59:19 3/12/2012 13
? 5:59:28 3/12/2012 15
? 6:29:32 3/12/2012 17
? 6:29:32 3/12/2012 17
? 6:29:32 3/12/2012 17
? 6:30:12 3/12/2012 15
? 6:33:26 3/12/2012 21
2 6:33:53 3/12/2012 1
? 6:34:16 3/12/2012 13
? 6:34:55 3/12/2012 15
? 7:10:14 3/12/2012 17
? 7:10:50 3/12/2012 15
? 7:55:06 3/12/2012 17
? 7:55:43 3/12/2012 4
? 7:55:44 3/12/2012 21
3 8:34:18 3/12/2012 1
? 8:34:30 3/12/2012 13
? 8:34:41 3/12/2012 15
? 9:32:02 3/12/2012 4
? 9:32:03 3/12/2012 21
4 12:33:03 3/12/2012 1
? 12:40:48 3/12/2012 13
? 12:41:11 3/12/2012 15
? 13:36:44 3/12/2012 17
? 13:36:44 3/12/2012 17
? 13:36:44 3/12/2012 17
? 13:37:28 3/12/2012 15
? 13:56:17 3/12/2012 4
? 13:56:18 3/12/2012 21

Requirement is that based on the ordering of the Activity Date and Activity time I need to fill up the NULLS with the session number allocated to Activity type 1.

so my result should appear as





LOGON_SESSION Activity_Time Activity_Date Activity_Type_ID
1 5:59:06 3/12/2012 1
1 5:59:19 3/12/2012 13
1 5:59:28 3/12/2012 15
1 6:29:32 3/12/2012 17
1 6:29:32 3/12/2012 17
1 6:29:32 3/12/2012 17
1 6:30:12 3/12/2012 15
1 6:33:26 3/12/2012 21
2 6:33:53 3/12/2012 1
2 6:34:16 3/12/2012 13
2 6:34:55 3/12/2012 15
2 7:10:14 3/12/2012 17
2 7:10:50 3/12/2012 15
2 7:55:06 3/12/2012 17
2 7:55:43 3/12/2012 4
2 7:55:44 3/12/2012 21
3 8:34:18 3/12/2012 1
3 8:34:30 3/12/2012 13
3 8:34:41 3/12/2012 15
3 9:32:02 3/12/2012 4
3 9:32:03 3/12/2012 21
4 12:33:03 3/12/2012 1
4 12:40:48 3/12/2012 13
4 12:41:11 3/12/2012 15
4 13:36:44 3/12/2012 17
4 13:36:44 3/12/2012 17
4 13:36:44 3/12/2012 17
4 13:37:28 3/12/2012 15
4 13:56:17 3/12/2012 4
4 13:56:18 3/12/2012 21

It simple to get this done in Excel, however is there asmarter SQL trick to get this done.

Thanks in advance.

REgards

Srividhya

3 REPLIES
Supporter

Re: copying sequences based in timestamp

Srividhya,

below query should do

select max(logon_session) over (order by activity_date, activity_time rows between unbounded preceding and current row) as logon_session
activity_time,
activity_date,
activity_type_id
from your_table

Enthusiast

Re: copying sequences based in timestamp

Wunderbar Danke Ulrich!

Supporter

Re: copying sequences based in timestamp

Gern geschehen!