All Forums Database
Srividhya80_b 12 posts Joined 07/05
15 Mar 2012
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

Regards Srividhya

ulrich 816 posts Joined 09/09
15 Mar 2012

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

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

Srividhya80_b 12 posts Joined 07/05
16 Mar 2012

Wunderbar Danke Ulrich!

Regards Srividhya

ulrich 816 posts Joined 09/09
16 Mar 2012

Gern geschehen!

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

You must sign in to leave a comment.