selecting multiple instances of a record based on record lifespan over years

Question

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:

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 - ReplaceYear

---

More details:

Sample Records:

1  item1  2010  5    
2  item2  2011  6

Desired 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  2029

Answer 1

This won't be fantastic depending on the indexes on the Items table, but should be much more efficient than the loop you were thinking about. In almost all cases, a set-based query will perform much better than iteration of any kind - there are a few exceptions, but you should only end up with a loop if it's actually necessary or proves to perform better than a set approach, never as a first reaction. IMHO.

This procedure takes advantage of a catalog view to build a set of numbers on the fly that represents the largest number of replacements that could be possible, given the input start/end year, if the smallest lifespan is one year. You could reduce this if the smallest span is 2 years, etc., but it won't really change the performance profile. Then it uses those numbers to find replacement years, based on modulo, like your approach would have - but it uses a set instead. There is probably a way I could have finagled the UNION into the JOIN but it seemed easier to call this part of the query out separately.

CREATE PROCEDURE dbo.GetReplacementsWithinYearRange
    @startyear int, 
    @endyear   int
AS
BEGIN
    SET NOCOUNT ON;

    ;WITH n(n) AS 
    (
      SELECT TOP (@endyear - @startyear + 1) ROW_NUMBER() OVER 
      (ORDER BY [object_id]) FROM sys.all_columns
    )
    SELECT ID, ItemName, ReplaceYear FROM
    (
     SELECT i.ID, i.ItemName, ReplaceYear = n.n + i.InstallYear
     FROM n INNER JOIN dbo.Items AS i
     ON (n.n - 1) % i.UsefullLife = i.UsefullLife - 1
     AND n.n + i.InstallYear > @startyear
     AND n.n + i.InstallYear <= @endyear
    ) AS x
    UNION
      SELECT ID, ItemName, InstallYear
       FROM dbo.Items
       WHERE InstallYear BETWEEN @startyear AND @endyear
    ORDER BY ID, ReplaceYear;
END
GO

Here's a sqlfiddle that demonstrates: http://sqlfiddle.com/#!3/09824f/2

Answer 2

Am I over simplifying here?

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;

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       2016

---

This 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 <= (@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   2022

Answer 3

CREATE PROCEDURE getreplacementswithinyearrang(@startyear int, @endyear int)
AS
BEGIN
    SELECT  itemID,
            itemName,
            InstallYear + life as replaceDtae
    FROM    dbo.Items

    WHERE @startyear <= (InstallYear+life)
    AND   @endyear > (InstallYear+Life)
END