Get a range based on conditions?-CodePudding

Say that I have this SQL table:

 --------------------------- -------------------- -------------------- ------------- 
| TxnDate                   |  TxnType           |  ComputerName      |  User       |
 --------------------------- -------------------- -------------------- ------------- 

| 2022-08-01T00:00:53.000Z  |  PowerOn           |  PC_BWZONE_0002    | dad         |
| 2022-08-01T00:01:41.000Z  |  PowerOn           |  PC_BWZONE_0001    | mom         |
| 2022-08-01T00:01:57.000Z  |  UserSignIn        |  PC_BWZONE_0002    | dad         |
| 2022-08-01T00:02:39.000Z  |  CheckEmail        |  PC_BWZONE_0002    | dad         |
| 2022-08-01T00:04:48.000Z  |  UserSignIn        |  PC_BWZONE_0001    | mom         |
| 2022-08-01T00:06:22.000Z  |  DownloadMovie     |  PC_BWZONE_0002    | dad         |
| 2022-08-01T00:06:42.000Z  |  PowerOn           |  PC_BWZONE_0003    | system      |
| 2022-08-01T00:06:48.000Z  |  PrintRecipe       |  PC_BWZONE_0001    | mom         |
| 2022-08-01T00:06:53.000Z  |  UserSignOut       |  PC_BWZONE_0002    | dad         |
| 2022-08-01T00:07:02.000Z  |  DownloadUpdate    |  PC_BWZONE_0003    | system      |
| 2022-08-01T00:07:03.000Z  |  UserSignOut       |  PC_BWZONE_0001    | mom         |
| 2022-08-01T00:07:04.000Z  |  QueryTimeServer   |  PC_BWZONE_0001    | mom         |
| 2022-08-01T00:07:04.000Z  |  DownloadUpdate    |  PC_BWZONE_0001    | dad         |
| 2022-08-01T00:07:41.000Z  |  PowerOff          |  PC_BWZONE_0001    | system         |
| 2022-08-01T00:08:27.000Z  |  DownloadUpdate    |  PC_BWZONE_0002    | system      |
 --------------------------- -------------------- -------------------- -------------

As you can see this table has some sample data that shows an event log of sorts.

Say that I only care about what happens on these computers between UserSignIn and UserSignOut of each user who signs into a computer. I am not concerned with any tasks happening before or after a user session (for example, the system user does not log in at all, so there are no user sessions for system).

How do I take this "raw" table data and filter it to show something more like this (doesn't have to be exact, but you'll get my meaning in a second:

 --------------------------- -------------------- -------------------- ------------- 
| TxnDate                   |  TxnType           |  ComputerName      |  User       |
 --------------------------- -------------------- -------------------- ------------- 
| 2022-08-01T00:01:57.000Z  |  UserSignIn        |  PC_BWZONE_0002    | dad         |
| 2022-08-01T00:02:39.000Z  |  CheckEmail        |  PC_BWZONE_0002    | dad         |
| 2022-08-01T00:06:22.000Z  |  DownloadMovie     |  PC_BWZONE_0002    | dad         |
| 2022-08-01T00:06:53.000Z  |  UserSignOut       |  PC_BWZONE_0002    | dad         |
| 2022-08-01T00:04:48.000Z  |  UserSignIn        |  PC_BWZONE_0001    | mom         |
| 2022-08-01T00:06:48.000Z  |  PrintRecipe       |  PC_BWZONE_0001    | mom         |
| 2022-08-01T00:07:03.000Z  |  UserSignOut       |  PC_BWZONE_0001    | mom         |
 --------------------------- -------------------- -------------------- -------------

My real table has thousands of records so however I end up doing this it needs to be scalable.

Sql Version: Microsoft SQL Server 2012 (SP1) - 11.0.3513.0 (X64)

CodePudding user response：

Don't want system? Just filter it out.

SELECT * 
FROM table
WHERE [User] != 'system'
ORDER BY [User], TxnDate

CodePudding user response：

drop table if exists #SWAP_USER_GROUP
select
     [TxnDate]
    ,[TxnType]
    ,[ComputerName]
    ,[User]
    ,[Group] = ROW_NUMBER() over(partition by [TxnDate], [ComputerName], [User] order by [TxnDate]) 
into #SWAP_USER_GROUP
from [Daemon].[dbo].[Sample1]
where TxnType in ('UserSignIn', 'UserSignOut')

select distinct S1.[TxnDate], S1.[TxnType], S1.[ComputerName], S1.[User]
from [Daemon].[dbo].[Sample1] S1
    left join #SWAP_USER_GROUP S2
    on S2.[ComputerName] = S1.[ComputerName]
    and S2.[User] = S1.[User]
    and S2.[TxnDate] <= S1.[TxnDate]
where S2.[TxnDate] is not null
order by S1.[ComputerName], S1.[User], S1.[TxnDate]