3

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
3
  • 1
    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. Aug 29, 2012 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. Aug 29, 2012 at 18:04
  • @aaron yes. the proc would need to get all of the items that would need to be replaced and what years they would need to be replaced Aug 29, 2012 at 18:05

3 Answers 3

3

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

1
  • 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... Aug 30, 2012 at 15:12
5

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
0
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
8
  • 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 Aug 29, 2012 at 17:03
  • @spaghetticowboy can you please show sample data and desired results instead of describing word problems? Aug 29, 2012 at 17:04
  • 1
    @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 :-) Aug 29, 2012 at 17:07
  • @Aaron i added some details per your request Aug 29, 2012 at 17:08
  • @EdwardDortland Yes. I am sorry I was not more specific. Aug 29, 2012 at 17:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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