Sql-server – Attribute values to “pervious” and “next” rows for aggregation (and allow exceptions) in query + view

sql serversql server 2014

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 |

Best Answer

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');

Related Solutions

Sql-server – Aggregation by positive and negative values

I use your code and added some checks, regarding the COST_CHANGE = 0

For Sql Server 2012, you can use the LAG (this is not available in 2008,2008R2)

--my source table
DECLARE @tPROD_COST TABLE
(ID INT /*IDENTITY(1,1)*/,
 Duration INT,
 Cost FLOAT, 
 COST_NEXT FLOAT, 
 COST_CHANGE FLOAT) 

 INSERT INTO @tPROD_COST(ID,Duration,Cost,COST_NEXT,COST_CHANGE) 
 VALUES  
(  1 , 1 , 10   , 8.5  , -1.5  ),
(  2 , 1 , 8.5  , 12.2 , 3.7   ),
(  3 , 1 , 12.2 , 5.3  , -6.9  ),
(  4 , 1 , 5.3  , 4.2  , 1.2   ),
(  5 , 1 , 4.2  , 6.2  , 2     ),
(  6 , 1 , 6.2  , 9.2  , 3     ),
(  7 , 1 , 9.2  , 7.5  , -2.7  ),
(  8 , 1 , 7.5  , 6.2  , -1.3  ),
(  9 , 1 , 6.2  , 6.3  , 0.1   ),
(  10 , 1 , 6.3  , 7.2  , 0.9  ),
(  11 , 1 , 7.2  , 7.5  , 0.3  ),
(  12 , 1 , 7.5  , 0    , 7.5  )
,(13,1,10,19.262,9.262)
,(14,1,9.262,9.262,0)
,(15,1,9.262,18.870 , 9.608)
,(16,1,18.870,18.870 , 0)
,(17,1,18.870,18.870 , 0)
,(18,1,18.870, 18.000, -0.870)
,(19,1,18.000, 18.000, 0)
,(20,1,18.000, 17.000, -1.00)

--for SQL 2012

SELECT ID,
      Duration,
      Cost,
      COST_NEXT,
      COST_CHANGE,
      SUM(GRP) OVER (ORDER BY ID ASC) +1 AS Groups
FROM ( 
    SELECT *, 
        CASE            
            WHEN SIGN(COST_CHANGE)!= SIGN(ISNULL(LAG(COST_CHANGE) OVER (ORDER BY ID ASC),COST_CHANGE)) 
                 AND ISNULL(LAG(COST_CHANGE) OVER (ORDER BY ID ASC),COST_CHANGE) <> 0
                 AND Cost_change != 0
            THEN 1 
            WHEN SIGN(COST_CHANGE)  <> 0
                AND SIGN(COST_CHANGE) <> SIGN(ISNULL(OA2.Prev_Cost_Change_NonZero,COST_CHANGE)) 
            THEN 1
            ELSE 0 END AS GRP 
    FROM @tPROD_COST AS PC
    OUTER APPLY  -- get the previous COST CHANGE <> 0
    (
        SELECT TOP(1)
            PC3.COST_CHANGE AS Prev_Cost_Change_NonZero
         FROM 
            @tPROD_COST AS PC3
         WHERE
            PC.ID>PC3.ID
            AND PC3.COST_CHANGE <> 0
        ORDER BY PC3.ID DESC
    )OA2(Prev_Cost_Change_NonZero)
    ) X

--for SQL 2008,2008R2:

;WITH cte
AS( SELECT ID,
          Duration,
          Cost,
          COST_NEXT,
          COST_CHANGE,
          GRP
    FROM
       ( SELECT ID, Duration, Cost, COST_NEXT, COST_CHANGE,
            CASE            
                WHEN SIGN(COST_CHANGE)!= SIGN(ISNULL(OA.Prev_Cost_Change,COST_CHANGE)) 
                     AND ISNULL(OA.Prev_Cost_Change,COST_CHANGE) <> 0
                     AND Cost_change != 0
                THEN 1 
                WHEN SIGN(COST_CHANGE)  <> 0
                    AND SIGN(COST_CHANGE) <> SIGN(ISNULL(OA2.Prev_Cost_Change_NonZero,COST_CHANGE)) 
                THEN 1
                ELSE 0 END AS GRP ,
                OA.Prev_Cost_Change,
                OA2.Prev_Cost_Change_NonZero
        FROM 
            @tPROD_COST AS PC
        OUTER APPLY  -- get the previous COST CHANGE
        (
            SELECT TOP(1)
                PC2.COST_CHANGE AS Prev_Cost_Change
             FROM 
                @tPROD_COST AS PC2
             WHERE
                PC.ID>PC2.ID
            ORDER BY PC2.ID DESC
        )OA(Prev_Cost_Change)
        OUTER APPLY  -- get the previous COST CHANGE <> 0
        (
            SELECT TOP(1)
                PC3.COST_CHANGE AS Prev_Cost_Change_NonZero
             FROM 
                @tPROD_COST AS PC3
             WHERE
                PC.ID>PC3.ID
                AND PC3.COST_CHANGE <> 0
            ORDER BY PC3.ID DESC
        )OA2(Prev_Cost_Change_NonZero)
    )A
)

    SELECT ID,
          Duration,
          Cost,
          COST_NEXT,
          COST_CHANGE,
          --GRP,
          ISNULL(OA.Prev_Sum_Grp,0) +1 AS Groups
    FROM
        cte AS C
    OUTER APPLY  -- get the previous SUM
    (
        SELECT 
            SUM(C2.GRP) AS Sum_GRP
         FROM 
            cte AS C2
         WHERE
            C.ID>=C2.ID
    )OA(Prev_Sum_GRP)

The output:

ID          Duration    Cost                   COST_NEXT              COST_CHANGE            Groups
----------- ----------- ---------------------- ---------------------- ---------------------- -----------
1           1           10                     8.5                    -1.5                   1
2           1           8.5                    12.2                   3.7                    2
3           1           12.2                   5.3                    -6.9                   3
4           1           5.3                    4.2                    1.2                    4
5           1           4.2                    6.2                    2                      4
6           1           6.2                    9.2                    3                      4
7           1           9.2                    7.5                    -2.7                   5
8           1           7.5                    6.2                    -1.3                   5
9           1           6.2                    6.3                    0.1                    6
10          1           6.3                    7.2                    0.9                    6
11          1           7.2                    7.5                    0.3                    6
12          1           7.5                    0                      7.5                    6
13          1           10                     19.262                 9.262                  6
14          1           9.262                  9.262                  0                      6
15          1           9.262                  18.87                  9.608                  6
16          1           18.87                  18.87                  0                      6
17          1           18.87                  18.87                  0                      6
18          1           18.87                  18                     -0.87                  7
19          1           18                     18                     0                      7
20          1           18                     17                     -1                     7

Sql-server – permissions for schema compare and view job history

For the msdb SQL Server Agent roles, granting SQLAgentUserRole allows them the ability to manage jobs which only they own, and see the job history of those jobs too.

To compare schema on all table of any DB, assuming you want to also allow them to have SELECT to all tables, the db_datareader role should be fine per DB.

Otherwise, if you want to be specific, then allow explicit VIEW DEFINITION on the schema per DB which you want to all them to compare and SELECT to the tables you want them to be able to compare the data.

EXPLICIT EXAMPLES TSQL (per the article)

GRANT VIEW DEFINITION ON OBJECT::Region TO Ted
GRANT SELECT ON OBJECT::Region TO Ted

EXPLICIT EXAMPLES TSQL (per the request in your screen shot)

GRANT VIEW ANY DEFINITION TO User

USE msdb

EXECUTE sp_addrolemember @rolename = 'SQLAgentReaderRole'
    ,@membername = 'username'

I think it depends on how well you want to lock it down really, and how trustworthy they are for what you are allowing them to have access to.

Best Answer

Related Solutions

Sql-server – Aggregation by positive and negative values

Sql-server – permissions for schema compare and view job history

Related Question