DATEADD function not working as expected in CASE expression

Question

I'm trying to build a SQL statement for use in a PowerBI report, that gets the number of times each listed operator performed one of three functions (referred to here as creation (entry_date), data entry (secondary) and double checking (dblentry). A requirement of this statement is that it pulls this data only for last month or the month prior, with the expectation that this is dynamic so no manual changes are required when the report is run in the following month.

Code:

WITH UserCounts AS (
        SELECT U.dbuser_id, U.user_name,
            SUM(CASE WHEN R.primary_user_id = U.dbuser_id AND R.entry_date = DATEADD(MONTH, -1, GETDATE()) THEN 1 ELSE 0 END) AS primary_count,
            SUM(CASE WHEN R.secondary_user_id = U.dbuser_id AND R.secondary_dt = DATEADD(MONTH, -1, GETDATE()) THEN 1 ELSE 0 END) AS secondary_count,
            SUM(CASE WHEN R.dblentry_user_id = U.dbuser_id AND R.dblentry_dt = DATEADD(MONTH, -1, GETDATE()) THEN 1 ELSE 0 END) AS dblentry_count
        FROM valid_users U
        LEFT JOIN requests R
            ON U.dbuser_id IN (R.primary_user_id, R.secondary_user_id, R.dblentry_user_id)
        GROUP BY U.dbuser_id, U.user_name
    )
    SELECT dbuser_id, user_name, primary_count, secondary_count, dblentry_count, 
        primary_count + secondary_count + dblentry_count AS total_count
    FROM UserCounts
    WHERE primary_count > 0 OR secondary_count > 0 OR dblentry_count > 0

With this code, I get no results at all. No errors, just no results. If I change

R.entry_date = DATEADD(MONTH, -1, GETDATE())

to

R.entry_date >= DATEADD(MONTH, -1, GETDATE())

I get results for both last month and the current month - which makes sense based on the query but still not what I need. To combat this I tried:

R.entry_date >= DATEADD(MONTH, -1, GETDATE()) AND R.entry_date < DATEADD(MONTH, 0, GETDATE())

... but this also gives me no results.

The count I'm expecting for the primary_count column should be close to 7510. I also want to get this data for the month prior which in this case should come in close to 5945. I'm seeing 79xx when the primary_count query works, so it's including this months' stats to date as well.

How do I get data specifically only for 1 month ago or 2 months ago, incorporating the entire month? Alot of Google results talk about using the DATEADD function in the SELECT query, which I don't think I can do here. This is how I'm expecting the output to look.

EDIT: Some sample data.

I'm getting the numbers I'm expecting via this query:

use asl;
select count (entry_date) from requests
where entry_date between '2023-05-01' and '2023-05-31'

Answer 1

You want to get data for the last month. This it:

where datetime_column >= '2023-05-01'
  and datetime_column <  '2023-06-01'

or dynamically using today's date:

where datetime_column >= dateadd(day, 1, eomonth(getdate(), -2))
  and datetime_column <  dateadd(day, 1, eomonth(getdate(), -1))

Applied to your query:

WITH UserCounts AS
(
  SELECT u.dbuser_id, u.user_name,
      SUM(CASE WHEN r.primary_user_id = u.dbuser_id
               AND r.entry_date >= DATEADD(DAY, 1, EOMONTH(GETDATE(), -2))
               AND r.entry_date <  DATEADD(DAY, 1, EOMONTH(GETDATE(), -1))
               THEN 1 ELSE 0
          END) AS primary_count,
      SUM(CASE WHEN r.secondary_user_id = u.dbuser_id 
               AND r.secondary_dt >= DATEADD(DAY, 1, EOMONTH(GETDATE(), -2))
               AND r.secondary_dt <  DATEADD(DAY, 1, EOMONTH(GETDATE(), -1))
               THEN 1 ELSE 0
          END) AS secondary_count,
      SUM(CASE WHEN r.dblentry_user_id = u.dbuser_id
                AND r.dblentry_dt >= DATEADD(DAY, 1, EOMONTH(GETDATE(), -2))
                AND r.dblentry_dt <  DATEADD(DAY, 1, EOMONTH(GETDATE(), -1))
                THEN 1 ELSE 0
          END) AS dblentry_count
  FROM valid_users u
  LEFT JOIN requests r
      ON u.dbuser_id IN (r.primary_user_id, r.secondary_user_id, r.dblentry_user_id)
  GROUP BY u.dbuser_id, u.user_name
)
SELECT
  dbuser_id, user_name, primary_count, secondary_count, dblentry_count, 
  primary_count + secondary_count + dblentry_count AS total_count
FROM usercounts
WHERE primary_count > 0 OR secondary_count > 0 OR dblentry_count > 0;