Query for Segments

All
I have a table that contains data for when a stretch of road was last
resurfaced and I am trying to return the cost per year for each completed
road segment. In the example below the road was resurfaced in 1971 and 1973
.
In 1971 the road was resurfaced from 1 kilometre to 4 kilometres and 5
kilometres to 9 kilometres. In 1973 the kilometre in between was resurfaced
.
I only want to show from start to finish the complete parts of the road that
were resurfaced and when as illustrated below:
CREATE TABLE rd_resurface
(
RoadNo NVARCHAR(20),
KMStart INT,
KMEnd INT,
Cost NUMERIC(13, 2),
Deprec NUMERIC(13, 2),
[Year] SMALLINT
)
GO
INSERT rd_resurface SELECT 'H001', 1, 2, 100.00, 10.00, 1971
INSERT rd_resurface SELECT 'H001', 2, 3, 100.00, 10.00, 1971
INSERT rd_resurface SELECT 'H001', 3, 4, 100.00, 10.00, 1971
INSERT rd_resurface SELECT 'H001', 4, 5, 100.00, 10.00, 1973
INSERT rd_resurface SELECT 'H001', 5, 6, 100.00, 10.00, 1971
INSERT rd_resurface SELECT 'H001', 6, 7, 100.00, 10.00, 1971
INSERT rd_resurface SELECT 'H001', 7, 8, 100.00, 10.00, 1971
INSERT rd_resurface SELECT 'H001', 8, 9, 100.00, 10.00, 1971
I am trying to return the results below:
RoadNo KMStart KMEnd Cost Deprec Year
H001 1 4 $300.00 $30.00 1971
H001 4 5 $100.00 $10.00 1973
H001 5 9 $400.00 $40.00 1971
Thanks for any assistance that can be provided.David,
I'm not sure I got it right as you didn't say if the same km of road can
appear more than once in the same year. Assuming it can't...
The following query calculates a grouping factor which is the last kmstart
value within the segment:
select *,
(select min(kmstart)
from rd_resurface as r2
where r2.roadno = r1.roadno
and r2.year = r1.year
and r2.kmstart >= r1.kmstart
and not exists
(select *
from rd_resurface as r3
where r3.roadno = r2.roadno
and r3.year = r2.year
and r3.kmstart = r2.kmend)) as grp
from rd_resurface as r1
RoadNo KMStart KMEnd Cost Deprec Year grp
-- -- -- -- -- -- --
H001 1 2 100.00 10.00 1971 3
H001 2 3 100.00 10.00 1971 3
H001 3 4 100.00 10.00 1971 3
H001 4 5 100.00 10.00 1973 4
H001 5 6 100.00 10.00 1971 8
H001 6 7 100.00 10.00 1971 8
H001 7 8 100.00 10.00 1971 8
H001 8 9 100.00 10.00 1971 8
The rest is simply to group the data and return the desired aggregates:
select roadno, year, min(kmstart) as kmstart, max(kmend) as kmend,
sum(cost) as cost, sum(deprec) as deprec
from (select *,
(select min(kmstart)
from rd_resurface as r2
where r2.roadno = r1.roadno
and r2.year = r1.year
and r2.kmstart >= r1.kmstart
and not exists
(select *
from rd_resurface as r3
where r3.roadno = r2.roadno
and r3.year = r2.year
and r3.kmstart = r2.kmend)) as grp
from rd_resurface as r1) as d
group by roadno, year, grp
roadno year kmstart kmend cost deprec
-- -- -- -- -- --
H001 1971 1 4 300.00 30.00
H001 1971 5 9 400.00 40.00
H001 1973 4 5 100.00 10.00
BG, SQL Server MVP
www.SolidQualityLearning.com
"David" <David@.discussions.microsoft.com> wrote in message
news:3A73BA53-4633-4E5C-B72C-EBB3D4B73472@.microsoft.com...
> All
> I have a table that contains data for when a stretch of road was last
> resurfaced and I am trying to return the cost per year for each completed
> road segment. In the example below the road was resurfaced in 1971 and
> 1973.
> In 1971 the road was resurfaced from 1 kilometre to 4 kilometres and 5
> kilometres to 9 kilometres. In 1973 the kilometre in between was
> resurfaced.
> I only want to show from start to finish the complete parts of the road
> that
> were resurfaced and when as illustrated below:
> CREATE TABLE rd_resurface
> (
> RoadNo NVARCHAR(20),
> KMStart INT,
> KMEnd INT,
> Cost NUMERIC(13, 2),
> Deprec NUMERIC(13, 2),
> [Year] SMALLINT
> )
> GO
> INSERT rd_resurface SELECT 'H001', 1, 2, 100.00, 10.00, 1971
> INSERT rd_resurface SELECT 'H001', 2, 3, 100.00, 10.00, 1971
> INSERT rd_resurface SELECT 'H001', 3, 4, 100.00, 10.00, 1971
> INSERT rd_resurface SELECT 'H001', 4, 5, 100.00, 10.00, 1973
> INSERT rd_resurface SELECT 'H001', 5, 6, 100.00, 10.00, 1971
> INSERT rd_resurface SELECT 'H001', 6, 7, 100.00, 10.00, 1971
> INSERT rd_resurface SELECT 'H001', 7, 8, 100.00, 10.00, 1971
> INSERT rd_resurface SELECT 'H001', 8, 9, 100.00, 10.00, 1971
> I am trying to return the results below:
> RoadNo KMStart KMEnd Cost Deprec Year
> H001 1 4 $300.00 $30.00 1971
> H001 4 5 $100.00 $10.00 1973
> H001 5 9 $400.00 $40.00 1971
>
> Thanks for any assistance that can be provided.sql