SQL Server conditional subtotal query

Question

given the following table:

    create table #T
    (
    user_id int,
    project_id int,
    datum datetime,
    status varchar(10),
    KM int
    )

insert into #T values 
    (1, 1, '20160301 10:25', 'START', 1000), 
    (1, 1, '20160301 10:28', 'PASS', 1008), 
    (2, 2, '20160301 10:29', 'START', 2000),
    (1, 1, '20160301 11:08', 'STOP', 1045), 
    (3, 3, '20160301 10:25', 'START', 3000),
    (2, 2, '20160301 10:56', 'STOP', 2020), 
    (1, 4, '20160301 15:00', 'START', 1045), 
    (4, 5, '20160301 15:10', 'START', 400), 
    (1, 4, '20160301 15:10', 'PASS', 1060), 
    (1, 4, '20160301 15:20', 'PASS', 1080), 
    (1, 4, '20160301 15:30', 'STOP', 1080), 
    (4, 5, '20160301 15:40', 'STOP', 450),
    (3, 3, '20160301 16:25', 'STOP', 3200)

I have to sum the length of a track between START and STOP statuses for a given user and project The expected result would be this:

    user_id     project_id  datum      TOTAL_KM
----------- ----------- ---------- -----------
1           1           2016-03-01 45
1           4           2016-03-01 35
2           2           2016-03-01 20
3           3           2016-03-01 200
4           5           2016-03-01 50

How can I achieve this without using a cluster? The performance is an issue (I have over 1 million records per month and we have to keep data for several years)

Explanation: We can ignore the records with the status "PASS". Basically we have to subtract the KM value of the START record from the STOP record for a given user and project. There can be several hundred records between a START and STOP (like describes in the sample data) The date should be the date of START (in case where we have an over midnight delivery)

I think I should have a SELECT with an OVER() clause but I don't know how to formulate my query to respect those conditions.

Any idea?

What version of SQL Server are you using?

Wyatt Shipman
– Wyatt Shipman

2016-03-31 19:32:59 +00:00
Commented Mar 31, 2016 at 19:32 — Wyatt Shipman
– Wyatt Shipman, Commented Mar 31, 2016 at 19:32

gofr1 · Accepted Answer · 2016-03-31 20:12:46Z

2

SELECT  t.[user_id],
        t.project_id,
        cast(t.datum as date) as datum,
        t1.KM- t.KM as KM
FROM #T t
INNER JOIN #T t1 
    ON t.[user_id]=t1.[user_id] and t.project_id = t1.project_id 
WHERE t.[status] = 'START' and t1.[status] = 'STOP'
ORDER BY t.[user_id],
        t.project_id,
        cast(t.datum as date)

Output:

user_id     project_id  datum      KM
----------- ----------- ---------- -----------
1           1           2016-03-01 45
1           4           2016-03-01 35
2           2           2016-03-01 20
3           3           2016-03-01 200
4           5           2016-03-01 50

(5 row(s) affected)

edited Mar 31, 2016 at 20:12

answered Mar 31, 2016 at 19:48

gofr1

16k11 gold badges47 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Wyatt Shipman Over a year ago

If you are only looking for completed trips, it's probably better to do an inner join.

rocky · Accepted Answer · 2016-03-31 19:42:34Z

0

This could be achieved by simple self join.

One of the example: (this may not be exact query but just an idea)

Select 
    a.user_id,
    a.project_id,
    b.datum as StartDate,
    a.KM-b.KM as TotalKM
From #T a
Where status = 'STOP'
Join 
(
    Select user_id, project_id, KM From #t Where 
    status = 'START'
) b ON b.user_id = a.user_id, b.project_id = a.project_id
#T b

answered Mar 31, 2016 at 19:42

rocky

1773 silver badges15 bronze badges

2 Comments

Gabor Over a year ago

This is not a working SQL query in none of the database dialects. :-(

rocky Over a year ago

@Gabor as i said already it was a conceptual Idea not exact work result you wanted, so point of Devoting just because you are expecting exact query.

Collectives™ on Stack Overflow

SQL Server conditional subtotal query

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related