How to count rows within a given date range?-CodePudding

I've tried to use this code to count rows & also to group the dates by count over the last 2 days but its not working either

select EventDateTime, count(EventDateTime)
from \[dbo\].\[AttributionFact\]
where EventDateTime \>= '2022-12-29' and
EventDateTime \< '2021-12-28'
group by EventDateTime
order by EventDateTime;

CodePudding user response：

There are a few issues with the code you provided:

You have used the wrong operator for comparison in the WHERE clause. The >= and < operators are for comparing values that are greater than or less than, respectively. In this case, you want to use the BETWEEN operator to specify a range of values.

The dates in the WHERE clause are also incorrect. You are trying to get rows with a EventDateTime between '2022-12-29' and '2021-12-28', but these dates are in the wrong order. The BETWEEN operator requires the lower bound to come before the upper bound.

In the SELECT clause, you are using count(EventDateTime) to count the number of rows. However, this will always return the total number of rows in the table, because COUNT counts all non-NULL values. If you want to count the number of rows for each distinct value of EventDateTime, you should use COUNT(*) instead.

Here's how the corrected query should look:

SELECT CONVERT(DATE,EventDateTime), COUNT(*)
FROM [dbo].[AttributionFact]
WHERE EventDateTime BETWEEN '2021-12-28' AND '2022-12-29'
GROUP BY CONVERT(DATE,EventDateTime)
ORDER BY CONVERT(DATE,EventDateTime);

This will select the EventDateTime column and the count of rows for each distinct value of EventDateTime, and return the results sorted by EventDateTime.