1

I've got a problem with my T-SQL query. There is one table where I want to count everything but when canceldate (it's a column with dates) is null then @currentdate (that is a parameter) should be between startdate and enddate. Else @currentdate should be between startdate and canceldate.

Here is my code but still i don't know how should i build it.

declare @currentdate as date

select count(*) from Table 
where
  case when Canceldate is null then 
    @currentdate between startdate and enddate
  else 
    @currentdate between startdate and canceldate
group by personalnum
GSerg
  • 76,472
  • 17
  • 159
  • 346
ninjaloot777
  • 19
  • 7
  • Dr. Linoff has provided the correct answer. It is possible to use a `case` expression in a `where` (or `on`) clause as shown [here](https://stackoverflow.com/a/10260297/92546), but the performance is likely to suffer considerably. – HABO Jun 15 '21 at 12:24

1 Answers1

3

You don't need case; coalesce() does exactly what you want:

select count(*)
from Table 
where @currentdate between startdate and coalesce(canceldate, enddate)
group by personalnum;

The statement of your problem does not mention a need for group by, but that is part of your code.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786