quote:

---

Originally posted by Keeter

Need 2 decimals places for inpections_per_day in the following query:

select InspectorID, min(inspectionDate)as First_Inspection_Date, max(inspectionDate) as last_inspection_date,DATEDIFF( day, min(inspectionDate), max(inspectionDate)) as days_between,
count(*) number_of_inspections, (count(*) / DATEDIFF( day, min(inspectionDate), max(inspectionDate))) as inspection_per_day
from inspections
where closedDate is not NULL
and memo not like 'Fire%'
group by InspectorId
order by last_inspection_date


Result set =

44 2006-12-05 2006-12-14 9 15 1 I want 1.67 15 divided by 9 Not 1
11 2007-01-25 2007-01-26 1 4 4
15 2006-12-08 2007-05-08 151 163 1
70 2007-01-02 2007-06-27 176 74 0 I want 0.42, not 0

I tried CAST and ROUND functions but can't get it to work. Please Help Keith Anderson

---

quote:

---

Originally posted by vijayisonly

select InspectorID
, min(inspectionDate)as First_Inspection_Date
, max(inspectionDate) as last_inspection_date
, DATEDIFF( day, min(inspectionDate), max(inspectionDate)) as days_between
, count(*) number_of_inspections
, cast ((count(*) * 1.0E / DATEDIFF( day, min(inspectionDate), max(inspectionDate))) as decimal(10,2)) as inspection_per_day
from inspections 
where closedDate is not NULL 
and memo not like 'Fire%'
group by InspectorId 
order by last_inspection_date

---