Average by day, month

[Palmyra]

I need to know how many clients were opened per year for a range of years and also the average number per day and month per year:

SELECT datepart(yy, clopendt) as 'Year', count(*) as 'Clients Opened',
count(*)/365 as 'Average Clients Opened per Day',
count(*)/12 as 'Average Clients Opened per Month'
FROM client
where clopendt >='1/1/2003' and clopendt <= '12/31/2009'
GROUP BY datepart(yy, clopendt)
WITH ROLLUP

The results don't seem to be exactly right, especially the days seem off:

Year Clients Average Average
Opened per Day per Month
2003 3989 10 332
2004 3163 8 263
2005 3646 9 303
2006 2712 7 226
2007 3244 8 270
2008 3189 8 265
2009 3049 8 254

and is there a way to account for weekends and ten holidays per year?

Any help appreciated.

Thanks.

 

[Bobbber]

Hi,

I'm not sure about the weekends or holidays, I'd just call the 365 days, 365-10-(52*2)=251 days.

Have you thought about integer divisions?

"select 10/365" = 0

whereas :

"select 10/365.00" = .027397

By using 365.00 instead of 365, you'll force SQL to take into account fractions.

Rob

 

[Bobbber]

In the case of the top line :

Year Clients Average Average
Opened per Day per Month
2003 3989 10 332

If you do "select 3989/365" is rounds it to 10... but :

"select 3989/10.00" shows the correct figure of 10.928767 (i.e. almost 11)

R