Calculation in Query returns 0

Transact-SQL
1

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
2

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
3

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

Thank you so much Scott!

4

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