Calculation in Query returns 0

Hello, I am having an issue with the below query, spefically with:

",CAST(((CustFrequency * CustDuration + CustFrequency*15)/12/60) AS decimal(10,5)) AS [Sum of Hours]"

This returns a value of 0, instead of the calculation above. I am a novice with SQL and any help, with specific steps, or a modified query is greatly appreciated.

Thank you!

SELECT	ID
		,Customer_ID
		,Customer_ID_S
		,Customer_ID_S+'|'+TerritoryID AS Customer_ID_S_Territory
		,Store
		,CustomerName2
		,CustomerAddress1
		,CustomerAddress2
		,CustomerCity
		,CustomerZipCode
--		,CustomerCountry
		,Prov
		,CustomerPhone
		,CustomerMobile 
		,CustomerFax
		,CustomerEmail
		,CusLatitude 
		,CusLongitude
		,CustomerCL1
		,Channel
		,Banner
		,[Regional Banner]
		,VisitFrequency
		,TerritoryID
		,CustSTTReserved
		,CAST(CustDuration as int) as Duration
--		,CustSTNReserved
		,CAST(CustFrequency as int) as Frequency
--		,CustSTNReserved2
		,SalesTeam_ID_fromCust
		,IsDistributor
		,CAST(((CustFrequency*CustDuration+CustFrequency*15)/12/60) AS decimal(10,5)) AS [Sum of Hours]
		,[Full Address]
		,'Canada' AS CustomerCountry
FROM	(	SELECT	ID
					,cust1.Customer_ID
					,cust1.Customer_ID_S
					,Store
					,CustomerName2
					,CustomerAddress1
					,CustomerAddress2
					,CustomerCity
					,CustomerZipCode
					,CustomerCountry
					,Prov
					,CustomerPhone
					,CustomerMobile 
					,CustomerFax
					,CustomerEmail
					,CusLatitude 
					,CusLongitude
					,CustomerCL1
					,Channel
					,Banner
					,[Regional Banner]
					,VisitFrequency
					,terr.Territory_ID AS TerritoryID		--2020-04-22: pulling Territory ID from Customer to Territory table instead
					,CustSTTReserved
					,CustDuration
--					,CustSTNReserved
					,CustFrequency
--					,CustSTNReserved2
					,SalesTeam_ID_fromCust
					,IsDistributor
					,[Full Address]
FROM					[CA_PowerBI].[dbo].[v_AFS_Customers]			cust1					
			LEFT JOIN	[CA_PowerBI].[dbo].[v_AFS_CustomerToTerritory]	terr
			ON			cust1.Customer_ID_S=terr.Customer_ID_S			) cust2	

First thing is to try changing the "15" to "15.0":

",CAST(((CustFrequency * CustDuration + CustFrequency*15.0)/12/60) AS decimal(10,5)) AS [Sum of Hours]"

1 Like

I can honestly say that I didn't expect that to work, but it did.

Thank you so much Scott!

No problem.

What happens is that if all the numbers are ints, SQL does pure int division, so that 9/10 = 0.

When you make any of them a decimal, SQL "upgrades" all the values to decimal and does "normal" division, so 9.0/10 = 0.9

3 Likes