Totals by Date - SQL Server Forums

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

Totals by Date

Author

Topic

youruseridistoxic
Yak Posting Veteran

65 Posts

Posted - 2009-05-07 : 10:06:17

I am trying to determine the number of unique records that appear for each date. I suspect my results are skewed because of the hour:minute:second info included in the transdate column, so any suggestions on a way around this would be greatly appreciated!

Sample dataset from table1

UID     TransType     TransDate            Quantity     UserID     
001     1             2008-10-22 17:40:06  27           0084
002     1             2008-10-22 18:24:41  1            1195
003     1             2008-10-23 13:10:42  13           1598
004     1             2008-10-23 13:10:42  6            1598

Expected output would look like this;

TransDate     Unique Scans
2008-10-22     2
2008-10-23     2

Thank you so much to all who respond :)

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-05-07 : 10:11:21

[code]select TransDate=DATEADD(DAY,DATEDIFF(DAY,0,TransDate),0),
[Unique Scans]=count(UID)
from
table1
group by
DATEADD(DAY,DATEDIFF(DAY,0,TransDate),0)[/code]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-05-07 : 10:13:27

select dateadd(day,datediff(day,0,TransDate),0) as TransDate,count(*) from table1
Group by dateadd(day,datediff(day,0,TransDate),0)

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-05-07 : 10:13:41

Madhivanan

Failing to plan is Planning to fail

youruseridistoxic
Yak Posting Veteran

65 Posts

Posted - 2009-05-07 : 10:30:47

Thanks to you both! This is exactly what I needed.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources