GingaNinga
New Member
- Joined
- Sep 1, 2017
- Messages
- 26
- Office Version
- 365
- Platform
- Windows
Hello - I am in need of help to update a SQL View Script.
Basically, the condition in bold below is no longer required, and it is returning a value of 0, where I would expect a value greater than 0, based on what is highlighted in red.
I have tried to change the "WHEN" clause to point to the CustFrequency, and CustDuration fields respectively, however when I do that, my Sum of Hours still returns 0, for all (not just where the condition matches FT
I have also tried to comment out the entire "CASE" clause in bold - but Sum of Hours still returned 0.
Any help is greatly appreciated.
Basically, the condition in bold below is no longer required, and it is returning a value of 0, where I would expect a value greater than 0, based on what is highlighted in red.
I have tried to change the "WHEN" clause to point to the CustFrequency, and CustDuration fields respectively, however when I do that, my Sum of Hours still returns 0, for all (not just where the condition matches FT
I have also tried to comment out the entire "CASE" clause in bold - but Sum of Hours still returned 0.
Any help is greatly appreciated.
SQL:
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
,CustDuration
,CustSTNReserved
,CustFrequency
,CustSTNReserved2
,SalesTeam_ID_fromCust
,IsDistributor
,CAST(Duration as int) AS Duration
,CAST(Frequency as int) AS Frequency
[COLOR=rgb(209, 72, 65)] ,CAST((Frequency*Duration + Frequency*15)/12/60 AS decimal(10,5)) AS [Sum of Hours][/COLOR]
,[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]
[B] ,CASE WHEN LEFT(terr.Territory_ID,1) = 'C' AND RIGHT(terr.Territory_ID,2)='FT'
THEN CustSTNReserved
ELSE CustDuration
END AS Duration
,CASE WHEN LEFT(terr.Territory_ID,1) = 'C' AND RIGHT(terr.Territory_ID,2)='FT'
THEN CustSTNReserved2
ELSE CustFrequency
END AS Frequency[/B]
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
GO