0

For our mortgage loans, we keep track of 'Lock Date'. I want to write an expression that counts the number of Lock Dates that occurred in the Previous Month (we have a token in my sql system for previous month start, previous month end, or just previous month name)

This is as far as I've gotten, but I keep getting an error:

COUNT(LockDate)
CASE WHEN LockDate BETWEEN @prevmonthstart AND @prevmonthend
Tab Alleman
  • 31,483
  • 7
  • 36
  • 52
Josh Briggs
  • 27
  • 5
  • Possible duplicate of [Sql Server equivalent of a COUNTIF aggregate function](https://stackoverflow.com/questions/582637/sql-server-equivalent-of-a-countif-aggregate-function) – Tab Alleman Feb 27 '19 at 20:41

2 Answers2

2

Change your expression to this:

COUNT(CASE WHEN LockDate BETWEEN @prevmonthstart AND @prevmonthend THEN LockDate ELSE NULL END)

and you should get the result you are looking for. Another way to accomplish the same thing is to use SUM with a conditional expression:

SUM(CASE WHEN LockDate BETWEEN @prevmonthstart AND @prevmonthend THEN 1 ELSE 0 END)

which might be easier to read.

jpw
  • 44,361
  • 6
  • 66
  • 86
0

Possibly a perform-ant option would be to put this condition in the where clause and simply do a COUNT(*) in the SELECT.

SELECT COUNT(*)
FROM Table
WHERE LockDate BETWEEN @prevmonthstart AND @prevmonthend
sam yi
  • 4,806
  • 1
  • 29
  • 40