Attribute values to "pervious" and "next" rows for aggregation (and allow exceptions) in query + view?

Question

I have a table in a database built on SQL Server 2014 which contains rows for "events", some events are attributed to User IDs while others events are not attributed to any user ID, values are '-'.

Table1

RecordID [char(18)]
UserID [nvarchar(50)]
Event [nvarchar(20)]
EventType [char(4)]
EventDuration [int]
EventBeginTimestamp [DateTime2(3)]

I wish to sum durations for all events and attribute duration time (seconds) to users

Durations where there is no user attributed to the first user (order by RecordID, EventTimestamp Desc)
Durations where there is no user following the "last" user, attribute to the last user.
I also wish to make exceptions based on EventType, for example exclude EventType '3005'

Questions

How can I do this in a query?
How can I do this in a view?

I know that sounds very convoluted, so how about an example:

edited to include additional demo data and explanation for clarification purposes

--Setup demo data
Declare @Table1 table
    (RecordID int, UserID varchar(5), Event varchar(20), EventType char(4), EventDuration int, EventBeginTimestamp DateTime2(3))
;

INSERT INTO @Table1
    (RecordID, UserID, Event, EventType, EventDuration, EventBeginTimestamp)
VALUES
    (123456, '-', 'EventA', '3001', '56', '2018-06-20 19:01:29.000'),
    (123456, '-', 'EventB', '3002', '50', '2018-06-20 19:02:25.000'),
    (123456, 'Bob', 'EventC', '3003', '90', '2018-06-20 19:03:15.000'),
    (123456, '-', 'EventD', '3002', '27', '2018-06-20 19:04:45.000'),
    (123456, 'Sam', 'EventE', '3003', '48', '2018-06-20 19:05:12.000'),
    (123456, '-', 'EventF', '3002', '60', '2018-06-20 19:06:00.000'),
    (123456, 'Sam', 'EventG', '3005', '114', '2018-06-20 19:07:00.000'),
    (123456, '-', 'EventH', '3002', '20', '2018-06-20 19:08:54.000'),
    (123456, 'Joe', 'EventI', '3003', '62', '2018-06-20 19:09:14.000'),
    (123456, '-', 'EventJ', '3006', '60', '2018-06-20 19:10:16.000'),
    (789111, '-', 'EventAA', '3001', '108', '2018-06-21 12:01:12.000'),
    (789111, '-', 'EventBB', '3002', '60', '2018-06-21 12:03:00.000'),
    (789111, 'Jane', 'EventCC', '3003', '114', '2018-06-21 12:04:00.000'),
    (789111, '-', 'EventDD', '3002', '80', '2018-06-21 12:05:54.000'),
    (789111, 'Jane', 'EventEE', '3005', '122', '2018-06-21 12:07:14.000'),
    (789111, 'Mike', 'EventFF', '3003', '74', '2018-06-21 12:09:16.000'),
    (789111, '-', 'EventGG', '3002', '106', '2018-06-21 12:10:30.000'),
    (789111, '-', 'EventHH', '3006', '60', '2018-06-21 12:12:16.000')

;

| RecordID  | UserID | Event      | EventType | EventDuration | EventBeginTimestamp     |
|-----------|--------|------------|-----------|---------------|-------------------------|
| 123456    |  -     | EventA     | 3001      | 56            | 2018-06-20 19:01:29.000 |
| 123456    |  -     | EventB     | 3002      | 50            | 2018-06-20 19:02:25.000 |
| 123456    | Bob    | EventC     | 3003      | 90            | 2018-06-20 19:03:15.000 |
| 123456    |  -     | EventD     | 3002      | 27            | 2018-06-20 19:04:45.000 |
| 123456    | Sam    | EventE     | 3003      | 48            | 2018-06-20 19:05:12.000 |
| 123456    |  -     | EventF     | 3002      | 60            | 2018-06-20 19:06:00.000 |
| 123456    | Sam    | EventG     | 3005      | 114           | 2018-06-20 19:07:00.000 |
| 123456    |  -     | EventH     | 3002      | 20            | 2018-06-20 19:08:54.000 |
| 123456    | Joe    | EventI     | 3003      | 62            | 2018-06-20 19:09:14.000 |
| 123456    |  -     | EventJ     | 3006      | 60            | 2018-06-20 19:10:16.000 |
| 789111    |  -     | EventAA    | 3001      | 108           | 2018-06-21 12:01:12.000 |
| 789111    |  -     | EventBB    | 3002      | 60            | 2018-06-21 12:03:00.000 |
| 789111    | Jane   | EventCC    | 3003      | 114           | 2018-06-21 12:04:00.000 |
| 789111    |  -     | EventDD    | 3002      | 80            | 2018-06-21 12:05:54.000 |
| 789111    | Jane   | EventEE    | 3005      | 122           | 2018-06-21 12:07:14.000 |
| 789111    | Mike   | EventFF    | 3003      | 74            | 2018-06-21 12:09:16.000 |
| 789111    |  -     | EventGG    | 3002      | 106           | 2018-06-21 12:10:30.000 |
| 789111    |  -     | EventHH    | 3006      | 60            | 2018-06-21 12:12:16.000 |

| RecordID  | UserID | Event      | EventType | EventDuration | EventBeginTimestamp     |
|-----------|--------|------------|-----------|---------------|-------------------------|
| 123456    | Bob    | EventA     | 3001      | 56            | 2018-06-20 19:01:29.000 |
| 123456    | Bob    | EventB     | 3002      | 50            | 2018-06-20 19:02:25.000 |
| 123456    | Bob    | EventC     | 3003      | 90            | 2018-06-20 19:03:15.000 |
| 123456    | Sam    | EventD     | 3002      | 27            | 2018-06-20 19:04:45.000 |
| 123456    | Sam    | EventE     | 3003      | 48            | 2018-06-20 19:05:12.000 |
| 123456    | Joe    | EventF     | 3002      | 60            | 2018-06-20 19:06:00.000 |
| 123456    | Sam    | EventG     | 3005      | 114           | 2018-06-20 19:07:00.000 |
| 123456    | Joe    | EventH     | 3002      | 20            | 2018-06-20 19:08:54.000 |
| 123456    | Joe    | EventI     | 3003      | 62            | 2018-06-20 19:09:14.000 |
| 123456    | Joe    | EventJ     | 3006      | 60            | 2018-06-20 19:10:16.000 |
| 789111    | Jane   | EventAA    | 3001      | 108           | 2018-06-21 12:01:12.000 |
| 789111    | Jane   | EventBB    | 3002      | 60            | 2018-06-21 12:03:00.000 |
| 789111    | Jane   | EventCC    | 3003      | 114           | 2018-06-21 12:04:00.000 |
| 789111    | Mike   | EventDD    | 3002      | 80            | 2018-06-21 12:05:54.000 |
| 789111    | Jane   | EventEE    | 3005      | 122           | 2018-06-21 12:07:14.000 |
| 789111    | Mike   | EventFF    | 3003      | 74            | 2018-06-21 12:09:16.000 |
| 789111    | Mike   | EventGG    | 3002      | 106           | 2018-06-21 12:10:30.000 |
| 789111    | Mike   | EventHH    | 3006      | 60            | 2018-06-21 12:12:16.000 |

| RecordID  | UserID | EventType | SumDuration |
|-----------|--------|-----------|-------------|
| 123456    |  Bob   | 3001      | 56          |
| 123456    |  Bob   | 3002      | 50          | 
| 123456    |  Bob   | 3003      | 90          | 
| 123456    |  Sam   | 3002      | 27          | 
| 123456    |  Sam   | 3003      | 48          | 
| 123456    |  Sam   | 3005      | 114         | 
| 123456    |  Joe   | 3002      | 80          | 
| 123456    |  Joe   | 3003      | 62          | 
| 123456    |  Joe   | 3006      | 60          | 
| 789111    |  Jane  | 3001      | 108         | 
| 789111    |  Jane  | 3002      | 60          | 
| 789111    |  Jane  | 3003      | 114         | 
| 789111    |  Jane  | 3005      | 112         | 
| 789111    |  Mike  | 3002      | 186         | 
| 789111    |  Mike  | 3003      | 74          |
| 789111    |  Mike  | 3006      | 60          |

| RecordID  | UserID | SumDuration |
|-----------|--------|-------------|
| 123456    |  Bob   | 196         |
| 123456    |  Sam   | 189         | 
| 123456    |  Joe   | 202         | 
| 789111    |  Jane  | 394         | 
| 789111    |  Mike  | 320         |

Although the below example is fictional, it illustrates that time allocate for EventType '3005' exception ' Generate Invoice' is not considered during allocation for null user ID associated with Lift time for the next user ID

| RecordID  | UserID | Event      | EventType | EventTypeDiscription | EventDuration | EventBeginTimestamp     |
|-----------|--------|------------|-----------|----------------------|---------------|-------------------------|
| 123456    |  -     | EventA     | 3001      | Enters Garage        | 56            | 2018-06-20 19:01:29.000 |
| 123456    |  -     | EventB     | 3002      | Lift Time            | 50            | 2018-06-20 19:02:25.000 |
| 123456    | Bob    | EventC     | 3003      | Performs Work        | 90            | 2018-06-20 19:03:15.000 |
| 123456    |  -     | EventD     | 3002      | Lift Time            | 27            | 2018-06-20 19:04:45.000 |
| 123456    | Sam    | EventE     | 3003      | Performs Work        | 48            | 2018-06-20 19:05:12.000 |
| 123456    |  -     | EventF     | 3002      | Lift Time            | 60            | 2018-06-20 19:06:00.000 |
| 123456    | Sam    | EventG     | 3005      | Generate Invoice     | 114           | 2018-06-20 19:07:00.000 |
| 123456    |  -     | EventH     | 3002      | Lift Time            | 20            | 2018-06-20 19:08:54.000 |
| 123456    | Joe    | EventI     | 3003      | Performs Work        | 62            | 2018-06-20 19:09:14.000 |
| 123456    |  -     | EventJ     | 3006      | Exits Garage         | 60            | 2018-06-20 19:10:16.000 |
| 789111    |  -     | EventAA    | 3001      | Enters Garage        | 108           | 2018-06-21 12:01:12.000 |
| 789111    |  -     | EventBB    | 3002      | Lift Time            | 60            | 2018-06-21 12:03:00.000 |
| 789111    | Jane   | EventCC    | 3003      | Performs Work        | 114           | 2018-06-21 12:04:00.000 |
| 789111    |  -     | EventDD    | 3002      | Lift Time            | 80            | 2018-06-21 12:05:54.000 |
| 789111    | Jane   | EventEE    | 3005      | Generate Invoice     | 122           | 2018-06-21 12:07:14.000 |
| 789111    | Mike   | EventFF    | 3003      | Performs Work        | 74            | 2018-06-21 12:09:16.000 |
| 789111    |  -     | EventGG    | 3002      | Lift Time            | 106           | 2018-06-21 12:10:30.000 |
| 789111    |  -     | EventHH    | 3006      | Exits Garage         | 60            | 2018-06-21 12:12:16.000 |

Can you re-check your examples and verify there are no errrors (it seems to me that there might be some: 1. Does RecordID always have the same value ? 2. You mentioned you would like to exclude 3005, but you retained it in the example ? 3. Can you clarify based on what rule did the row with Event F/ duration 60 get assigned to Joe (and not Sam?) ? — Błażej Ciesielski, Jun 27, 2018 at 5:36
I added additional demo data and another fictional table to illustrate where this might occur in practice. 1. No however all events belong to one record, think of it as a parsing of events for a work order perhaps. 2. I want to exclude it in the logic allocating values to null user ID 3. the user in EventType 3005 should not be considered when assigning the value to the previous record. — AlwaysLearning, Jun 27, 2018 at 18:16

Milan · Accepted Answer · 2018-07-03 10:13:44Z

Here are scripts to achieve this, by using Window Functions solution.
I have included:

one data preparation script (for you to check the answers)
two scripts as answers for the two questions

Answers:

1. How can I do this in a query?

;WITH cte AS (
    SELECT  RecordID, UserID, Event, EventType, EventDuration, EventBeginTimestamp
        , MAX(RelevantID) OVER( ORDER BY RecordID, EventBeginTimestamp
              ROWS UNBOUNDED PRECEDING ) AS Grp

    FROM    dbo.DemoData AS t
    CROSS APPLY ( VALUES( CASE WHEN UserID <> '-' THEN EventBeginTimestamp END ) ) AS A(relevantid)
    WHERE   t.EventType <> '3005'
)
SELECT RecordID, UserID, Event, EventType, EventDuration, EventBeginTimestamp
    , UserID_Calculated =
        MAX(UserID) OVER(
            PARTITION BY grp
            ORDER BY EventBeginTimestamp
            ROWS UNBOUNDED PRECEDING )
FROM cte;

2. How can I do this in a view?

CREATE VIEW dbo.vwDemo AS
(
    SELECT RecordID, UserID, Event, EventType, EventDuration, EventBeginTimestamp
        , UserID_Calculated =
            MAX(UserID) OVER(
                PARTITION BY grp
                ORDER BY EventBeginTimestamp
                ROWS UNBOUNDED PRECEDING )  
    FROM (
        SELECT  RecordID, UserID, Event, EventType, EventDuration, EventBeginTimestamp
            , MAX(RelevantID) OVER( ORDER BY RecordID, EventBeginTimestamp
                ROWS UNBOUNDED PRECEDING ) AS Grp

        FROM    dbo.DemoData AS t
        CROSS APPLY ( VALUES( CASE WHEN UserID <> '-' THEN EventBeginTimestamp END ) ) AS A(relevantid)
        ) AS cte
);
GO

SELECT  *
FROM    dbo.vwDemo AS v
WHERE   v.EventType <> '3005' -- Exclude the unwanted event

For more information on the used solution, could check this post: "The Last non NULL Puzzle", by Itzik Ben-Gan, Here.
The post explains two excellent solutions for a similar task.

Data Preparation Script:

Create demo table and insert data:
(I converted a Temp Variable to a Table, so that it can be used in a View later on)

--Create demo table
CREATE TABLE dbo.DemoData
    (RecordID int, UserID varchar(5), Event varchar(20), EventType char(4), 
EventDuration int, EventBeginTimestamp DateTime2(3))
;

--Insert demo data
INSERT INTO dbo.DemoData
    (RecordID, UserID, Event, EventType, EventDuration, EventBeginTimestamp)
VALUES
    (123456, '-', 'EventA', '3001', '56', '2018-06-20 19:01:29.000'),
    (123456, '-', 'EventB', '3002', '50', '2018-06-20 19:02:25.000'),
    (123456, 'Bob', 'EventC', '3003', '90', '2018-06-20 19:03:15.000'),
    (123456, '-', 'EventD', '3002', '27', '2018-06-20 19:04:45.000'),
    (123456, 'Sam', 'EventE', '3003', '48', '2018-06-20 19:05:12.000'),
    (123456, '-', 'EventF', '3002', '60', '2018-06-20 19:06:00.000'),
    (123456, 'Sam', 'EventG', '3005', '114', '2018-06-20 19:07:00.000'),
    (123456, '-', 'EventH', '3002', '20', '2018-06-20 19:08:54.000'),
    (123456, 'Joe', 'EventI', '3003', '62', '2018-06-20 19:09:14.000'),
    (123456, '-', 'EventJ', '3006', '60', '2018-06-20 19:10:16.000'),
    (789111, '-', 'EventAA', '3001', '108', '2018-06-21 12:01:12.000'),
    (789111, '-', 'EventBB', '3002', '60', '2018-06-21 12:03:00.000'),
    (789111, 'Jane', 'EventCC', '3003', '114', '2018-06-21 12:04:00.000'),
    (789111, '-', 'EventDD', '3002', '80', '2018-06-21 12:05:54.000'),
    (789111, 'Jane', 'EventEE', '3005', '122', '2018-06-21 12:07:14.000'),
    (789111, 'Mike', 'EventFF', '3003', '74', '2018-06-21 12:09:16.000'),
    (789111, '-', 'EventGG', '3002', '106', '2018-06-21 12:10:30.000'),
    (789111, '-', 'EventHH', '3006', '60', '2018-06-21 12:12:16.000');

Stack Exchange Network

Attribute values to "pervious" and "next" rows for aggregation (and allow exceptions) in query + view?

1 Answer 1

Your Answer

Not the answer you're looking for? Browse other questions tagged
sql-server
sql-server-2014
or ask your own question.

Hot Network Questions

Attribute values to "pervious" and "next" rows for aggregation (and allow exceptions) in query + view?

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged sql-serversql-server-2014 or ask your own question.

Related

Hot Network Questions

Not the answer you're looking for? Browse other questions tagged
sql-server
sql-server-2014
or ask your own question.