CODEWITHSUNDEEP
sqlsql-server
Excited_to_learn
Excited_to_learn

Reputation: 359

Suggestion for a better WHERE condition

I have a below query:

SELECT AppInitiatedDate,
       Incomplete.Product1,
       Incomplete.Product2, 
       Incomplete.Product3, 
       Incomplete.Product4, 
       Incomplete.Product5, 
       CASE WHEN 
         len(Incomplete.Product1) > 0 or
         len(Incomplete.Product2) > 0 or
         len(Incomplete.Product3) > 0 or
         len(Incomplete.Product4) > 0 or
         len(Incomplete.Product5) > 0 THEN 1 ELSE 0 
       END AS NumberIncomplete,
       CASE WHEN AppInitiatedDate >= @StartDate AND AppInitiatedDate < @EndDate 
            THEN 1 
            ELSE 0 
       END AS NumberInitiated
FROM [TableNames]
WHERE AppInitiatedDate >= @StartDate) 
  AND AppInitiatedDate < @EndDate) 
  AND  **(DecisionStatus.Status in ('Active', 'Inactive', 'Complete'
         , 'Cancelled', 'WithDrawn', 'CounterOffer') 
   OR (DecisionStatus.Status = 'Cancelled' 
       AND DecisionStatus.Status IS NOT NULL)** 
--DecisionStatus table inclues fields like 
--Active, Inactive, Complete, Cancelled, WithDrawn, CounterOffer etc. ALso

The Scenario is: I want all the records of Incomplete Product Application with the all Status included in DecisionStatus table but don't want Cancelled records where all the records are null. Please suggest if I can imporve the above query.

Upvotes: 2

Views: 67

Answers (1)

Reza
Reza

Reputation: 19863

I don't know if I get your point correctly, but try this

WHERE AppInitiatedDate >= @StartDate) AND AppInitiatedDate < @EndDate) AND IsNull(DecisionStatus.Status,'Cancelled') <> 'Cancelled'

Upvotes: 1

Related Questions