Rows with the same date, count date only once

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

General SQL Server Forums

New to SQL Server Programming

Rows with the same date, count date only once

Author

Topic

newToSqI
Starting Member

2 Posts

Posted - 2011-01-17 : 14:31:23

Hi community,

I'm trying to run a script that will count all rows for a certain period of time in example below only January submissions. However, some of those rows have the same date and I want to have the latest time row of that day in the count. Could anyone give me a hand with this?

Thanks,

Oracle 10g

sample 'submit' table

pk submit_time
1 1/17/2011 08:05:23 AM
2 1/17/2011 07:59:53 AM
3 1/14/2011 05:25:35 PM
4 1/05/2011 01:06:12 PM
5 12/18/2010 4:34:12 PM

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-01-17 : 14:40:11

DECLARE @Table TABLE (pk int,submit_time datetime)

INSERT INTO @Table
SELECT 1 ,'1/17/2011 08:05:23 AM' UNION ALL
SELECT 2,'1/17/2011 07:59:53 AM' UNION ALL
SELECT 3,'1/14/2011 05:25:35 PM' UNION ALL
SELECT 4,'1/05/2011 01:06:12 PM' UNION ALL
SELECT 5,'2/18/2010 4:34:12 PM'

SELECT count (distinct datepart(day,submit_time))
FROM @table
WHERE submit_time >= '20110101' and submit_time < '20110201'

Jim

Everyday I learn something that somebody else already knew

newToSqI
Starting Member

2 Posts

Posted - 2011-01-17 : 15:23:44

Hi,

Thanks for the quick reply, wow.

so use a temporary table basically and use the distinct statement to pull the 'one' i want to use in my count.

this is easy enough for a handful of submissions. Any idea on how to make something like this when having to check tens of thousands of records?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-01-17 : 15:31:34

I only used a table variable so I'd have data to work with, you can replace @Table in my query with 'submit' for yours.

Jim

Everyday I learn something that somebody else already knew

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-01-18 : 08:47:58

If you use ORACLE, you need to post the question at ORACLE forums such as www.orafaq.com

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources