Price Variation Query for the same Stock Numbers

  • Dear All,

    I am trying to fetch Price Variation between two records (TR_UNITCOST) (Previous and Current based on Transaction No.) for the same stock number but unable to conclude anything so far. Seeking your help and Thank you in advance.

    Below is my query and out put (2.PNG) for your reference.

    Select *

    from

    (Select CT_STOCKNO_P,CT_STOCKSUMMARY,TR_UNITCOST,TR_DATE,TR_PONO,

    RANK() Over (PARTITION BY CT_STOCKNO_P ORDER BY TR_TRANSACTIONNO_P DESC) AS TRNS

    From ST_COMMODITYTYPE

    Inner Join ST_TRANSACTIONS ON ST_TRANSACTIONS.TR_STOCKNO=ST_COMMODITYTYPE.CT_STOCKNO_P

    Inner Join ST_LOCALPOLICY ON ST_LOCALPOLICY.PY_STOCKNO=ST_COMMODITYTYPE.CT_STOCKNO_P

    WHERE TR_TYPECODE = 'PREC') as RS

    WHERE TRNS<=2

  • Just want to verify that you are using SQL Server 2008. This will determine how a solution is developed. There are features in 2012 and newer that make this type of problem easier to solve.

  • Hi Lynn Pettis,

    Yes I am using SQL 2008. Thank you in advance for your help.

Viewing 3 posts - 1 through 2 (of 2 total)

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