i have 2 table lot , lot_transactions
lot
lotid | salesorderdocline | ------------------------------------------------- 72266414 | 15 | 72266415 | 0 | 72266416 | 0 | lot_transactions
lotid | salesorderdocline | trstypeid | trsdate ---------------------------------------------------------------------------------------- 72266414 | 0 | 400 | 19-05-2016 72266414 | 11 | 1104 | 20-05-2016 72266414 | 11 | 132 | 24-05-2016 72266414 | 0 | 133 | 25-05-2016 72266414 | 15 | 1104 | 30-05-2016 72266415 | 0 | 400 | 04-02-2016 72266416 | 0 | 400 | 03-02-2016 72266416 | 10 | 1104 | 05-02-2016 72266416 | 10 | 400 | 06-02-2016 72266416 | 10 | 132 | 08-02-2016 72266416 | 0 | 133 | 09-02-2016 output
lotid | stockstatus ---------------------------------------- 72266414 | salesorder 72266415 | instock 72266416 | dropout criteria getting salesorder,instock , dropout 72266414 in salesorder because if check salesorderdocline has changed 0 - 11 -0 - 15 72266415 in instock because salesorderdocline 0 72266416 in dropout because salesorderdocline has changes 0-10-0.
if salesorderdocline =0 stock. if greater 0 in salesorder lot_transactions table can see transactions particular lot lot table show current status
and using mssql database
select l.lotid, stockstatus = case when l.salesorderdocline > 0 'salesorder' when l.salesorderdocline = 0 , not exists ( select * lot_transactions x x.lotid = l.lotid , x.salesorderdocline > 0 ) 'instock' else 'dropout' end lot l 