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.
| Author |
Topic |
|
Trininole
Yak Posting Veteran
83 Posts |
 Posted - 2011-04-28 : 14:45:36
|
| what would the syntax be on getting counts per day in a three month frame?for example the dates from January 1st 2011 to April 27th 2011 for each day in between?Roger DeFour |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-04-28 : 14:52:10
|
| group by the date and count(*) for the value or sum depending on your data.If you may have days with no data the you will have to derive a table with the days then left join to that.In this case you can't use count(*) - you could use count(col) as that does not count nulls but I prefer sum(case when col is not null then 1 else 0 end)==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-04-28 : 14:54:11
|
| [code]select yourDateColumn, count(*)from yourTablewhere yourDateColumn between '20110101' and '20110427'group by yourDateColumn[/code]You may need to make adjustment to the end date depending on the data type you use for storing the date (date, datetime etc.)This assumes you have data for every intervening day - if you don't, you will not see those dates in the output. If you do want those rows to show, you will need to use a calendar table, which.... |
|
|
|
|
|
|
counts per day by date