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 |
|
edwardborner
Starting Member
1 Post |
 Posted - 2010-05-10 : 09:08:57
|
| Hi Guys,I have a table with the following fields:* Date* ValueI need to write a select which returns the count of value per day of a particular month. Where a day contains no records it should be defaulted to 0. So the output would be something like this:Day Count----------------1 1232 03 2345... And so on until the last day of the month.I had a suggestion to create a table which stores the days of the month and join on that. But that would suggest that each month had the same number of days.Any help with this would be greatly appreciated.Thanks! |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2010-05-10 : 09:52:49
|
| Try something like thisselect day(t1.dates),count(t2.date_col) from(select dateadd(day,number,dateadd(month,datediff(month,0,getdate())-1,0)) as dates from master..spt_valueswhere type='p' and dateadd(day,number,dateadd(month,datediff(month,0,getdate())-1,0))<dateadd(month,datediff(month,0,getdate()),0)) as t1left join your_table as t2on t2.date_col>=dateadd(month,datediff(month,0,getdate())-1,0) andt2.date_col<dateadd(month,datediff(month,0,getdate()),0)MadhivananFailing to plan is Planning to fail |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
|
|
|
|
|
Record Per Day in Select