Select query to get count by daily for one month

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Select query to get count by daily for one month

Author

Topic

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2012-11-09 : 12:01:50

I want to get a count on daily basis, based on each day between date range, is it possible:

select count(*) from Table_cleanse WHERE reject_date
BETWEEN cast('10/01/2012'  as datetime)
AND cast('10/31/2012' as datetime)

Thanks a for the helpful info

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-09 : 12:05:25

The following query should work if your reject_date does not have a time portion associated with it

SELECT reject_date,COUNT(*) AS DailyRejects
FROM   Table_cleanse
WHERE  reject_date
       BETWEEN CAST('10/01/2012' AS DATETIME)
       AND CAST('10/31/2012' AS DATETIME)
GROUP BY reject_date
ORDER BY reject_date;

If it does have a time portion, change the query to this

SELECT 
	DATEADD(dd,DATEDIFF(dd,0,reject_date),0) AS reject_date,
	COUNT(*) AS DailyRejects
FROM   Table_cleanse
WHERE  reject_date
       BETWEEN CAST('10/01/2012' AS DATETIME)
       AND CAST('10/31/2012' AS DATETIME)
GROUP BY DATEADD(dd,DATEDIFF(dd,0,reject_date),0)
ORDER BY DATEADD(dd,DATEDIFF(dd,0,reject_date),0);

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2012-11-09 : 12:13:45

Sunita Thanks.

I am getting rows but all has same reject date.

It is showing Oct 3rd as date for all rows. for reject date column.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-09 : 12:23:29

Do you have data for other dates in the table? The example below seems to work as I would expect it to. Depending on what you want to do, you may need to use the first or the second. You can copy this query to a SSMS query window and run it to see what it does.

CREATE TABLE #Table_cleanse(reject_date DATETIME, number_of_rejects INT);
INSERT INTO #Table_cleanse VALUES
('20121001',11),
('20121001',17),
('20121002',9),
('20121003',2),
('20121003',5);

--- 1
SELECT
	reject_date,
	COUNT(*) AS DailyRejects
FROM
	#Table_cleanse
GROUP BY 
	reject_date
ORDER BY
	reject_date;

-- 2
SELECT
	reject_date,
	SUM(number_of_rejects) AS DailyRejects
FROM
	#Table_cleanse
GROUP BY 
	reject_date
ORDER BY
	reject_date;
	
DROP TABLE #Table_cleanse;

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2012-11-09 : 13:55:39

Sunita,

Thks a lot i only have 1 single set of dates for all records. thats why i am seeeing just one date.

Thank you its working.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources