CODEWITHSUNDEEP
sqlsql-servert-sql
Sonali
Sonali

Reputation: 391

How to add a extra column at runtime in SQL Server

My query is 

SELECT  
   RemainingQty, CompletedQty 
FROM 
   tblJobStatus AS JT 
LEFT OUTER JOIN 
   TBLJOBWORKS  AS TJ ON JobWorksId = TaskId 
LEFT OUTER JOIN 
   tblTotalJob AS T ON T.Id = JobID    
WHERE 
   JT.ProductID = '28' 
   AND ItemId = '15' 
ORDER BY 
   JobSequence ASC

When executing the query I get this result

enter image description here

Now I want to add a extra column with this at runtime which will contain status. If the RemainingQty is 0 and CompletedQty is greater than 0 then the status column will contain complete else pending.

Like following picture

enter image description here

Is it possible without a loop?

Upvotes: 1

Views: 2604

Answers (4)

sdf
sdf

Reputation: 189

SELECT  
   RemainingQty, CompletedQty,
   Case when RemainingQty=0 and  CompletedQty>0 then 'Complete' else 'Pending' end as Status
FROM 
   tblJobStatus AS JT 
LEFT OUTER JOIN 
   TBLJOBWORKS  AS TJ ON JobWorksId = TaskId 
LEFT OUTER JOIN 
   tblTotalJob AS T ON T.Id = JobID    
WHERE 
   JT.ProductID = '28' 
   AND ItemId = '15' 
ORDER BY 
   JobSequence ASC

Upvotes: 1

Stanislav Stoyanov
Stanislav Stoyanov

Reputation: 2120

SELECT  
   RemainingQty, CompletedQty,
   case when RamaininQty = and CompletedQty > 0 then 'Complete' else 'Pending' end as Status
FROM 
   tblJobStatus AS JT 
LEFT OUTER JOIN 
   TBLJOBWORKS  AS TJ ON JobWorksId = TaskId 
LEFT OUTER JOIN 
   tblTotalJob AS T ON T.Id = JobID    
WHERE 
   JT.ProductID = '28' 
   AND ItemId = '15' 
ORDER BY 
   JobSequence ASC

Upvotes: 1

roman
roman

Reputation: 117337

select
    ...,
    case
       when RemainingQty = 0 and CompletedQty > 0 then 'Complete'
       else 'Pending'
    end as Status
from ...

Upvotes: 1

marc_s
marc_s

Reputation: 754240

Sure, no problem - that's a really simple CASE expression:

SELECT  
   RemainingQty, CompletedQty,
   Status = CASE
               WHEN RemainingQy = 0 AND CompletedQty > 0
                 THEN 'Complete'
                 ELSE 'Pending'
            END
FROM 
   tblJobStatus AS JT 
LEFT OUTER JOIN 
   TBLJOBWORKS  AS TJ ON JobWorksId = TaskId 
LEFT OUTER JOIN 
   tblTotalJob AS T ON T.Id = JobID    
WHERE 
   JT.ProductID = '28' 
   AND ItemId = '15' 
ORDER BY 
   JobSequence ASC

Upvotes: 2

Related Questions