SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Select query to get count by daily for one month
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

cplusplus
Aged Yak Warrior

534 Posts

Posted - 11/09/2012 :  12:01:50  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/09/2012 :  12:05:25  Show Profile  Reply with Quote
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);
Go to Top of Page

cplusplus
Aged Yak Warrior

534 Posts

Posted - 11/09/2012 :  12:13:45  Show Profile  Reply with Quote
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.
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/09/2012 :  12:23:29  Show Profile  Reply with Quote
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;
Go to Top of Page

cplusplus
Aged Yak Warrior

534 Posts

Posted - 11/09/2012 :  13:55:39  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000