1 Reply Latest reply on Aug 24, 2017 11:52 AM by Amber Loranger

    Dashboard data connection taking more time to edit which is using stored procedure

    ravindra babu

      Hi Everyone,

       

      I have a issue raised by a user. user wants to edit the dashboard data connection which is using a stored procedure in My SQL server. it took more than 1 hr to load the data.

      Note: we just created a dashboard. not published in server.

      i had gone through below blogs

       

      Re: stored procedure takes 11 minutes runs in a minute using raw sql

      Using stored procedures and temp tables

      SET NOCOUNT (Transact-SQL) | Microsoft Docs

      https://www.experts-exchange.com/questions/28045667/Using-Tableau-With-SQL-Server-Stored-Procedures.html

      Stored Procedures

       

      i am attaching the create Query for your reference. please help out of this.

      tableau version :10.2

       

       

      USE [DW_PA]

      GO

       

       

      /****** Object:  StoredProcedure [dbo].[TableauHighwayMaster]    Script Date: 8/17/2017 3:37:55 PM ******/

      SET ANSI_NULLS ON

      GO

       

       

      SET QUOTED_IDENTIFIER ON

      GO

       

       

      -- ================================================

       

       

      -- Author: <Author,,Name>

      -- Create date: <Create Date,,>

      -- Description: <Description,,>

      -- =============================================

      CREATE PROCEDURE [dbo].[TableauHighwayMaster]

      -- Add the parameters for the stored procedure here

       

       

      AS

      BEGIN

      -- SET NOCOUNT ON added to prevent extra result sets from

      -- interfering with SELECT statements.

      SET NOCOUNT ON;

       

       

          -- Insert statements for procedure here

      Truncate TABLE dbo.HighwayOverview

       

       

      INSERT INTO dbo.HighwayOverview

      Select distinct

      b.Region

      ,c.HubName

      ,c.HubID

      ,CASE WHEN k.IncludeInCarrierDBFlag is null then 'N' else k.IncludeInCarrierDBFlag end as IncludeInCarrierDBFlag

      , CASE WHEN a.HighwayServiceType  in('LBMN','LDBS','LDBT') THEN 'Y' ELSE 'N' END as 'LoadBoardFlag'

      ,a.OrderNumber

      ,a.LoadNumber

      ,a.OrderStatus

      ,e.*

      ,j.ReportingCustomerNumber

      ,j.ReportingCustomerName

      ,j.CustomerNumber

      ,j.CustomerName

      ,a.LogisticsFlag

      ,a.OriginDispatchHub

      ,q.HubName as OriginDispatchHubName

      ,a.HighwayServiceType 

      ,a.TransportationMode

      ,a.TransportationSubMode

      ,f.JobReferenceID

      ,a.CarrierSource

      ,a.Mileage

      ,d.PickupFromAppointmentDate

      ,d.PickupToAppointmentDate

      ,d.PickupFromRequestDate

      ,d.PickupToRequestDate

      ,d.ArrivePickupLocationDate

       

       

      , Case WHEN  a.OriginState IN ('PA','NJ','MD','CT','ME','MA','DE','NY','NH','RI','WV','VT') THEN 'NORTHEAST'

        WHEN  a.OriginState = 'OH' and a.OriginZoneNumberHighway <> 436 then 'NORTHEAST'

        WHEN  a.OriginState IN ('VA','NC','SC','GA','FL','AL','MS') THEN 'SOUTHEAST'

        WHEN  a.OriginState = 'TN' and  a.OriginZoneNumberHighway <> 381 then 'SOUTHEAST'

        WHEN j.OriginCountryCode = 'CAN' and a.OriginState <> 'ON' THEN 'SOUTHEAST'

        WHEN  a.OriginState = 'KY' and  a.OriginZoneNumberHighway = 421 then 'SOUTHEAST'

        WHEN  a.OriginState IN ('CA','OR','WA','UT','AZ','ID','CO','NM','NV','WY','MT') THEN 'WEST'

        WHEN  a.OriginState IN ('IN','IL','MI','WI','MN','MO','IA','SD','ND') THEN 'CENTRAL NORTH'

        WHEN  a.OriginState = 'OH' and  a.OriginZoneNumberHighway = 436 then 'CENTRAL NORTH'

        WHEN  a.OriginState = 'KY' and  a.OriginZoneNumberHighway <> 421 then 'CENTRAL NORTH'

        WHEN j.OriginCountryCode = 'CAN' and a.OriginState = 'ON' THEN 'CENTRAL NORTH'

        WHEN  a.OriginState IN ('NE','KS','OK','AR','LA','TX') THEN 'CENTRAL SOUTH'

        WHEN  a.OriginState = 'TN' and  a.OriginZoneNumberHighway = 381 then 'CENTRAL SOUTH'

        WHEN j.OriginCountryCode = 'MEX' and a.OriginState IN('BC','BS','BJ','SO') THEN 'WEST'

        WHEN j.OriginCountryCode = 'MEX' and a.OriginState NOT IN('BC','BS','BJ','SO') THEN 'MEXICO'

        ELSE 'ZNot Classified' end as 'OriginZone'

      , Case WHEN  a.DestinationState IN ('PA','NJ','MD','CT','ME','MA','DE','NY','NH','RI','WV','VT') THEN 'NORTHEAST'

        WHEN  a.DestinationState = 'OH' and a.DestinationZoneNumberHighway <> 436 then 'NORTHEAST'

        WHEN  a.DestinationState IN ('VA','NC','SC','GA','FL','AL','MS') THEN 'SOUTHEAST'

        WHEN  a.DestinationState = 'TN' and  a.DestinationZoneNumberHighway <> 381 then 'SOUTHEAST'

        WHEN j.DestinationCountryCode = 'CAN' and a.DestinationState <> 'ON' THEN 'SOUTHEAST'

        WHEN  a.DestinationState = 'KY' and  a.DestinationZoneNumberHighway = 421 then 'SOUTHEAST'

        WHEN  a.DestinationState IN ('CA','OR','WA','UT','AZ','ID','CO','NM','NV','WY','MT') THEN 'WEST'

        WHEN  a.DestinationState IN ('IN','IL','MI','WI','MN','MO','IA','SD','ND') THEN 'CENTRAL NORTH'

        WHEN  a.DestinationState = 'OH' and  a.DestinationZoneNumberHighway = 436 then 'CENTRAL NORTH'

        WHEN  a.DestinationState = 'KY' and  a.DestinationZoneNumberHighway <> 421 then 'CENTRAL NORTH'

        WHEN j.DestinationCountryCode = 'CAN' and a.DestinationState = 'ON' THEN 'CENTRAL NORTH'

        WHEN  a.DestinationState IN ('NE','KS','OK','AR','LA','TX') THEN 'CENTRAL SOUTH'

        WHEN  a.DestinationState = 'TN' and  a.DestinationZoneNumberHighway = 381 then 'CENTRAL SOUTH'

        WHEN j.DestinationCountryCode = 'MEX' and a.DestinationState IN('BC','BS','BJ','SO') THEN 'WEST'

        WHEN j.DestinationCountryCode = 'MEX' and a.DestinationState NOT IN('BC','BS','BJ','SO') THEN 'MEXICO'

        ELSE 'ZNot Classified' end as 'DestinationZone'

       

       

      ,CASE WHEN a.TransportationMode = 'IML' then 'INTERMODAL'

       

       

        WHEN a.HighwayServiceType  in('SPCS','SPCT','EMDR') AND a.LogisticsFlag =0 AND c.HubID not  in(10,24) and j.ReportingCustomerNumber not in(15155,21113,26307,18490,3410) then 'PROJECT'

       

       

        WHEN a.CustomerNumber = 1501 and a.OriginCity in('LANARK','MORTON') then 'PROJECT'

       

       

        WHEN a.LogisticsFlag = 1 and a.OriginDispatchHub in(01,03,04,05,06,07,10,11,12,13,14,15,16,17,20,24,26,29,30,33,34,48) then 'UNYSON'

       

       

        WHEN c.HubID =10 and a.CarrierSource <> 'DRAY' THEN 'WHITESTOWN'

       

       

        WHEN (a.CarrierSource = 'DRAY' or a.TransportationSubMode in('HSF')) and a.LogisticsFlag = 0 then 'CORE'

       

       

        WHEN c.HubID =10 and a.CarrierSource = 'DRAY' THEN 'CORE'

       

       

        WHEN a.OriginDispatchHub =24 and j.ReportingCustomerNumber in(206,239,360,1088,1450,46159,359,374,35392) and a.TransportationMode = 'OTR' and e.YearNumber >= DatePart(Year,DateAdd(Year,-1,Current_TimeStamp)) THEN 'CAPACITY SOLUTIONS'

       

       

        WHEN c.HubID =24 and e.YearNumber in(2015,2016,2017) THEN 'CAPACITY SOLUTIONS'

       

       

        WHEN a.HighwayServiceType  = 'HCSL' and a.OriginDispatchHub =24 and a.LogisticsFlag = 0 and e.StandardDate >= '09/01/2015' then  'CAPACITY SOLUTIONS'

       

       

         ELSE 'CORE' end as 'BusinessCategory'

       

       

      ,CASE WHEN a.OriginZoneNumberHighway in(294,292,296,282,276,284) then 'CAROLINAS'

                  WHEN a.OriginZoneNumberHighway in(303) then 'ATLANTA'

      WHEN a.OriginZoneNumberHighway in(61,21,11,32) then 'NEW ENGLAND'

      WHEN a.OriginZoneNumberHighway in(197,212,89,181,171,191,152,200,122,112,142,148,165,146,132) then 'MID-ATLANTIC'

      WHEN a.OriginZoneNumberHighway in(606,601,617,604,611,612,625) then 'CHICAGO'

      WHEN a.OriginZoneNumberHighway in(384) then 'MEMPHIS'

      WHEN a.OriginZoneNumberHighway in(752,780,770,761) then 'TEXAS'

      WHEN a.OriginZoneNumberHighway in(900,917) then 'LA/ONT'

      WHEN a.OriginZoneNumberHighway in(641,631) then 'STL/KC'

      WHEN (a.OriginZoneNumberHighway in(952,941) OR j.OriginZipCode3 in ('955','960')) then 'NORTHERN CAL'

      WHEN a.OriginZoneNumberHighway in(352,356,366,361) then 'ALABAMA'

      WHEN a.OriginZoneNumberHighway in(477,462) then 'INDY'

      WHEN a.OriginZoneNumberHighway in(235,232,240,226) then 'VIRGINIA'

      WHEN a.OriginZoneNumberHighway in(452,441,432,436,482) then 'OHIO'

      ELSE 'zOTHER' end as Market

       

       

      ,a.OrderUserID

      ,a.OrderSource

      ,h.ZoneName

      ,a.OriginCity

      ,a.OriginState

      ,j.OriginCountryCode

      ,j.OriginZipCode3

      ,a.OriginZoneNumberHighway

      ,m.ZoneNumber as 'OriginDATZone'

      ,m.ZoneDescription as 'OriginDATZoneDesc'

      ,a.DestinationCity

      ,a.DestinationState

      ,j.DestinationCountryCode

      ,j.DestinationZipCode3

      ,a.DestinationZoneNumberHighway

      ,n.ZoneNumber as 'DestinationDATZone'

      ,n.ZoneDescription as 'DestinationDATZoneDesc'

      ,f.DualModeLaneID

      ,j.OrderDate

      ,a.HighwayVendorNumber

      ,a.CommodityCode

      ,k.VendorName

      ,a.HighwayEquipmentCode

      ,i.HighwayEquipmentCodeDescription

      ,l.OriginDispatchDate

      ,a.PriorWeekYearNumber

      ,a.CurrentWeekYearNumber

      ,a.PriorWeekNumber

      ,o.VendorName as OriginDraymanVendorName

      ,p.VendorName as DestinationDraymanVendorName

      ,Cast(Null as Money) as TotalRevenue

      ,Cast(Null as Money) as LineHaulRevenue

      ,Cast(Null as Money) as FuelRevenue

      ,Cast(Null as Money) as EquipmentRevenue

      ,Cast(Null as Money) as NonEquipmentRevenue

      ,Cast(Null as Money) as OtherRevenue

       

       

      ,Cast(Null as Money) as TotalCost

      ,Cast(Null as Money) as LineHaulCost

      ,Cast(Null as Money) as FuelCost

      ,Cast(Null as Money) as EquipmentCost

      ,Cast(Null as Money) as NonEquipmentCost

      ,Cast(Null as Money) as OtherCost

       

       

      ,Cast(Null as Money) as TotalMargin

      ,Cast(Null as Money) as LineHaulMargin

      ,Cast(Null as Money) as FuelMargin

      ,Cast(Null as Money) as EquipmentMargin

      ,Cast(Null as Money) as NonEquipmentMargin

      ,Cast(Null as Money) as OtherMargin

      ,Cast(Null as nvarchar(1)) as PriorDayFlag

      ,Cast(Null as nvarchar(1)) as WTD

      ,Cast(Null as nvarchar(1)) as MTD

      ,Cast(Null as nvarchar(1)) as QTD

      ,Cast(Null as nvarchar(1)) as YTD

      ,Cast(Null as nvarchar(255)) as OTRIMLOffice

      ,Cast(Null as nvarchar(255)) as OTRIMLRegion

      ,Cast(Null as nvarchar(255)) as RCSub

      ,Cast(Null as nvarchar(255)) as HSFFlag

      ,Cast(Null as nvarchar(255)) as PricingRegion

       

       

      ,Case when df.ReasonCode in ('BD','BE','CR','UA') then 1 else otp.DeliveryOnTimeCount end as DeliveryOnTimeCount

       

       

       

       

      From  dw2.dbo.vwFactHeaderMasterV2Table a with (nolock)

      left join dw2.dbo.vwDimRegion b with (nolock)

      on a.DimRegionFunctionKey = b.DWKey

       

       

      left join dw2.dbo.vwDimHub c with (nolock)

      on a.SellingHub = c.HubID

       

       

      left join dw2.dbo.OperationalSummary d with (nolock)

      on a.OrderNumber = d.OrderNumber

       

       

      left join dw2.dbo.dimDate e with (nolock)

      on a.DSRDateNull = e.StandardDate

       

       

      left join dw2.dbo.vwDimDocuments f with (nolock)

      on a.OrderNumber = f.OrderNumber

       

       

      left join dw2.dbo.DimRamp g with (nolock)

      on a.DimRampRailOriginKey = g.DWKey

       

       

      left join dw2.dbo.vwDimZonesHighway h with (nolock)

      on a.OriginZoneNumberHighway = h.ZoneNumber

       

       

      left join dw2.dbo.vwDimHighwayEquipmentCodes i with (nolock)

      on a.HighwayEquipmentCode = i.HighwayEquipmentCode

       

       

      left join dw2.dbo.OperationalSummary j with (nolock)

      on a.OrderNumber = j.OrderNumber

       

       

      left join dw2.dbo.vwDimVendor k with (nolock)

      on a.HighwayVendorNumber = k.VendorNumber

       

       

      left join dw2.dbo.vwDimOrderEventDates l with (nolock)

      on a.OrderNumber = l.OrderNumber

       

       

      left join (Select Distinct a.ZoneNumber, a.ZoneDescription, b.Zip3

      from ODS_TPS_IU.dbo.ZNEZNEMP_BN a with (nolock)

      left join ODS_TPS_IU.dbo.ZNEZIPTP_BN b with (nolock)

      on a.ZoneNumber = b.ZoneNumber) m

       

       

      on j.OriginZipCode3 = m.Zip3

      left join (Select Distinct a.ZoneNumber, a.ZoneDescription, b.Zip3

      from ODS_TPS_IU.dbo.ZNEZNEMP_BN a with (nolock)

      left join ODS_TPS_IU.dbo.ZNEZIPTP_BN b with (nolock)

      on a.ZoneNumber = b.ZoneNumber) n

      on j.DestinationZipCode3 = n.Zip3

       

       

      left join dw2.dbo.dimVendor o with (nolock)

      on a.OriginDraymanVendorNumber = o.VendorNumber

       

       

      left join dw2.dbo.dimVendor p with (nolock)

      on a.DestinationDraymanVendorNumber = p.VendorNumber

       

       

      left join dw2.dbo.dimHub q with (nolock)

      on a.OriginDispatchHub = q.HubID

       

       

      left join dw2.dbo.OTPMaster otp with (nolock)

      on a.OrderNumber = otp.OrderNumber

       

       

      left join dw2.dbo.vwdeliveryfailure df with (nolock)

      on a.OrderNumber = df.OrderNumber

      Where

       

       

      a.TransportationMode = 'OTR'

      and a.TransportationSubMode not in('PHM','IML','HDT')

      and a.OrderStatus not in ( 'CNL')

      and c.HubID not in ( '35','02','38','08','54','55','18'  )

      and a.OriginDispatchHub  not in( '18')

      and a.CustomerNumber  NOT IN  ( 94, 33976, 33977, 48839  )

      and a.OrderNumber  NOT IN  ( 109081134, 109081154, 109081187, 113516389, 111627289, 111104365, 110943603, 111056691, 111047990, 111189538, 111174811, 111345038,

      111335582, 111479086, 111365499, 111476008, 111631635, 111626090, 111785046, 111770524, 111791886, 111915326, 111908320, 112068606,

      112060525, 112227441, 112208144, 112235404, 112400540, 112391788, 112537744, 112551154, 112716150, 112682011, 112682041, 112811238,

      112811173, 114303404, 114696522, 116681251, 115377104, 115446054, 115446068, 116236557, 116663613, 116663613, 116663613, 115260820,

      114661094, 115371648, 115595367, 115645078, 115645080, 115645090, 115646256, 115646261, 115646262, 115647299, 115725234, 116199046,

      116293000, 116525134, 116822768, 116322583, 116490867, 115295297, 116261898, 116261903, 114931664, 115082318, 116143771, 116306202,

      116452718, 115234599, 115234599, 122046809, 122046878, 122046921, 122046997, 122047041, 122047079, 122047153, 122047196, 122047464,

      122047488, 122047569, 122047598, 122047713, 122048981, 122047102, 122047449  )

      and

      (a.LogisticsFlag  =  '0' or (a.LogisticsFlag  =  '1' and a.OriginDispatchHub  IN  ( '01','03','10','13','15','29','30','04','07','17','20','24','26','05','06','11','12','14','16','33','34','48','18'  )))

      and e.YearNumber >=  DatePart(Year,DateAdd(Year,-1,Current_TimeStamp))

       

       

       

       

       

       

       

       

      Update  dbo.HighwayOverview

      Set OTRIMLOffice = CASE WHEN ReportingCustomerNumber =  320 then '29'

                 WHEN ReportingCustomerNumber <> 320 then  Left(DualModeLaneID,2)

        ELSE HubID  end

       

       

       

       

      Update  dbo.HighwayOverview

      Set OTRIMLRegion = CASE WHEN (CASE WHEN ReportingCustomerNumber =  320 then '29'

                 WHEN ReportingCustomerNumber <> 320 then  Left(DualModeLaneID,2)

        ELSE HubID  end) = '10' then 'WHITESTOWN'

        WHEN (CASE WHEN ReportingCustomerNumber =  320 then '29'

                 WHEN ReportingCustomerNumber <> 320 then  Left(DualModeLaneID,2)

        ELSE HubID  end)  in('04','07','17','20','03','26','08','21')  then 'EAST'

        WHEN (CASE WHEN ReportingCustomerNumber =  320 then '29'

                 WHEN ReportingCustomerNumber <> 320 then  Left(DualModeLaneID,2)

        ELSE HubID  end)  in('05','06','12','15','24','29','13','01','02','30','34','38','22')  then 'CENTRAL'

        WHEN (CASE WHEN ReportingCustomerNumber =  320 then '29'

                 WHEN ReportingCustomerNumber <> 320 then  Left(DualModeLaneID,2)

        ELSE HubID  end)  in('14','33','16','11','18','48')  then 'WEST'

         WHEN (CASE WHEN ReportingCustomerNumber =  320 then '29'

                 WHEN ReportingCustomerNumber <> 320 then  Left(DualModeLaneID,2)

        ELSE HubID  end)  in('31')  then 'UNYSON' else 'zOther' end

       

       

       

       

      Update  dbo.HighwayOverview

      Set RCSub = (ReportingCustomerName + TransportationSubMode)

       

       

       

       

      Update  dbo.HighwayOverview

      Set HSFFlag = Case when HighwayVendorNumber = 976 and HubID <> 10 and TransportationSubMode = 'HSF' then 1

        when HighwayVendorNumber = 976 and HubID <> 10 and CarrierSource = 'DRAY' then 1

        when ReportingCustomerNumber = 206 and TransportationSubMode = 'HSF' then 1 else 0 end

       

       

       

       

      Update dbo.HighwayOverview

      Set  TotalRevenue = b.TotalRevenue

      , LineHaulRevenue = b.LineHaulRevenue

      , FuelRevenue = b.FuelRevenue

      , EquipmentRevenue = b.EquipmentRevenue

      , NonEquipmentRevenue = b.NonEquipmentRevenue

      , OtherRevenue = b.OtherRevenue

      , TotalCost = b.TotalCost

      , LineHaulCost = b.LineHaulCost

      , FuelCost = b.FuelCost

      , EquipmentCost = b.EquipmentCost

      , NonEquipmentCost = b.NonEquipmentCost

      , OtherCost = b.OtherCost

      , TotalMargin = b.TotalMargin

      , LineHaulMargin = b.LineHaulMargin

      , FuelMargin = b.FuelMargin

      , EquipmentMargin = b.EquipmentMargin

      , NonEquipmentMargin = b.NonEquipmentMargin

      , OtherMargin = b.OtherMargin

      from dbo.HighwayOverview a

      left join (

      Select Distinct OrderNumber

      ,Sum(Revenue) AS TotalRevenue

      ,SUM(CASE WHEN ChargeCategory = 'Line Haul' THEN Revenue ELSE 0 END) AS LineHaulRevenue

      ,SUM(CASE WHEN ChargeCategory = 'Fuel' THEN Revenue ELSE 0 END) AS FuelRevenue

      ,SUM(CASE WHEN ChargeCategory = 'Equipment Accessorial' THEN Revenue ELSE 0 END) AS EquipmentRevenue

      ,SUM(CASE WHEN ChargeCategory = 'Non-Equipment  Accessorial' THEN Revenue ELSE 0 END) AS NonEquipmentRevenue

      ,SUM(CASE WHEN ChargeCategory NOT IN ('Line Haul','Fuel','Non-Equipment Accessorial','Other', 'Equipment Accessorial' ) THEN Revenue ELSE 0 END) AS OtherRevenue

       

       

      ,Sum(Cost_Blended) AS TotalCost

      ,SUM(CASE WHEN ChargeCategory = 'Line Haul' THEN Cost_Blended ELSE 0 END) AS LineHaulCost

      ,SUM(CASE WHEN ChargeCategory = 'Fuel' THEN Cost_Blended ELSE 0 END) AS FuelCost

      ,SUM(CASE WHEN ChargeCategory = 'Equipment Accessorial' THEN Cost_Blended ELSE 0 END) AS EquipmentCost

      ,SUM(CASE WHEN ChargeCategory = 'Non-Equipment  Accessorial' THEN Cost_Blended ELSE 0 END) AS NonEquipmentCost

      ,SUM(CASE WHEN ChargeCategory NOT IN ('Line Haul','Fuel','Non-Equipment Accessorial','Other', 'Equipment Accessorial' ) THEN Cost_Blended ELSE 0 END) AS OtherCost

       

       

      ,Sum(Margin_Blended) AS TotalMargin

      ,SUM(CASE WHEN ChargeCategory = 'Line Haul' THEN Margin_Blended ELSE 0 END) AS LineHaulMargin

      ,SUM(CASE WHEN ChargeCategory = 'Fuel' THEN Margin_Blended ELSE 0 END) AS FuelMargin

      ,SUM(CASE WHEN ChargeCategory = 'Equipment Accessorial' THEN Margin_Blended ELSE 0 END) AS EquipmentMargin

      ,SUM(CASE WHEN ChargeCategory = 'Non-Equipment  Accessorial' THEN Margin_Blended ELSE 0 END) AS NonEquipmentMargin

      ,SUM(CASE WHEN ChargeCategory NOT IN ('Line Haul','Fuel','Non-Equipment Accessorial','Other', 'Equipment Accessorial' ) THEN Margin_Blended ELSE 0 END) AS OtherMargin

      from dw2.dbo.AnalyticalDetail with (nolock)

      Where TransportationMode = 'OTR'

      and DSRDateYearNumber >= DatePart(Year,DateAdd(Year,-1,Current_TimeStamp))

      and OrderStatus <> 'CNL'

      Group By OrderNumber) b

      on a.OrderNumber = b.OrderNumber

       

       

       

       

       

       

      Update  dbo.HighwayOverview

      Set PriorDayFlag = Case when  Datepart(dw,StandardDate) = Datepart(dw,DateAdd(day,-1,CURRENT_TIMESTAMP)) and WeekNumber = ((Case when (DATEPART(week,Current_Timestamp)-1) = 52 then 1 else (DATEPART(week,Current_Timestamp)) end)) then 'Y' else 'N' end

      ,WTD = Case when WeekNumber = ((Case when (DATEPART(week,Current_Timestamp)-1) = 52 then 1 else (DATEPART(week,Current_Timestamp)) end)) and  Datepart(dw,StandardDate) <= (Case when Datepart(dw,DateAdd(day,-1,CURRENT_TIMESTAMP)) = 7 then 1 else Datepart(dw,DateAdd(day,-1,CURRENT_TIMESTAMP)) end) then 'Y' else 'N' end

      ,MTD = Case when MonthNumber = DatePart(month,DateAdd(day,-1,Current_TimeStamp)) and DayOfTheMonthNumber <= DatePart(day,DateAdd(day,-1,Current_TimeStamp)) then 'Y' else 'N' end

      ,QTD = Case when QuarterNumber = DatePart(quarter,DateAdd(day,-1,Current_TimeStamp)) and ( (MonthNumber < DatePart(month,DateAdd(day,-1,Current_TimeStamp)))  or (MonthNumber = DatePart(month,DateAdd(day,-1,Current_TimeStamp)) and DayOfTheMonthNumber <= DatePart(day,DateAdd(day,-1,Current_TimeStamp)))) then 'Y' else 'N' end

      ,YTD = Case when ((MonthNumber < DatePart(month,DateAdd(day,-1,Current_TimeStamp)))  or (MonthNumber = DatePart(month,DateAdd(day,-1,Current_TimeStamp)) and DayOfTheMonthNumber <= DatePart(day,DateAdd(day,-1,Current_TimeStamp)))) then 'Y' else 'N' end

       

       

       

       

      Update  dbo.HighwayOverview

      Set  PricingRegion = Case when OriginZipCode3 in('769','780','781','782','783','784','785','788','797','798','799','880','885') then 'LAREDO'

      when OriginZipCode3 in('400','401','402','403','404','405','406','410','413','414','417','418','419','420','421','422','423','

      424','427','434','435','436','458','460','461','462','463','464','465','466','467','468','469','471','

      472','473','474','475','476','477','478','479','480','481','482','483','484','485','486','487','488','

      489','490','491','492','493','494','495','496','497','498','499','500','501','502','503','504','505','

      506','507','508','509','510','511','512','513','514','520','521','522','523','524','525','526','527','

      528','530','531','532','534','535','537','538','539','540','541','542','543','544','545','546','547','

      548','549','550','551','553','554','555','556','557','558','559','560','561','562','563','564','565','

      566','567','570','571','572','573','574','575','576','577','580','581','582','583','584','585','586','

      587','588','600','601','602','603','604','605','606','607','608','610','611','612','613','614','619','

      624','628','629') then 'NORTH CENTRAL'

                                when OriginZipCode3 in('005','010','011','012','013','014','015','016','017','018','019','020','021','022','023','024','025','

      026','027','028','029','030','031','032','033','034','035','036','037','038','039','040','041','042','

      043','044','045','046','047','048','049','050','051','052','053','054','055','056','057','058','059','

      060','061','062','063','064','065','066','067','068','069','070','071','072','073','074','075','076','

      077','078','079','080','081','082','083','084','085','086','087','088','089','100','101','102','103','

      104','105','106','107','108','109','110','111','112','113','114','115','116','117','118','119','120','

      121','122','123','124','125','126','127','128','129','130','131','132','133','134','135','136','137','

      138','139','140','141','142','143','144','145','146','147','148','149','150','151','152','153','154','

      155','156','157','158','159','160','161','162','163','164','165','166','167','168','169','170','171','

      172','173','174','175','176','177','178','179','180','181','182','183','184','185','186','187','188','

      189','190','191','192','193','194','195','196','197','198','199','200','202','203','204','205','206','

      207','208','209','210','211','212','214','215','216','217','218','219','247','248','249','250','251','

      252','253','254','255','256','257','258','259','260','261','262','263','264','265','266','267','268','

      411','412','415','416','430','431','432','433','437','438','439','440','441','442','443','444','445','

      446','447','448','449','450','451','452','453','454','455','456','457','459','470','569') then 'NORTH EAST'

                                when OriginZipCode3 in('375','380','381','382','383','386','515','516','609','615','616','617','618','620','622','623','625','

      626','627','630','631','633','634','635','636','637','638','639','640','641','644','645','646','647','

      648','649','650','651','652','653','654','655','656','657','658','660','661','662','664','665','666','

      667','668','669','670','671','672','673','674','675','676','677','678','679','680','681','683','684','

      685','686','687','688','689','690','691','692','693','700','701','703','704','705','706','707','708','

      710','711','712','713','714','716','717','718','719','720','721','722','723','724','725','726','727','

      728','729','730','731','733','734','735','736','737','738','739','740','741','743','744','745','746','

      747','748','749','750','751','752','753','754','755','756','757','758','759','760','761','762','763','

      764','765','766','767','768','770','772','773','774','775','776','777','778','779','786','787','789','

      790','791','792','793','794','795','796') then 'SOUTH CENTRAL'

                                when OriginZipCode3 in('201','220','221','222','223','224','225','226','227','228','229','230','231','232','233','234','235','

      236','237','238','239','240','241','242','243','244','245','246','270','271','272','273','274','275','

      276','277','278','279','280','281','282','283','284','285','286','287','288','289','290','291','292','

      293','294','295','296','297','298','299','300','301','302','303','304','305','306','307','308','309','

      310','311','312','313','314','315','316','317','318','319','320','321','322','323','324','325','326','

      327','328','329','330','331','332','333','334','335','336','337','338','339','341','342','344','346','

      347','349','350','351','352','354','355','356','357','358','359','360','361','362','363','364','365','

      366','367','368','369','370','371','372','373','374','376','377','378','379','384','385','387','388','

      389','390','391','392','393','394','395','396','397','398','399','407','408','409','425','426') then 'SOUTH EAST'

                                when OriginZipCode3 in('590','591','592','593','594','595','596','597','598','599','800','801','802','803','804','805','806','

      807','808','809','810','811','812','813','814','815','816','820','821','822','823','824','825','826','

      827','828','829','830','831','832','833','834','835','836','837','838','840','841','842','843','844','

      845','846','847','850','851','852','853','855','856','857','859','860','863','864','865','870','871','

      872','873','874','875','877','878','879','881','882','883','884','889','890','891','893','894','895','

      897','898','900','901','902','903','904','905','906','907','908','910','911','912','913','914','915','

      916','917','918','919','920','921','922','923','924','925','926','927','928','930','931','932','933','

      934','935','936','937','938','939','940','941','942','943','944','945','946','947','948','949','950','

      951','952','953','954','955','956','957','958','959','960','961','970','971','972','973','974','975','

      976','977','978','979','980','981','982','983','984','985','986','988','989','990','991','992','993','

      994') then 'WEST' else 'zOTHER' end

       

       

      Update  dbo.HighwayOverview

      Set  PricingRegion = Case when OriginState in('AB','BC','MB','ON','SK') then 'NORTH CENTRAL'

      when OriginState in('NB','NF','NS','QC') then 'NORTH EAST' else PricingRegion end

                 

       

       

       

       

       

       

       

       

       

       

      ------------------

      --FINAL OUTPUT--

      ------------------

      SELECT *

      FROM dbo.HighwayOverview

       

       

      END

      GO

       

      Thanks,

      Ravindra