Complicated select statement? or not...

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Complicated select statement? or not...

Author

Topic

JacekK
Starting Member

14 Posts

Posted - 2011-01-11 : 12:58:45

I have a table of roads that are segmented by speed at specific segments. I need to keep 1 record for each road and assign a speed to it based on a set of specific rules:

if (SPEED = 40 and DISTANCE >= 100) then SPEED 40

else assign the speed from the longest of combined distances.

Here is an example of simplifed table and desired results:

DECLARE @T TABLE(ROAD VARCHAR(1), SPEED int, DISTANCE INT)

INSERT @T (ROAD, SPEED,DISTANCE)
VALUES
('A', 50, 100),
('A', 40, 75),
('A', 60, 200),
('A', 40, 50),
('B', 50, 100),
('B', 40, 75),
('B', 60, 125),
('B', 50, 50),
('C', 50, 100),
('C', 60, 150),
('C', 40, 50)

DECLARE @T_r TABLE(ROAD VARCHAR(1), SPEED int, DISTANCE INT)
INSERT @T_r (ROAD, SPEED,DISTANCE)
VALUES
('A', 40, 125),
('B', 50, 150),
('C', 60, 150)

SELECT * FROM @T
SELECT * FROM @T_r

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-01-11 : 14:29:00

I'm not sure I understand. Can you detail the logic on how you calcualted the first result row?

JacekK
Starting Member

14 Posts

Posted - 2011-01-11 : 14:40:00

Sure. Sorry for not being too clear on this...

Add distances of all segments with the same speed for road A.
We get:

Road, Speed, Distance
('A', 50, 100),
('A', 40, 125),
('A', 60, 200)

If Speed = 40 and cumulative distance >= 100, assign 40 speed to that road
Final result :
Road, Speed, Distance
('A', 40, 125)

I hope this makes it a little more sense...

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-01-11 : 15:18:01

Ahh got it:

SELECT Road, Speed, Distance
FROM
(
    SELECT Road, Speed, SUM(Distance) AS Distance,
    ROW_NUMBER() OVER(PARTITION BY Road ORDER BY CASE WHEN Speed = 40 AND SUM(Distance) >= 100 THEN 0 ELSE 1 END, SUM(Distance) DESC) AS RowNum
    FROM @T
    GROUP BY Road, Speed
) AS T
WHERE RowNum = 1
ORDER BY Road