Sum Activity Code in Row Above

Other SQL Server Topics
1

Hi,

I'm fairly new to SQL and I'm looking for a bit of help with a data set. I have a report that shows engineer productivity in order of activity start time, date and ID. I'm trying to add a new column that sums the activity with the row above, if the row above is travel. If it's any other activity then just use the length already showing:

Date ID Start End Length Activity Adjusted Length
02/07/2021 Eng 1 07:35:00 08:00:00 25 Unproductive 25
02/07/2021 Eng 1 08:00:00 08:25:00 25 Travel 25
02/07/2021 Eng 1 08:25:00 11:08:00 163 Job 188
02/07/2021 Eng 1 11:08:00 11:26:00 18 Travel 18
02/07/2021 Eng 1 11:26:00 13:18:00 112 Job 130
02/07/2021 Eng 1 13:45:00 13:50:00 5 Travel 5
02/07/2021 Eng 1 13:50:00 14:49:00 59 Job 64
02/07/2021 Eng 1 14:49:00 15:02:00 13 Travel 13
02/07/2021 Eng 1 15:02:00 15:18:00 16 Job 29
02/07/2021 Eng 1 15:18:00 16:11:00 53 Travel 53
02/07/2021 Eng 1 16:11:00 17:16:00 65 Job 118
02/07/2021 Eng 1 17:16:00 17:53:00 37 Travel 37
03/07/2021 Eng 2 07:29:00 07:54:00 25 Unproductive 25
03/07/2021 Eng 2 07:54:00 11:44:00 230 Coaching 230
03/07/2021 Eng 2 11:44:00 12:25:00 41 Travel 41
03/07/2021 Eng 2 12:25:00 13:24:00 59 Job 100
03/07/2021 Eng 2 13:24:00 13:29:00 5 Unproductive 5
03/07/2021 Eng 2 13:57:00 14:04:00 7 Unproductive 7
03/07/2021 Eng 2 14:04:00 14:25:00 21 Travel 21
03/07/2021 Eng 2 14:25:00 15:12:00 47 Job 68
03/07/2021 Eng 2 15:12:00 15:19:00 7 Travel 7
03/07/2021 Eng 2 15:19:00 16:59:00 100 Job 107
03/07/2021 Eng 2 16:59:00 17:21:00 22 Travel 22

The caveat is that the formula has to be engineer and date specific to ensure the travel time isn't double counted anywhere.

Any help on this would be a big help.

Thanks in advance,
Gary

2

There is a nice feature that is called LEAD/LAG, that can do the trick.

1 Like
3

First, some nice, directly usable sample data (not all of yours but a majority):

CREATE TABLE #data ( Date date NOT NULL, ID varchar(30) NOT NULL, start time NOT NULL, [end] time NOT NULL, length smallint NOT NULL, activity varchar(100) NOT NULL )
INSERT INTO #data values
    ('02/07/2021',	'Eng 1', '07:35:00', '08:00:00', 25,	'Unproductive'),
    ('02/07/2021',	'Eng 1', '08:00:00', '08:25:00', 25,	'Travel'),
    ('02/07/2021',	'Eng 1', '08:25:00', '11:08:00', 163,	'Job'),
    ('02/07/2021',	'Eng 1', '11:08:00', '11:26:00', 18	,   'Travel'),
    ('02/07/2021',	'Eng 1', '11:26:00', '13:18:00', 112,	'Job'),
    ('02/07/2021',	'Eng 1', '13:45:00', '13:50:00', 5,	    'Travel'),
    ('02/07/2021',	'Eng 1', '13:50:00', '14:49:00', 59,	'Job'),
    ('02/07/2021',	'Eng 1', '14:49:00', '15:02:00', 13,	'Travel'),
    ('02/07/2021',	'Eng 1', '15:02:00', '15:18:00', 16,	'Job'),
    ('02/07/2021',	'Eng 1', '15:18:00', '16:11:00', 53,	'Travel'),
    ('02/07/2021',	'Eng 1', '16:11:00', '17:16:00', 65,	'Job'),
    ('02/07/2021',	'Eng 1', '17:16:00', '17:53:00', 37,	'Travel'),
    ('03/07/2021',	'Eng 2', '07:29:00', '07:54:00', 25,	'Unproductive'),
    ('03/07/2021',	'Eng 2', '07:54:00', '11:44:00', 230,	'Coaching'),
    ('03/07/2021',	'Eng 2', '11:44:00', '12:25:00', 41,	'Travel'),
    ('03/07/2021',	'Eng 2', '12:25:00', '13:24:00', 59,	'Job'),
    ('03/07/2021',	'Eng 2', '13:24:00', '13:29:00', 5,	    'Unproductive'),
    ('03/07/2021',	'Eng 2', '13:57:00', '14:04:00', 7,	    'Unproductive'),
    ('03/07/2021',	'Eng 2', '14:04:00', '14:25:00', 21,	'Travel	21'),
    ('03/07/2021',	'Eng 2', '14:25:00', '15:12:00', 47,	'Job	68'),
    ('03/07/2021',	'Eng 2', '15:12:00', '15:19:00', 7,	    'Travel	7'),
    ('03/07/2021',	'Eng 2', '15:19:00', '16:59:00', 100,	'Job	107'),
    ('03/07/2021',	'Eng 2', '16:59:00', '17:21:00', 22	,   'Travel	22')
1 Like
4

Now the SQL code for that data:

;WITH cte_data AS (
    SELECT *, LAG(length, 1) OVER(PARTITION BY ID ORDER BY start) AS prev_length, LAG(activity, 1) OVER(PARTITION BY ID ORDER BY start) AS prev_activity
    FROM #data
)
SELECT
    Date, ID, start, [end], 
    length + CASE WHEN prev_activity = 'Travel' THEN prev_length ELSE 0 END AS length, 
    activity
FROM cte_data
ORDER BY ID, start
1 Like
5

Exactly what I needed.

Thanks for your help, Scott!