I have a database that keeps track of service calls for my company. I am trying to create a query that will show how many times a technician has had to reboot the machine in order to solve the problem and then divide that by the number of machines at the customer site. Every service call has several solutions code fields, so that the technician can track everything he / she did on the call. I have been able to count each time the solution code for rebooting is used, and I can count the number of machines at the site. My problem occurs when I try to divide those two numbers. No matter what I do, I only get whole numbers, and I know I should be getting decimals. Below is my query, could you tell me what I have done wrong? Any assistance you can provide would be greatly appreciated.
Code:
SELECT CUSTOMER_NAME, Reboots, [Base Units], Sum(Reboots\[Base Units]) AS [Reboots per Unit]
FROM
(SELECT tblCustomerData.CUSTOMER_NAME,Count(Switch([SOLUTN_CODE_1]='DDTTAA',1))+Count(Switch([SOLUTN_CODE_2]='DDTTAA',1))+Count(Switch([SOLUTN_CODE_3]='DDTTAA',1))+Count(Switch([SOLUTN_CODE_4]='DDTTAA',1))+Count(Switch([SOLUTN_CODE_5]='DDTTAA',1))+Count(Switch([SOLUTN_CODE_6]='DDTTAA',1)) AS Reboots,
Count(Switch([BASE_UNIT]='Y',1)) AS [Base Units]
FROM (tblCustomerData INNER JOIN tblCallHistory ON tblCustomerData.CUST_NUM = tblCallHistory.SITE) INNER JOIN tblEquipmentData ON tblCustomerData.EQUIP_KEY = tblEquipmentData.EQUIP_KEY
WHERE (((tblCallHistory.COMPLT_DATE) Is Not Null And (tblCallHistory.COMPLT_DATE) Between (DateSerial(Year(Date())-1,Month(Date()),1)) And (DateSerial(Year(Date()),Month(Date()),0))))
GROUP BY tblCustomerData.CUSTOMER_NAME) AS [Total_Reboots]
WHERE Reboots >0 and [Base Units] >0
GROUP BY CUSTOMER_NAME, Reboots, [Base Units];
