CODEWITHSUNDEEP
sql
user1128912
user1128912

Reputation: 191

SQL query filter NULL and current month

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

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.

Upvotes: 0

Views: 443

Answers (3)

Arjun
Arjun

Reputation: 31

SELECT        IDProcess, Fee, DtSubmission 
FROM          dbo.Process
WHERE        
(IDProcessState = '7' and month(DtSubmission ) = MONTH(getdate()))
or IDProcessState in ('5','6')

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269623

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.

Upvotes: 1

Dev
Dev

Reputation: 367

You can get output by modify where condition only as -

SELECT        IDProcess, Fee, DtSubmission
FROM            dbo.Process
WHERE IDProcessState in (5,6) OR (IDProcessState == 7 AND MONTH(DtSubmission) = MONTH(getdate()))

I hope it will works for you.

Upvotes: 0

Related Questions