Ramesh Ramesh - 1 month ago 6
SQL Question

How to count records by week from specific data frame using SQL Query

Start Date: 10/14/2016
End Date: Statdate - 4 Weeks

I need a single query that does this sequence in SQL

SELECT COUNT(*) FROM TableName
WHERE AddDate between '10/14/2016 10:16:00' and '10/14/2016 10:31:00'

SELECT COUNT(*) FROM TableName
WHERE AddDate between '10/07/2016 10:16:00' and '10/07/2016 10:31:00'

SELECT COUNT(*) FROM TableName
WHERE AddDate between '09/30/2016 10:16:00' and '09/30/2016 10:31:00'

SELECT COUNT(*) FROM TableName
WHERE AddDate between '09/23/2016 10:16:00' and '09/23/2016 10:31:00'


I would appreciate if someone help on this.

Answer

How about something like this (for MySQL, but similar exists for other DBs)

SELECT 
DATE(AddDate) as TheDate,
COUNT(*) as Records
FROM TableName
WHERE
AddDate BETWEEN '2016-23-09' AND '2016-10-15'
AND DAYOFWEEK(AddDate) = 1 //for SUNDAYS
AND TIME(AddDate) BETWEEN '10:16:00' AND '10:31:00'
GROUP BY DATE(AddDate)
ORDER BY DATE(AddDate) ASC

I haven't tested this, but it should give you something like:

TheDates    Records
--------------------
2016-09-23    120
2016-09-30    157
...etc