CODEWITHSUNDEEP
sqlt-sqldynamic-sql
Jared
Jared

Reputation: 416

Selecting Count of Records in a Date Range SQL

I'm looking to select the count of comments from a table for each day provided. For example I want to see the count of comments for each day between 11-20-2016 and 11-19-2016.

I've explored this idea for awhile and this is just my basic code I wrote to get a single day, but I am looking to do this dynamically in a date range.

DECLARE @Date VARCHAR(250)
SET @Date = '2016-11-23'

SELECT      
    COUNT(*),
    @Date
FROM CRA_METRO2_REJECT_COMMENTS RC
WHERE 
    CONVERT(DATE, RC.comment_date) IN (CONVERT(DATE, @Date))

Could someone point me in the right direction?

Upvotes: 0

Views: 37

Answers (2)

Jared
Jared

Reputation: 416

With BWS's help here is my solution.

DECLARE @StartDate VARCHAR(250)
DECLARE @EndDate VARCHAR(250)
SET @StartDate = '2016-11-23'
SET @EndDate = '2016-11-29'

SELECT 
    CONVERT(DATE, RC.comment_date),
    COUNT(*)
FROM CRA_METRO2_REJECT_COMMENTS RC
WHERE CONVERT(DATE, RC.comment_date) BETWEEN CONVERT(DATE, @StartDate) AND CONVERT(DATE, @EndDate)
GROUP BY CONVERT(DATE, RC.comment_date)
ORDER BY CONVERT(DATE, RC.comment_date)

Upvotes: 1

BWS
BWS

Reputation: 3836

You should try a simpler approach:

SELECT RC.comment_date, COUNT(*)
FROM CRA_METRO2_REJECT_COMMENTS RC
GROUP BY RC.comment_date
ORDER BY RC.comment_date

This should work for whatever format your comment_date uses.

Upvotes: 1

Related Questions