Post reply

populate a priority field based on who quantity

  • October 23, 2012 at 4:55 am

    #264547

    Do you know of a way I can give a priority number to the following table [PriorityStores]

    Table[rsg_PriorityStores]

    ID , storeID , priority,warehouse , itemHQID

    1 , 1 , 0 , 1 , 4

    3 , 2 , 0 , 0 , 4

    5 , 3 , 0 , 0 , 4

    Table[##SalesData]

    quantity , ItemHQID , storeid

    1 , 4 , 1

    2 , 4 , 2

    3 , 4 , 3

    So from this you can see I have a priority field in first table, what I need to do is set this to a priority number based on who sold the most from table 2

    So it should end up like:

    ID , storeID , priority ,warehouse , itemHQID

    1 , 1 , 3 , 1 , 4

    3 , 2 , 2 , 0 , 4

    5 , 3 , 1 , 0 , 4

  • October 23, 2012 at 5:10 am

    #1551828

    Is ##SalesData a global temp table in your database?

    You could do it with a trigger if it's not a temp table, but I'd strongly recommend using a view or function for this.

  • October 23, 2012 at 5:26 am

    #1551833

    ##Salesdata is a tempory table which i create. So does not need to be tempory.

    I just need a way of giving each store a priorty number based on which sold the most.

    Im unsure of the Sql that can create the priorty value.

  • October 23, 2012 at 5:57 am

    #1551850

    I think this is the way to go:

    select rank() OVER (ORDER BY quantity desc ,itemhqid ) as rank,storeID,itemhqid

    from ##SalesData

    order by rank

  • October 23, 2012 at 6:47 am

    #1551865

    Yep, that's a good way - you can roll it into a view or function if you need to calculate it from other sources.

    If you need to work it out for multiple values of HQID, look into the PARTITION clause of RANK.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply