SQL View Script Update

GingaNinga

New Member
Joined
Sep 1, 2017
Messages
26
Office Version
  1. 365
Platform
  1. 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.


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
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Simplified version:
Looking to understand why the calculation ",CAST((CustFrequency*CustDuration+CustFrequency*15)/12/60 AS decimal(10,5)) AS [Sum of Hours]" in the below query does not return expected values, it is returning 0

SQL:
SELECT    ID
        ,Cust1.Customer_ID
        ,Cust1.Customer_ID_S
        ,Cust1.Customer_ID_S+'|'+Terr.Territory_ID 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
        ,terr.Territory_ID
        ,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]
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
 
Upvote 0

Forum statistics

Threads
1,215,281
Messages
6,124,043
Members
449,139
Latest member
sramesh1024

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top