Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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:

    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
share|improve this question
It would also be good to add some sample data. And which DBMS you are using. – ypercubeᵀᴹ Aug 29 '12 at 15:22
in my actual usecase there would be another bunch of other columns with different cotextual data, but this is the basics of what i need to do – spaghetticowboy Aug 29 '12 at 15:30
a thought would be to join against a table full of years throughout that range, or to iterate through the range and the table... the later seems like it would be sooo inefficient – spaghetticowboy Aug 29 '12 at 15:36
Does the stored procedure need to get all the items in the table, or should it also take an ItemID as a parameter? Your sample data of a single row makes the requested solution ambiguous. – Aaron Bertrand Aug 29 '12 at 17:49
Also, if the range is 2011-2030, should item1 show up at all? Should it only return rows >= 2011, even though the installYear pre-dates the selected range? There are a lot of edge cases your simplistic sample doesn't cover. – Aaron Bertrand Aug 29 '12 at 18:04
up vote 3 down vote accepted

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

    ;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
      SELECT ID, ItemName, InstallYear
       FROM dbo.Items
       WHERE InstallYear BETWEEN @startyear AND @endyear
    ORDER BY ID, ReplaceYear;

Here's a sqlfiddle that demonstrates:!3/09824f/2

share|improve this answer
This did the trick. I did not need the union because i still wanted to know schedules of items that would need to be replaced from earlier periods. Now i need to know how to handle them... – spaghetticowboy Aug 30 '12 at 15:12

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)


    @StartYear int,
    @EndYear int
    SELECT *, InstallYear + Life AS ReplaceYear 
    FROM Items 
    WHERE InstallYear <= @EndYear AND (InstallYear + Life) >= @StartYear;

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       2016

This works:

    @StartYear int,
    @EndYear int
    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

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
share|improve this answer
Why would this be better as a view or ad hoc query? Encapsulation in a stored procedure is the perfect place for something that has parameters. A view can't be parameterized and most people are very lazy about parameterizing ad hoc queries correctly. – Aaron Bertrand Aug 29 '12 at 17:03
Code in the client side app is a bad thing, for two reasons: (1) if you have to change or tune the query, you have to recompile and redeploy the clients (2) if multiple apps call the same code, now you have redundancy (the bad kind) and you'll have to deploy changes to multiple apps. I don't want to turn this into a religious debate, as it often can, but if a user is already asking for stored procedures, this site should not be discouraging them away from it unless there is a good reason (e.g. table-valued function instead). – Aaron Bertrand Aug 29 '12 at 17:07
For developers, a stored procedure should be a black box. IMHO. Some developers can tune a query and understand the underlying DB mechanics, but if their job is to develop the app and not tune the database, those lines should cross as little as possible. The DBA or architect should be able to change the data access code without having to become a developer (and by that I mean pull the app code from source control, change a database query, and re-compile). – Aaron Bertrand Aug 29 '12 at 17:52
CREATE PROCEDURE getreplacementswithinyearrang(@startyear int, @endyear int)
    SELECT  itemID,
            InstallYear + life as replaceDtae
    FROM    dbo.Items

    WHERE @startyear <= (InstallYear+life)
    AND   @endyear > (InstallYear+Life)
share|improve this answer
This would only ever give me 1 instance of the item. I am looking for a solution that would give me an instance for each time the item would need to be replaced – spaghetticowboy Aug 29 '12 at 17:03
@spaghetticowboy can you please show sample data and desired results instead of describing word problems? – Aaron Bertrand Aug 29 '12 at 17:04
@spaghettiCowboy, do you mean that the life column is as an repeatable number? as in: Every 2 years? That would be a totally different ball game :-) – Edward Dortland Aug 29 '12 at 17:07
@Aaron i added some details per your request – spaghetticowboy Aug 29 '12 at 17:08
@EdwardDortland Yes. I am sorry I was not more specific. – spaghetticowboy Aug 29 '12 at 17:08

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.