locked
sql query, rank, no price change last 5 years RRS feed

  • Question

  • Dear SQL Server Experts,

    I have the following colums:

    sentdate, price, partId

    I need a way to figure partIds for which the price did not increase in the last 5 years.
    I guess I have to use the RANK function?
    Could someone point me in the right direction?
    • Moved by Tom Phillips Monday, February 11, 2019 8:27 PM TSQL question
    Monday, February 11, 2019 8:09 PM

Answers

  • --"...a way to figure partIds for which the price did not increase in the last 5 years. " create table test (sentdate datetime, price decimal(6,2), partId int) insert into test values ('2019-01-01',10.01,1) ,('2019-01-01',10.09,2) ,('2017-01-01',10.01,2) ,('2019-01-01',10.01,3) ,('2016-01-01',10.01,3) ;with mycte as ( Select * , dense_rank() OVER(Partition by partid Order by price) dnk ,count(*) OVER (Partition by partid) cnt from test WHERE sentdate>= dateadd(year,-5,getdate()) ) Select partid from test except Select partid from mycte WHERE dnk>1 or (dnk=1 and cnt>1) /* --or

    Select partid from   mycte
     WHERE  dnk=1 and cnt=1 

    */ drop table test



    • Edited by Jingyang Li Tuesday, February 12, 2019 7:54 PM
    • Marked as answer by metallon123 Wednesday, February 13, 2019 5:48 PM
    Tuesday, February 12, 2019 7:04 PM

All replies

  • create table test (sentdate datetime, price decimal(6,2), partId int)
    
    insert into test values('2019-01-01',10.01,1)
    ,('2019-01-01',10.09,2)
    ,('2017-01-01',10.01,2)
    ,('2019-01-01',10.09,3)
    ,('2010-01-01',10.01,3)
    
    
    ;with mycte as (
    Select *, dense_rank() OVER(Partition by partid Order by price) dnk from test
    WHERE sentdate>= dateadd(year,-5,getdate())
    )
    
    Select partid from test 
    except
    Select   partid from mycte
    WHERE dnk>1
    
    drop table test


    • Edited by Jingyang Li Monday, February 11, 2019 8:45 PM
    Monday, February 11, 2019 8:40 PM
  • Dear SQL Server Experts,

    I have the following colums:

    sentdate, price, partId

    I need a way to figure partIds for which the price did not increase in the last 5 years.
    I guess I have to use the RANK function?
    Could someone point me in the right direction?

    No sample data and desired output with business rules, it is hard to figure out which situation you need to achieve.

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, February 12, 2019 2:16 AM

  • Hi  Jingyang Li,

    Many thanks for your reply.
    I just edited one row for the sample data and it does not seems to work.
    I get partid 1 but only 3 is the one where really no increase in price happened over the last 5 years

    create table test (sentdate datetime, price decimal(6,2), partId int)
    
    insert into test values
     ('2019-01-01',10.01,1)
    ,('2019-01-01',10.09,2)
    ,('2017-01-01',10.01,2)
    ,('2019-01-01',10.01,3)
    ,('2016-01-01',10.01,3)
    
    
    ;with mycte as (
    Select *, dense_rank() OVER(Partition by partid Order by price) dnk from test
    WHERE sentdate>= dateadd(year,-5,getdate())
    )
    
    Select partid from test
    except
    Select partid from mycte
    WHERE dnk>1


    Tuesday, February 12, 2019 7:00 PM
  • --"...a way to figure partIds for which the price did not increase in the last 5 years. " create table test (sentdate datetime, price decimal(6,2), partId int) insert into test values ('2019-01-01',10.01,1) ,('2019-01-01',10.09,2) ,('2017-01-01',10.01,2) ,('2019-01-01',10.01,3) ,('2016-01-01',10.01,3) ;with mycte as ( Select * , dense_rank() OVER(Partition by partid Order by price) dnk ,count(*) OVER (Partition by partid) cnt from test WHERE sentdate>= dateadd(year,-5,getdate()) ) Select partid from test except Select partid from mycte WHERE dnk>1 or (dnk=1 and cnt>1) /* --or

    Select partid from   mycte
     WHERE  dnk=1 and cnt=1 

    */ drop table test



    • Edited by Jingyang Li Tuesday, February 12, 2019 7:54 PM
    • Marked as answer by metallon123 Wednesday, February 13, 2019 5:48 PM
    Tuesday, February 12, 2019 7:04 PM