Dashboard data connection taking more time to edit which is using stored procedure
ravindra babu Aug 17, 2017 3:27 PMHi 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
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