patternsqlMinor
selecting multiple instances of a record based on record lifespan over years
Viewed 0 times
instancesrecordmultiplebasedselectinglifespanyearsover
Problem
I have a table of items that have a origin date and a life span. I want to create a procedure that selects all of the items that will need to be replaced in a specific duration. I feel like there is a math way of doing this in the where clause of a single select. The only way i can think of doing it is iterating though the years and appending the items that would be up for replacement... that seems super slow and inefficient. How should I approach this issue?
Details per comment requests:
Database Engine: SQL Server 2005
Source Table:
Desired stored procedure format:
Desired output:
More details:
Sample Records:
Desired results for range between 2010 and 2030:
Details per comment requests:
Database Engine: SQL Server 2005
Source Table:
CREATE TABLE Items
(
ID int,
ItemName varchar(255),
InstallYear int,
UsefullLife int
);Desired stored procedure format:
GetReplacementsWithinYearRange(startyear int, endyear int)Desired output:
ItemID - ItemName - ReplaceYearMore details:
Sample Records:
1 item1 2010 5
2 item2 2011 6Desired results for range between 2010 and 2030:
1 item1 2010
1 item1 2015
1 item1 2020
1 item1 2025
1 item1 2030
2 item2 2011
2 item2 2017
2 item2 2023
2 item2 2029Solution
Am I over simplifying here?
Returns:
This works:
CREATE TABLE Items (ItemID int, ItemName nvarchar(255), InstallYear int, Life int)
INSERT INTO Items VALUES (1,'test1',2010,2)
INSERT INTO Items VALUES (2,'test2',2011,2)
INSERT INTO Items VALUES (3,'test3',2012,5)
INSERT INTO Items VALUES (4,'test4',2013,3)
SELECT * FROM Items
GO
CREATE PROCEDURE GetItemLifetimeInfo
(
@StartYear int,
@EndYear int
)
AS
BEGIN
SELECT *, InstallYear + Life AS ReplaceYear
FROM Items
WHERE InstallYear = @StartYear;
END
GO
EXEC GetItemLifetimeInfo 2011, 2022;Returns:
ItemID ItemName InstallYear Life ReplaceYear
1 test1 2010 2 2012
2 test2 2011 2 2013
3 test3 2012 5 2017
4 test4 2013 3 2016This works:
CREATE PROCEDURE GetItemLifetimeInfo
(
@StartYear int,
@EndYear int
)
AS
BEGIN
WITH ReplaceYears(ItemID, [Year])
AS (
SELECT ItemID, Life
FROM Items
UNION ALL
SELECT Items.ItemID, Items.Life + ReplaceYears.Year
FROM Items
INNER JOIN ReplaceYears ON Items.ItemID = ReplaceYears.ItemID
WHERE ReplaceYears.Year = @StartYear
ORDER BY 1,2
END
EXEC GetItemLifetimeInfo 2011,2022;
ItemID ReplaceYear
1 2012
1 2014
1 2016
1 2018
1 2020
1 2022
2 2013
2 2015
2 2017
2 2019
2 2021
3 2017
3 2022
4 2016
4 2019
4 2022Code Snippets
CREATE TABLE Items (ItemID int, ItemName nvarchar(255), InstallYear int, Life int)
INSERT INTO Items VALUES (1,'test1',2010,2)
INSERT INTO Items VALUES (2,'test2',2011,2)
INSERT INTO Items VALUES (3,'test3',2012,5)
INSERT INTO Items VALUES (4,'test4',2013,3)
SELECT * FROM Items
GO
CREATE PROCEDURE GetItemLifetimeInfo
(
@StartYear int,
@EndYear int
)
AS
BEGIN
SELECT *, InstallYear + Life AS ReplaceYear
FROM Items
WHERE InstallYear <= @EndYear AND (InstallYear + Life) >= @StartYear;
END
GO
EXEC GetItemLifetimeInfo 2011, 2022;ItemID ItemName InstallYear Life ReplaceYear
1 test1 2010 2 2012
2 test2 2011 2 2013
3 test3 2012 5 2017
4 test4 2013 3 2016CREATE PROCEDURE GetItemLifetimeInfo
(
@StartYear int,
@EndYear int
)
AS
BEGIN
WITH ReplaceYears(ItemID, [Year])
AS (
SELECT ItemID, Life
FROM Items
UNION ALL
SELECT Items.ItemID, Items.Life + ReplaceYears.Year
FROM Items
INNER JOIN ReplaceYears ON Items.ItemID = ReplaceYears.ItemID
WHERE ReplaceYears.Year <= (@EndYear - @StartYear)
)
SELECT Items.ItemID, InstallYear + ReplaceYears.Year AS ReplaceYear
FROM Items
INNER JOIN ReplaceYears ON Items.ItemID = ReplaceYears.ItemID
WHERE InstallYear + ReplaceYears.Year <= @EndYear
AND (InstallYear + ReplaceYears.Year) >= @StartYear
ORDER BY 1,2
END
EXEC GetItemLifetimeInfo 2011,2022;
ItemID ReplaceYear
1 2012
1 2014
1 2016
1 2018
1 2020
1 2022
2 2013
2 2015
2 2017
2 2019
2 2021
3 2017
3 2022
4 2016
4 2019
4 2022Context
StackExchange Database Administrators Q#23352, answer score: 5
Revisions (0)
No revisions yet.