Q:

Query for differentiate the records in stock,salesorder,dropout

I have 2 table lot and 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 for getting salesorder,Instock and Dropout
72266414 is in salesorder because if we check the salesorderdocline it has changed from 0 - 11 -0 - 15
72266415 is in Instock because salesorderdocline is 0
72266416 is in Dropout because salesorderdocline has changes from 0-10-0.
if the salesorderdocline =0 it is stock.
if greater than 0 than in salesorder
lot_transactions is the table where we can see all the transactions for a particular lot
lot is the table where it will only show the current status
And I am using MSSQL database

A:

SELECT l.lotID,
       Stockstatus = case when l.salesorderdocline > 0
                          then 'SalesOrder'
                          when l.salesorderdocline = 0
                          and  not exists 
                               (
                                   select *
                                   from   lot_transactions x
                                   where  x.lotID = l.lotID
                                   and    x.salesorderdocline > 0
                               )
                          then 'Instock'
                          else 'Dropout'
                          end
FROM   lot l

