SQL query filter NULL and current month

I have a SQL query and I would like it to list records that:

• Are in one of these IDProcessState(s): 5, 6 or 7
• If IDProcessState is 7, it should only show the ones that were submitted (DtSubmission) this month
• If IDProcessState is 5 or 6 it should still show the records (they have null DtSubmission column)

The code below shows records with current month submissions (IDProcessState = 7) but excludes the ones that were not yet submitted, and therefore, excludes the ones in IDProcessState 5 or 6 (in these states, the processes were not submitted yet).

SELECT        IDProcess, Fee, DtSubmission
FROM            dbo.Process
WHERE        (IDProcessState IN ('5', '6', '7')) AND (DtSubmission >= DATEADD(month, 
DATEDIFF(month, 0, GETDATE()), 0))

I assume I should use something like “CASE WHEN” but I’m only getting syntax errors

Thanks in advance.

Answer

Based on your syntax, this answer assumes you are using SQL Server.

In SQL Server, you can express this as:

WHERE IDProcessState IN (5, 6) OR
      (IDProcessState = 7 AND
       DATEDIFF(month, DtSubmission, GETDATE()) = 0
      )

Note that this assumes that IDProcessState is a number — it looks like a number.

I might also suggest phrasing this as:

WHERE IDProcessState IN (5, 6, 7) AND
      (IDProcessState <> 7 OR
       DtSubmission >= DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1) 
      )

This version assumes there are no future dates.