locked
select last time quantity starts increasing RRS feed

  • Question

  •  Hi, i am trying to retreive the last date when the quantity starts increasing. Below is an example to show you what I mean:

    date qty
    2009-02-13 13
    2009-04-27 -33
    2009-05-07 13
    2009-12-10 15
    2009-12-11 30
    2009-12-12 40

    Therefore the last time the quantity starts increasing is on
    2009-05-07. The one before that is on 2009-02-13 but since i want the last time it starts increasing then my result should be 2009-05-07.

    My query looks like this for now

    Select max(M.[Date])
    from atp_temp T
    OUTER APPLY
    (Select date from atp_temp
    Where [qty] < T.[qty])M

    But the result is not what i want as it retuns 2009-12-11.

    I know it can be done in a while loop but im trying to avoid while loops.

    Any ideas???

    Thank you

    eli
    Wednesday, February 18, 2009 12:31 AM

Answers

  • Try this, good sir.

    --Sample data  
    DECLARE @MyDates TABLE 
        (  
        datecol datetime,  
        qty int 
        )  
          
    INSERT @MyDates(datecol, qty)  
    SELECT '2009-02-13', 13   
    UNION ALL SELECT '2009-04-27', -33   
    UNION ALL SELECT '2009-05-07', 13   
    UNION ALL SELECT '2009-12-10', 15   
    UNION ALL SELECT '2009-12-11', 30  
    UNION ALL SELECT '2009-12-12', 40;  
     
    --The goods  
    with NumberedDates (datecol, qty, RowNumber)  
    AS 
    (SELECT datecol, qty, row_number() OVER (ORDER BY datecol ASCAS RowNuber  
    FROM        @MyDates)  
     
    SELECT          max(a.Today) AS [WooHoo!]  
    FROM    (  
            SELECT          nd1.datecol as Today, nd2.datecol AS Yesterday,   
                            nd3.datecol AS YesterdayEve  
            FROM            NumberedDates nd1  
            LEFT OUTER JOIN NumberedDates nd2 ON nd1.RowNumber = nd2.RowNumber + 1  
            LEFT OUTER JOIN NumberedDates nd3 ON nd2.RowNumber = nd3.RowNumber + 1  
            WHERE           nd2.datecol IS NULL 
            OR              (nd3.datecol IS NULL AND nd2.qty < nd1.qty)  
            OR              (nd3.qty > nd2.qty AND nd2.qty < nd1.qty)  
            ) AS a 

    It uses ROW_NUMBER(), and a Common Table Expression to work it's magic.

    If you don't understand any part of it, please let me know what part(s) you would like me to explain - it's important that you understand how it works so you can do it next time!


    Aaron Alton | thehobt.blogspot.com
    Wednesday, February 18, 2009 3:35 AM
  • Yup, Aaron is right :)

    Here is little modifed one -

    ;with NumberedDates (datecol, qty, RowNuMber)     
    AS    
    (  
    SELECT datecol, qty, row_number() OVER (ORDER BY datecol ASCAS RowNumber     
    FROM        MyDates  
    )   
    , CTE as(   
    SELECT  MAX(a.rownumber) as Mx  
    FROM NumberedDates A LEFT JOIN NumberedDates B  
    ON a.RowNumber -1 =b.RowNumber  
    AND a.qty >b.qty  
    WHERE B.Rownumber is null 
    )  
    SELECT TOP 1 a.DATECOL  
    FROM NumberedDates A JOIN CTE B  
    ON a.rownumber> b.mx  
    ORDER BY Datecol  



    Mangal Pardeshi
    SQL With Mangal
    Technical Skill is the mastery of complexity, while Creativity is the master of simplicity
    Wednesday, February 18, 2009 5:55 AM

All replies

  • Try this, good sir.

    --Sample data  
    DECLARE @MyDates TABLE 
        (  
        datecol datetime,  
        qty int 
        )  
          
    INSERT @MyDates(datecol, qty)  
    SELECT '2009-02-13', 13   
    UNION ALL SELECT '2009-04-27', -33   
    UNION ALL SELECT '2009-05-07', 13   
    UNION ALL SELECT '2009-12-10', 15   
    UNION ALL SELECT '2009-12-11', 30  
    UNION ALL SELECT '2009-12-12', 40;  
     
    --The goods  
    with NumberedDates (datecol, qty, RowNumber)  
    AS 
    (SELECT datecol, qty, row_number() OVER (ORDER BY datecol ASCAS RowNuber  
    FROM        @MyDates)  
     
    SELECT          max(a.Today) AS [WooHoo!]  
    FROM    (  
            SELECT          nd1.datecol as Today, nd2.datecol AS Yesterday,   
                            nd3.datecol AS YesterdayEve  
            FROM            NumberedDates nd1  
            LEFT OUTER JOIN NumberedDates nd2 ON nd1.RowNumber = nd2.RowNumber + 1  
            LEFT OUTER JOIN NumberedDates nd3 ON nd2.RowNumber = nd3.RowNumber + 1  
            WHERE           nd2.datecol IS NULL 
            OR              (nd3.datecol IS NULL AND nd2.qty < nd1.qty)  
            OR              (nd3.qty > nd2.qty AND nd2.qty < nd1.qty)  
            ) AS a 

    It uses ROW_NUMBER(), and a Common Table Expression to work it's magic.

    If you don't understand any part of it, please let me know what part(s) you would like me to explain - it's important that you understand how it works so you can do it next time!


    Aaron Alton | thehobt.blogspot.com
    Wednesday, February 18, 2009 3:35 AM
  • Hi,

    How about this - (I used the sample data created by Arron :) )

    ;with NumberedDates (datecol, qty, RowNuMber)     
    AS    
    (  
    SELECT datecol, qty, row_number() OVER (ORDER BY datecol ASCAS RowNumber     
    FROM        MyDates  
    )   
    SELECT TOP 1 a.datecol  
    FROM NumberedDates A LEFT JOIN NumberedDates B  
    ON a.RowNumber -1 =b.RowNumber  
    WHERE a.qty >b.qty  
    ORDER BY a.datecol ASC 

    Mangal Pardeshi
    SQL With Mangal
    Technical Skill is the mastery of complexity, while Creativity is the master of simplicity
    Wednesday, February 18, 2009 4:53 AM
  • Mangal,

    That works with the sample data provided, but not if you expand the example.  The OP was looking for the last date when the quantity starts increasing, so if you extrapolate on the sample data:

    DECLARE @MyDates TABLE    
        (     
        datecol datetime,     
        qty int    
        )     
             
    INSERT @MyDates(datecol, qty)     
    SELECT '2009-02-13', 13      
    UNION ALL SELECT '2009-04-27', -33      
    UNION ALL SELECT '2009-05-07', 13      
    UNION ALL SELECT '2009-12-10', 15      
    UNION ALL SELECT '2009-12-11', 30     
    UNION ALL SELECT '2009-12-12', 40  
    UNION ALL SELECT '2009-12-15', 30  
    UNION ALL SELECT '2009-12-20', 50  
    UNION ALL SELECT '2009-12-25', 59; 

    Your solution will still return 2009-05-07, whereas it needs to return 2009-12-20.
    Aaron Alton | thehobt.blogspot.com
    Wednesday, February 18, 2009 5:37 AM
  • Yup, Aaron is right :)

    Here is little modifed one -

    ;with NumberedDates (datecol, qty, RowNuMber)     
    AS    
    (  
    SELECT datecol, qty, row_number() OVER (ORDER BY datecol ASCAS RowNumber     
    FROM        MyDates  
    )   
    , CTE as(   
    SELECT  MAX(a.rownumber) as Mx  
    FROM NumberedDates A LEFT JOIN NumberedDates B  
    ON a.RowNumber -1 =b.RowNumber  
    AND a.qty >b.qty  
    WHERE B.Rownumber is null 
    )  
    SELECT TOP 1 a.DATECOL  
    FROM NumberedDates A JOIN CTE B  
    ON a.rownumber> b.mx  
    ORDER BY Datecol  



    Mangal Pardeshi
    SQL With Mangal
    Technical Skill is the mastery of complexity, while Creativity is the master of simplicity
    Wednesday, February 18, 2009 5:55 AM