Take the 2-minute tour ×
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. It's 100% free, no registration required.

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
share|improve this question
1  
Could you add some details about the table in question? Like the field names and data types? –  Max Vernon Aug 29 '12 at 15:17
1  
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
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. –  Aaron Bertrand Aug 29 '12 at 17:49
show 3 more comments

3 Answers 3

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

share|improve this answer
    
This shows me that I know nuffing. Must learn more! –  Max Vernon Aug 29 '12 at 18:27
    
Thanks for taking an interest in my question @Aaron. I will likely try and implement this tomorrow morning. –  spaghetticowboy Aug 29 '12 at 18:30
    
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
add comment

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
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
    
true, it is just such a simple little function. I guess this comes from my preference to have the code in the client side of the app. Which comes from my roots as a programmer first, dba second. –  Max Vernon Aug 29 '12 at 17:05
    
thanks for those edits, Aaron. I guess I should be more careful to check my code and ensure it actually runs! –  Max Vernon Aug 29 '12 at 17:07
2  
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
    
I can see what you're saying. And I'm right there with hating redundancy (the bad kind). The only downside to having server-side code (again from the developer's point of view only) is that debugging becomes more difficult, and reading code (for code reviews, for instance) becomes more tedious. Having said that, I think your 2 points probably override the downsides I've raised. –  Max Vernon Aug 29 '12 at 17:50
show 1 more comment
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
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
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 :-) –  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
show 3 more comments

Your Answer

 
discard

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.