SQL Conditional/Iif Where clause-CodePudding

I'm working on this query that shows the receivables in 2021-01. Sometimes the matchdate is before the original invoice date, in that case I want a different where clause. This is the desired where clause:

if status = 1 then where originalmatch_period <= 202101 else
where yearperiod <= 202101 and originalinvoice_period <= 202101

Can someone help me to make this work?

CodePudding user response：

Simply put it in the same clause and distinguish the case with OR:

WHERE (status = 1 AND originalmatch_period <= 202101) 
   OR (status <> 1 AND yearperiod <= 202101 and originalinvoice_period <= 202101)

CodePudding user response：

In SQL, there are various approaches like dynamic SQL or apply all possible conditions with OR

Here are sample that may be helpful to build your solution.

OR approach (please refer kutschkem's answer)

Dynamic Query Approach:

DECLARE @SelectQuery NVARCHAR(MAX) = 'SELECT * FROM Table'
DECLARE @WhereClause NVARCHAR(MAX) = ' WHERE '

IF @status = 1 then 
BEGIN 
  SET @WhereClause =  @WhereClause   'originalmatch_period <= 202101'
END
ELSE
BEGIN
  SET @WhereClause = @WhereClause   'yearperiod <= 202101 and originalinvoice_period <= 202101'
END

DECLARE @Query NVARCHAR(MAX) = @SelectQuery    @WhereClause 

EXECUTE sp_executesql @Query

CodePudding user response：

Both of the two answers are correct, if you don't want to use variables just use kutschkem solution, will be easier