decimal - Get daily average for specific date range SQL Server -


thanks in advance!!

here have far:

i selecting total amount of tasks person specific owner given date range. trying exact daily average number of tasks each person.

the problem having keeps returning .00 on end of average, rather exact average. example, person may have 2 tasks in week; getting 0.00 average rather 0.28 or 0.29

select     convert(nvarchar, count(p.personid)) count,    convert(decimal(4, 2), count(p.personid) / datediff(day, @startdate, @enddate)) average,    p.personid,    p.firstname,    p.lastname,    c.companyname       tasks t join     person p on p.personid = t.personid join     client c on c.id = p.employer join    commission m on m.clientid = c.id      t.created between @startdate , @enddate    , m.owner in ('john doe') group     p.personid, p.firstname, p.lastname, c.companyname order     c.companyname, count desc 

be aware if enddate , startdate identical division 0 error.

your problem count(p.personid) , datediff(day,@startdate,@enddate) both return integers. when dividing 2 integers third integer rounded down. multiplying 1.0 decimal value.

convert(decimal(4,2),1.0*count(p.personid)/datediff(day,@startdate,@enddate)) 

Comments

Popular posts from this blog

image - ClassNotFoundException when add a prebuilt apk into system.img in android -

I need to import mysql 5.1 to 5.5? -

Java, Hibernate, MySQL - store UTC date-time -