question

Mukesh_Kumar avatar image
Mukesh_Kumar asked

Query For Select first and last row of common group

Dear friend,

I have script of our sample data. I want to write query for find first row and last row of similar events. Expected out is written in last.

CREATETABLE #Events

(

ID BIGINTIDENTITYPRIMARYKEY

,UserIDINT

,EventTimeDATETIME

,[Event]TINYINT-- 0=Login, 1=Logout

);

INSERTINTO #Events

SELECT 2,'2019-03-18 00:51:04.500', 0

UNIONALLSELECT 2,'2019-03-18 00:52:04.500', 0

UNIONALLSELECT 2,'2019-03-18 00:52:35.500', 0

UNIONALLSELECT 2,'2019-03-18 00:53:14.500', 0

UNIONALLSELECT 2,'2019-03-18 00:53:55.500', 0

UNIONALLSELECT 2,'2019-03-18 00:54:04.500', 0

UNIONALLSELECT 2,'2019-03-18 00:54:24.500', 0

UNIONALLSELECT 2,'2019-03-18 00:54:59.500', 0

UNIONALLSELECT 2,'2019-03-18 00:55:05.500', 1

UNIONALLSELECT 2,'2019-03-18 00:56:51.500', 0

UNIONALLSELECT 2,'2019-03-18 00:57:25.500', 1

UNIONALLSELECT 2,'2019-03-18 00:58:59.500', 1

UNIONALLSELECT 2,'2019-03-18 00:59:45.500', 1

UNIONALLSELECT 2,'2019-03-18 01:01:12.500', 1

UNIONALLSELECT 2,'2019-03-18 01:01:55.500', 1

UNIONALLSELECT 2,'2019-03-18 01:02:10.500', 1

UNIONALLSELECT 2,'2019-03-18 01:03:01.500', 1

UNIONALLSELECT 2,'2019-03-18 01:03:57.500', 1

UNIONALLSELECT 2,'2019-03-18 01:04:07.500', 1

UNIONALLSELECT 2,'2019-03-18 01:05:07.500', 0

UNIONALLSELECT 2,'2019-03-18 01:07:17.500', 0

UNIONALLSELECT 2,'2019-03-18 01:08:21.500', 0

UNIONALLSELECT 2,'2019-03-18 01:11:07.500', 0

UNIONALLSELECT 2,'2019-03-18 01:14:09.500', 0

UNIONALLSELECT 2,'2019-03-18 01:17:11.500', 0

UNIONALLSELECT 2,'2019-03-18 01:20:07.500', 0

UNIONALLSELECT 2,'2019-03-18 01:22:24.500', 0

UNIONALLSELECT 2,'2019-03-18 01:24:07.500', 1

UNIONALLSELECT 2,'2019-03-18 01:25:07.500', 1

UNIONALLSELECT 2,'2019-03-18 01:26:12.500', 1

UNIONALLSELECT 2,'2019-03-18 01:27:15.500', 1

UNIONALLSELECT 2,'2019-03-18 01:27:57.500', 1

UNIONALLSELECT 2,'2019-03-18 01:29:37.500', 1

UNIONALLSELECT 2,'2019-03-18 01:30:11.500', 1

SELECT*FROM #Events ORDERBY UserID, EventTime;

122019-03-18 00:51:04.5000

222019-03-18 00:52:04.5000

322019-03-18 00:52:35.5000

422019-03-18 00:53:14.5000

522019-03-18 00:53:55.5000

622019-03-18 00:54:04.5000

722019-03-18 00:54:24.5000

822019-03-18 00:54:59.5000

922019-03-18 00:55:05.5001

1022019-03-18 00:56:51.5000

1122019-03-18 00:57:25.5001

1222019-03-18 00:58:59.5001

1322019-03-18 00:59:45.5001

1422019-03-18 01:01:12.5001

1522019-03-18 01:01:55.5001

1622019-03-18 01:02:10.5001

1722019-03-18 01:03:01.5001

1822019-03-18 01:03:57.5001

1922019-03-18 01:04:07.5001

2022019-03-18 01:05:07.5000

2122019-03-18 01:07:17.5000

2222019-03-18 01:08:21.5000

2322019-03-18 01:11:07.5000

2422019-03-18 01:14:09.5000

2522019-03-18 01:17:11.5000

2622019-03-18 01:20:07.5000

2722019-03-18 01:22:24.5000

2822019-03-18 01:24:07.5001

2922019-03-18 01:25:07.5001

3022019-03-18 01:26:12.5001

3122019-03-18 01:27:15.5001

3222019-03-18 01:27:57.5001

3322019-03-18 01:29:37.5001

3422019-03-18 01:30:11.5001

Expected Output

For Zero

122019-03-18 00:51:04.5000

822019-03-18 00:54:59.5000

1022019-03-18 00:56:51.5000 *

2022019-03-18 01:05:07.5000

2722019-03-18 01:22:24.5000

922019-03-18 00:55:05.5001 *

1122019-03-18 00:57:25.5001

1922019-03-18 01:04:07.5001

2822019-03-18 01:24:07.5001

3422019-03-18 01:30:11.5001

If first rows Event value is zero pick this rows and search last rows zero value without changing event value. If previous and next rows values is different display value with * for example id = 9 & 10

Regards

Mukesh

query
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Is this the same question that you've already asked?

0 Likes 0 ·

Yes, Now I shared my sample data script and expected output

0 Likes 0 ·

0 Answers

·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.