Date and Time column - count and group by day

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

Date and Time column - count and group by day

Author

Topic

Stan1978
Starting Member

31 Posts

Posted - 2011-07-20 : 10:28:08

Thanks for all the previous help and reading this question.

I have a table structured;

TransID-----AccID-----DateTime
1-----------111-------2011-01-01 12:01:00:000
2-----------200-------2011-03-15 01:00:15:000
3-----------111-------2011-04-05 14:25:43:001
4-----------200-------2011-04-05 14:25:43:001

I would like to return unique AccIDs per day.

Day------------Unique AccID
2011-01-01-----1
2011-03-15-----1
2011-04-05-----2

Thank you for your help.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-07-20 : 10:40:48

select
dateadd(d,datediff(d,0,DateTimeColumn),0) as Day,
count(distinct AccID) as UniqueAccID
from YourTable
group by dateadd(d,datediff(d,0,DateTimeColumn),0)

No, you're never too old to Yak'n'Roll if you're too young to die.

Stan1978
Starting Member

31 Posts

Posted - 2011-07-20 : 10:58:22

Thank you very much for the response, you have saved me working all night and it works quickly unlike some of my queries.

One more question when the DateTime column in the format 2011-01-01 12:01:00:000 is replaced by a string e.g. 2011010182344253328

How do I perform the same query?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-07-20 : 11:08:16

Then:
select
left(DateTimeColumn,8) as Day,
count(distinct AccID) as UniqueAccID
from YourTable
group by left(DateTimeColumn,8)

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources