how to get result from this data.

Question

I want to compute result from this table. I want quantity 1 - quantity2 as another column in the table shown below. this table has more such records

I am trying to query but not been able to get result.

select  * from v order by is_active desc, transaction_id desc


PK_GUEST_ITEM_ID FK_GUEST_ID          QUANTITY               TRANSACTION_ID       IS_ACTIVE
---------------- -------------------- ---------------------- -------------------- -----------
12963            559                  82000                  795                  1
12988            559                  79000                  794                  0
12987            559                  76000                  793                  0
12986            559                  73000                  792                  0
12985            559                  70000                  791                  0
12984            559                  67000                  790                  0
12983            559                  64000                  789                  0
12982            559                  61000                  788                  0
12981            559                  58000                  787                  0
12980            559                  55000                  786                  0
12979            559                  52000                  785                  0
12978            559                  49000                  784                  0
12977            559                  46000                  783                  0
12976            559                  43000                  782                  0

I want another column that will contain the subtraction of two quantities .

DESIRED RESULT SHOULD BE SOMETHING LIKE THIS

PK_GUEST_ITEM_ID FK_GUEST_ID          QUANTITY     Result          TRANSACTION_ID       IS_ACTIVE
---------------- -------------------- ---------------------- -------------------- -----------
12963            559                  82000       3000           795                  1
12988            559                  79000       3000           794                  0
12987            559                  76000       3000           793                  0
12986            559                  73000       3000           792                  0
12985            559                  70000       3000           791                  0
12984            559                  67000       3000           790                  0
12983            559                  64000       3000           789                  0
12982            559                  61000       3000           788                  0
12981            559                  58000       3000           787                  0
12980            559                  55000       3000           786                  0
12979            559                  52000       3000           785                  0
12978            559                  49000       3000           784                  0
12977            559                  46000       3000           783                  0
12976            559                  43000       NULL           782                  0

Answer 1

Try something like this:

SELECT 
    v1.*,
    v1.QUANTITY - v2.QUANTITY AS result
FROM 
    v AS v1 
    -- always join transaction (some will be NULL in result)
    LEFT JOIN
        v AS v2
    ON
        -- join to lower trans id
        v2.PK_TRANSACTION_ID = v1.PK_TRANSACTION_ID-1
WHERE
    -- get only odd trans ids
    v1.PK_TRANSACTION_ID % 2 = 1
ORDER BY 
    is_active DESC, 
    transaction_id DESC

But i am afraid it won't be deamon of speed :(.

Answer 2

select  order2.Quantity-order1.Quantity,order1.fk_guestId, ...from v order1 
join v order2 on order1.Transaction_id=order2.transaction_id+1
 by is_active desc, transaction_id desc

Answer 3

to get the next lower transaction id you can use a subquery

Select max(transactionid) 
from vinner 
where vinner.tr.ansactionid <vouter.transactionid

This works fine for me:

select v1.transactionid as HigherTransactionID
      ,v2.transactionid as LowerTransactionId
  ,v1.quantity as HigherQuan
  ,v2.quantity as LowerQuan
      ,v1.quantity - v2.quantity as Result
from v  as v1
     left join v as v2 on
v2.transactionid = 
            (Select MAX(v.transactionid) 
             from v 
              where v.transactionid < v1.transactionid)

Tested with following Table:

quantity    transactionid
8200            795
7900            794
6600            793
6300            792
6000            788
5700            787
4300            786

With following result:

HigherTransactionID LowerTransactionId  HigherQuan  LowerQuan   Result
795                 794                 8200            7900            300
794                 793                 7900            6600            1300
793                 792                     6600            6300            300
792                 788                     6300            6000            300
788                 787                     6000            5700            300
787                 786                     5700            4300            1400
786                 NULL                    4300            NULL            NULL

Hope this is what you expected