April 24, 2017 at 9:04 am
Good morning - This is first time posting to this forum and I need help in streamlining my code. I am attaching sample input data and sample output data as well as table definition and current code I am using. I need for my code to be better streamlined in an array. The input data is what we receive from our vendor, in which the code looks to see if there is a value in fields code 1 thru 15 (COD1C-COD15), then the output field will be assigned the amount 1 thru 15 (AMT1-AMT15) that's associated with it. I can't put my head around how to get this into an array and have the code conduct a do loop (if that is the correct term). Can someone review the attached? We tend to add additional charges and copying and pasting the same logic is redundant if there is a more efficient way to code this.
Thank you-
April 24, 2017 at 9:07 am
anissw - Monday, April 24, 2017 9:04 AMGood morning - This is first time posting to this forum and I need help in streamlining my code. I am attaching sample input data and sample output data as well as table definition and current code I am using. I need for my code to be better streamlined in an array. The input data is what we receive from our vendor, in which the code looks to see if there is a value in fields code 1 thru 15 (COD1C-COD15), then the output field will be assigned the amount 1 thru 15 (AMT1-AMT15) that's associated with it. I can't put my head around how to get this into an array and have the code conduct a do loop (if that is the correct term). Can someone review the attached? We tend to add additional charges and copying and pasting the same logic is redundant if there is a more efficient way to code this.Thank you-
Hi, welcome to the forums.
Unfortunately, I'm not going to open a zip file in my computer, and most people won't either. To get better help, read the articles on my signature to know how to post what we would need to give a proper answer.
April 24, 2017 at 9:50 am
Downloaded the file, remotely, on my home Raspberry Pi (the expendable one), and the zip file does include 3 xlsx file and a txt file (which contains SQL), so it does seem legit
I can't, however, open that file at work as it's an untrusted ZIP file and I would suggest, like Luis said, provided the data in a consumable format for everyone. Forum users have very little trust of ZIP files uploaded to forums, etc.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 24, 2017 at 10:07 am
Hi Luis -
Thanks for Replying. I have done instructed and posting the SQL Code for you to review. I am attaching the table definition in Excel file as well. Let me know if there is anything else I need to include.
IF OBJECT_ID('TempDB..#SPECIALCHARGES','U') IS NOT NULL
DROP TABLE #SPECIALCHARGES
CREATE TABLE #SPECIALCHARGES (
[OWNERB] [varchar](8) NULL,
[ORDNOB] [numeric](11, 0) NOT NULL,
[HSTRFB] [varchar](15) NULL,
[COD1C] [varchar](3) NULL,
[DESC1] [varchar](40) NULL,
[AMT1C] [numeric](13, 2) NULL,
[COD2C] [varchar](3) NULL,
[DESC2] [varchar](40) NULL,
[AMT2C] [numeric](13, 2) NULL,
[COD3C] [varchar](3) NULL,
[DESC3] [varchar](40) NULL,
[AMT3C] [numeric](13, 2) NULL,
[COD4C] [varchar](3) NULL,
[DESC4] [varchar](40) NULL,
[AMT4C] [numeric](13, 2) NULL,
[COD5C] [varchar](3) NULL,
[DESC5] [varchar](40) NULL,
[AMT5C] [numeric](13, 2) NULL,
[COD6C] [varchar](3) NULL,
[DESC6] [varchar](40) NULL,
[AMT6C] [numeric](13, 2) NULL,
[COD7C] [varchar](3) NULL,
[DESC7] [varchar](40) NULL,
[AMT7C] [numeric](13, 2) NULL,
[COD8C] [varchar](3) NULL,
[DESC8] [varchar](40) NULL,
[AMT8C] [numeric](13, 2) NULL,
[COD9C] [varchar](3) NULL,
[DESC9] [varchar](40) NULL,
[AMT9C] [numeric](13, 2) NULL,
[COD10C] [varchar](3) NULL,
[DESC10] [varchar](40) NULL,
[AMT10C] [numeric](13, 2) NULL,
[COD11C] [varchar](3) NULL,
[DESC11] [varchar](40) NULL,
[AMT11C] [numeric](13, 2) NULL,
[COD12C] [varchar](3) NULL,
[DESC12] [varchar](40) NULL,
[AMT12C] [numeric](13, 2) NULL,
[COD13C] [varchar](3) NULL,
[DESC13] [varchar](40) NULL,
[AMT13C] [numeric](13, 2) NULL,
[COD14C] [varchar](3) NULL,
[DESC14] [varchar](40) NULL,
[AMT14C] [numeric](13, 2) NULL,
[COD15C] [varchar](3) NULL,
[DESC15] [varchar](40) NULL,
[AMT15C] [numeric](13, 2) NULL
) ON [PRIMARY]
GO
INSERT INTO #SPECIALCHARGES ([OWNERB]
,[ORDNOB]
,[HSTRFB]
,[COD1C]
,[DESC1]
,[AMT1C]
,[COD2C]
,[DESC2]
,[AMT2C]
,[COD3C]
,[DESC3]
,[AMT3C]
,[COD4C]
,[DESC4]
,[AMT4C]
,[COD5C]
,[DESC5]
,[AMT5C]
,[COD6C]
,[DESC6]
,[AMT6C]
,[COD7C]
,[DESC7]
,[AMT7C]
,[COD8C]
,[DESC8]
,[AMT8C]
,[COD9C]
,[DESC9]
,[AMT9C]
,[COD10C]
,[DESC10]
,[AMT10C]
,[COD11C]
,[DESC11]
,[AMT11C]
,[COD12C]
,[DESC12]
,[AMT12C]
,[COD13C]
,[DESC13]
,[AMT13C]
,[COD14C]
,[DESC14]
,[AMT14C]
,[COD15C]
,[DESC15]
,[AMT15C])
SELECT 'MORTEX01',81834707, 'WEBF3539342', '500', '# of Pallets', 1, '510', 'Stretch-wrap', 1, '130', NULL, 10, '200', 'Completed BOL', 1, '210', 'Pack List w/shipment', 1, NULL, NULL, 0, NULL, NULL, 0, NULL, NULL, 0, NULL, NULL, 0, NULL, NULL, 0, NULL, NULL, 0, NULL, NULL, 0, NULL, NULL, 0, NULL, NULL, 0, NULL, NULL, 0 UNION ALL
SELECT 'ICI01', 82159863, 'WEBF3539821', '510', 'Stretch-wrap', 1, '515', 'Corrugated pallet wrap', 1, '210', 'Pack List w/shipment', 1, '230', 'COA Sheets sent', 1, '100', 'Picking - Pallet', 1, '110', 'Picking - Eaches', 12, '540', 'Record Lot/Serial #', 1, '200', 'Completed BOL', 1, NULL, NULL, 0, NULL, NULL, 0, NULL, NULL, 0, NULL, NULL, 0, NULL, NULL, 0, NULL, NULL, 0, NULL, NULL, 0 UNION ALL
SELECT 'EVAERO01', 89147593, '4002042138', '500', '# of Pallets', 1, '510', 'Stretch-wrap', 1, '200', 'Completed BOL', 1, '370', 'Marking/Tagging', 18, '230', 'COA Sheets sent', 2, NULL, NULL, 0, NULL, NULL, 0, NULL, NULL, 0, NULL, NULL, 0, NULL, NULL, 0, NULL, NULL, 0, NULL, NULL, 0, NULL, NULL, 0, NULL, NULL, 0, NULL, NULL, 0 UNION ALL
SELECT 'ALPATT01', 84523672, 'WEBF3542276', '010', 'Billable Hours-Reg', 0.75, '040', 'Forklift Hours', 1, '500', '# of Pallets', 1, NULL, NULL, 0, NULL, NULL, 0, NULL, NULL, 0, NULL, NULL, 0, NULL, NULL, 0, NULL, NULL, 0, NULL, NULL, 0, NULL, NULL, 0, NULL, NULL, 0, NULL, NULL, 0, NULL, NULL, 0, NULL, NULL, 0
select * from #SPECIALCHARGES
SELECT [OWNERB]
,[ORDNOB] As [WMS Order No]
,[HSTRFB] As [CUST ORD NO]
,CASE WHEN [COD1C] IN ('H','010') THEN [AMT1C]
WHEN [COD2C] IN ('H','010') THEN [AMT2C]
WHEN [COD3C] IN ('H','010') THEN [AMT3C]
WHEN [COD4C] IN ('H','010') THEN [AMT4C]
WHEN [COD5C] IN ('H','010') THEN [AMT5C]
WHEN [COD6C] IN ('H','010') THEN [AMT6C]
WHEN [COD7C] IN ('H','010') THEN [AMT7C]
WHEN [COD8C] IN ('H','010') THEN [AMT8C]
WHEN [COD9C] IN ('H','010') THEN [AMT9C]
WHEN [COD10C] IN ('H','010') THEN [AMT10C]
WHEN [COD11C] IN ('H','010') THEN [AMT11C]
WHEN [COD12C] IN ('H','010') THEN [AMT12C]
WHEN [COD13C] IN ('H','010') THEN [AMT13C]
WHEN [COD14C] IN ('H','010') THEN [AMT14C]
WHEN [COD15C] IN ('H','010') THEN [AMT15C] ELSE 0 END AS [MAN HOURS]
,CASE WHEN [COD1C] IN ('P','500') THEN [AMT1C]
WHEN [COD2C] IN ('P','500') THEN [AMT2C]
WHEN [COD3C] IN ('P','500') THEN [AMT3C]
WHEN [COD4C] IN ('P','500') THEN [AMT4C]
WHEN [COD5C] IN ('P','500') THEN [AMT5C]
WHEN [COD6C] IN ('P','500') THEN [AMT6C]
WHEN [COD7C] IN ('P','500') THEN [AMT7C]
WHEN [COD8C] IN ('P','500') THEN [AMT8C]
WHEN [COD9C] IN ('P','500') THEN [AMT9C]
WHEN [COD10C] IN ('P','500') THEN [AMT10C]
WHEN [COD11C] IN ('P','500') THEN [AMT11C]
WHEN [COD12C] IN ('P','500') THEN [AMT12C]
WHEN [COD13C] IN ('P','500') THEN [AMT13C]
WHEN [COD14C] IN ('P','500') THEN [AMT14C]
WHEN [COD15C] IN ('P','500') THEN [AMT15C] ELSE 0 END AS [PALLETS]
,CASE WHEN [COD1C] IN ('F','130') THEN [AMT1C]
WHEN [COD2C] IN ('F','130') THEN [AMT2C]
WHEN [COD3C] IN ('F','130') THEN [AMT3C]
WHEN [COD4C] IN ('F','130') THEN [AMT4C]
WHEN [COD5C] IN ('F','130') THEN [AMT5C]
WHEN [COD6C] IN ('F','130') THEN [AMT6C]
WHEN [COD7C] IN ('F','130') THEN [AMT7C]
WHEN [COD8C] IN ('F','130') THEN [AMT8C]
WHEN [COD9C] IN ('F','130') THEN [AMT9C]
WHEN [COD10C] IN ('F','130') THEN [AMT10C]
WHEN [COD11C] IN ('F','130') THEN [AMT11C]
WHEN [COD12C] IN ('F','130') THEN [AMT12C]
WHEN [COD13C] IN ('F','130') THEN [AMT13C]
WHEN [COD14C] IN ('F','130') THEN [AMT14C]
WHEN [COD15C] IN ('F','130') THEN [AMT15C] ELSE 0 END AS [FULL_CASE]
,CASE WHEN [COD1C] IN ('S','510') THEN [AMT1C]
WHEN [COD2C] IN ('S','510') THEN [AMT2C]
WHEN [COD3C] IN ('S','510') THEN [AMT3C]
WHEN [COD4C] IN ('S','510') THEN [AMT4C]
WHEN [COD5C] IN ('S','510') THEN [AMT5C]
WHEN [COD6C] IN ('S','510') THEN [AMT6C]
WHEN [COD7C] IN ('S','510') THEN [AMT7C]
WHEN [COD8C] IN ('S','510') THEN [AMT8C]
WHEN [COD9C] IN ('S','510') THEN [AMT9C]
WHEN [COD10C] IN ('S','510') THEN [AMT10C]
WHEN [COD11C] IN ('S','510') THEN [AMT11C]
WHEN [COD12C] IN ('S','510') THEN [AMT12C]
WHEN [COD13C] IN ('S','510') THEN [AMT13C]
WHEN [COD14C] IN ('S','510') THEN [AMT14C]
WHEN [COD15C] IN ('S','510') THEN [AMT15C] ELSE 0 END AS [STRETCH]
,CASE WHEN [COD1C] IN ('C','520') THEN [AMT1C]
WHEN [COD2C] IN ('C','520') THEN [AMT2C]
WHEN [COD3C] IN ('C','520') THEN [AMT3C]
WHEN [COD4C] IN ('C','520') THEN [AMT4C]
WHEN [COD5C] IN ('C','520') THEN [AMT5C]
WHEN [COD6C] IN ('C','520') THEN [AMT6C]
WHEN [COD7C] IN ('C','520') THEN [AMT7C]
WHEN [COD8C] IN ('C','520') THEN [AMT8C]
WHEN [COD9C] IN ('C','520') THEN [AMT9C]
WHEN [COD10C] IN ('C','520') THEN [AMT10C]
WHEN [COD11C] IN ('C','520') THEN [AMT11C]
WHEN [COD12C] IN ('C','520') THEN [AMT12C]
WHEN [COD13C] IN ('C','520') THEN [AMT13C]
WHEN [COD14C] IN ('C','520') THEN [AMT14C]
WHEN [COD15C] IN ('C','520') THEN [AMT15C] ELSE 0 END AS [CONT_PLT_BLD]
,CASE WHEN [COD1C] IN ('O','450') THEN [AMT1C]
WHEN [COD2C] IN ('O','450') THEN [AMT2C]
WHEN [COD3C] IN ('O','450') THEN [AMT3C]
WHEN [COD4C] IN ('O','450') THEN [AMT4C]
WHEN [COD5C] IN ('O','450') THEN [AMT5C]
WHEN [COD6C] IN ('O','450') THEN [AMT6C]
WHEN [COD7C] IN ('O','450') THEN [AMT7C]
WHEN [COD8C] IN ('O','450') THEN [AMT8C]
WHEN [COD9C] IN ('O','450') THEN [AMT9C]
WHEN [COD10C] IN ('O','450') THEN [AMT10C]
WHEN [COD11C] IN ('O','450') THEN [AMT11C]
WHEN [COD12C] IN ('O','450') THEN [AMT12C]
WHEN [COD13C] IN ('O','450') THEN [AMT13C]
WHEN [COD14C] IN ('O','450') THEN [AMT14C]
WHEN [COD15C] IN ('O','450') THEN [AMT15C] ELSE 0 END AS [RUSH_ORDER]
,CASE WHEN [COD1C] IN ('R','320') THEN [AMT1C]
WHEN [COD2C] IN ('R','320') THEN [AMT2C]
WHEN [COD3C] IN ('R','320') THEN [AMT3C]
WHEN [COD4C] IN ('R','320') THEN [AMT4C]
WHEN [COD5C] IN ('R','320') THEN [AMT5C]
WHEN [COD6C] IN ('R','320') THEN [AMT6C]
WHEN [COD7C] IN ('R','320') THEN [AMT7C]
WHEN [COD8C] IN ('R','320') THEN [AMT8C]
WHEN [COD9C] IN ('R','320') THEN [AMT9C]
WHEN [COD10C] IN ('R','320') THEN [AMT10C]
WHEN [COD11C] IN ('R','320') THEN [AMT11C]
WHEN [COD12C] IN ('R','320') THEN [AMT12C]
WHEN [COD13C] IN ('R','320') THEN [AMT13C]
WHEN [COD14C] IN ('R','320') THEN [AMT14C]
WHEN [COD15C] IN ('R','320') THEN [AMT15C] ELSE 0 END AS [RELABELS]
,CASE WHEN [COD1C] IN ('B','200') THEN [AMT1C]
WHEN [COD2C] IN ('B','200') THEN [AMT2C]
WHEN [COD3C] IN ('B','200') THEN [AMT3C]
WHEN [COD4C] IN ('B','200') THEN [AMT4C]
WHEN [COD5C] IN ('B','200') THEN [AMT5C]
WHEN [COD6C] IN ('B','200') THEN [AMT6C]
WHEN [COD7C] IN ('B','200') THEN [AMT7C]
WHEN [COD8C] IN ('B','200') THEN [AMT8C]
WHEN [COD9C] IN ('B','200') THEN [AMT9C]
WHEN [COD10C] IN ('B','200') THEN [AMT10C]
WHEN [COD11C] IN ('B','200') THEN [AMT11C]
WHEN [COD12C] IN ('B','200') THEN [AMT12C]
WHEN [COD13C] IN ('B','200') THEN [AMT13C]
WHEN [COD14C] IN ('B','200') THEN [AMT14C]
WHEN [COD15C] IN ('B','200') THEN [AMT15C] ELSE 0 END AS [BILL OF LADING]
,CASE WHEN [COD1C] IN ('M','430') THEN [AMT1C]
WHEN [COD2C] IN ('M','430') THEN [AMT2C]
WHEN [COD3C] IN ('M','430') THEN [AMT3C]
WHEN [COD4C] IN ('M','430') THEN [AMT4C]
WHEN [COD5C] IN ('M','430') THEN [AMT5C]
WHEN [COD6C] IN ('M','430') THEN [AMT6C]
WHEN [COD7C] IN ('M','430') THEN [AMT7C]
WHEN [COD8C] IN ('M','430') THEN [AMT8C]
WHEN [COD9C] IN ('M','430') THEN [AMT9C]
WHEN [COD10C] IN ('M','430') THEN [AMT10C]
WHEN [COD11C] IN ('M','430') THEN [AMT11C]
WHEN [COD12C] IN ('M','430') THEN [AMT12C]
WHEN [COD13C] IN ('M','430') THEN [AMT13C]
WHEN [COD14C] IN ('M','430') THEN [AMT14C]
WHEN [COD15C] IN ('M','430') THEN [AMT15C] ELSE 0 END AS [MISC_SUP]
,CASE WHEN [COD1C] IN ('L','310') THEN [AMT1C]
WHEN [COD2C] IN ('L','310') THEN [AMT2C]
WHEN [COD3C] IN ('L','310') THEN [AMT3C]
WHEN [COD4C] IN ('L','310') THEN [AMT4C]
WHEN [COD5C] IN ('L','310') THEN [AMT5C]
WHEN [COD6C] IN ('L','310') THEN [AMT6C]
WHEN [COD7C] IN ('L','310') THEN [AMT7C]
WHEN [COD8C] IN ('L','310') THEN [AMT8C]
WHEN [COD9C] IN ('L','310') THEN [AMT9C]
WHEN [COD10C] IN ('L','310') THEN [AMT10C]
WHEN [COD11C] IN ('L','310') THEN [AMT11C]
WHEN [COD12C] IN ('L','310') THEN [AMT12C]
WHEN [COD13C] IN ('L','310') THEN [AMT13C]
WHEN [COD14C] IN ('L','310') THEN [AMT14C]
WHEN [COD15C] IN ('L','310') THEN [AMT15C] ELSE 0 END AS [SPL_SHIP_LBLS]
,CASE WHEN [COD1C] IN ('A','400') THEN [AMT1C]
WHEN [COD2C] IN ('A','400') THEN [AMT2C]
WHEN [COD3C] IN ('A','400') THEN [AMT3C]
WHEN [COD4C] IN ('A','400') THEN [AMT4C]
WHEN [COD5C] IN ('A','400') THEN [AMT5C]
WHEN [COD6C] IN ('A','400') THEN [AMT6C]
WHEN [COD7C] IN ('A','400') THEN [AMT7C]
WHEN [COD8C] IN ('A','400') THEN [AMT8C]
WHEN [COD9C] IN ('A','400') THEN [AMT9C]
WHEN [COD10C] IN ('A','400') THEN [AMT10C]
WHEN [COD11C] IN ('A','400') THEN [AMT11C]
WHEN [COD12C] IN ('A','400') THEN [AMT12C]
WHEN [COD13C] IN ('A','400') THEN [AMT13C]
WHEN [COD14C] IN ('A','400') THEN [AMT14C]
WHEN [COD15C] IN ('A','400') THEN [AMT15C] ELSE 0 END AS [ADMIN_FEE]
,CASE WHEN [COD1C] IN ('D','410') THEN [AMT1C]
WHEN [COD2C] IN ('D','410') THEN [AMT2C]
WHEN [COD3C] IN ('D','410') THEN [AMT3C]
WHEN [COD4C] IN ('D','410') THEN [AMT4C]
WHEN [COD5C] IN ('D','410') THEN [AMT5C]
WHEN [COD6C] IN ('D','410') THEN [AMT6C]
WHEN [COD7C] IN ('D','410') THEN [AMT7C]
WHEN [COD8C] IN ('D','410') THEN [AMT8C]
WHEN [COD9C] IN ('D','410') THEN [AMT9C]
WHEN [COD10C] IN ('D','410') THEN [AMT10C]
WHEN [COD11C] IN ('D','410') THEN [AMT11C]
WHEN [COD12C] IN ('D','410') THEN [AMT12C]
WHEN [COD13C] IN ('D','410') THEN [AMT13C]
WHEN [COD14C] IN ('D','410') THEN [AMT14C]
WHEN [COD15C] IN ('D','410') THEN [AMT15C] ELSE 0 END AS [DETENT_FEE]
,CASE WHEN [COD1C] IN ('E','420') THEN [AMT1C]
WHEN [COD2C] IN ('E','420') THEN [AMT2C]
WHEN [COD3C] IN ('E','420') THEN [AMT3C]
WHEN [COD4C] IN ('E','420') THEN [AMT4C]
WHEN [COD5C] IN ('E','420') THEN [AMT5C]
WHEN [COD6C] IN ('E','420') THEN [AMT6C]
WHEN [COD7C] IN ('E','420') THEN [AMT7C]
WHEN [COD8C] IN ('E','420') THEN [AMT8C]
WHEN [COD9C] IN ('E','420') THEN [AMT9C]
WHEN [COD10C] IN ('E','420') THEN [AMT10C]
WHEN [COD11C] IN ('E','420') THEN [AMT11C]
WHEN [COD12C] IN ('E','420') THEN [AMT12C]
WHEN [COD13C] IN ('E','420') THEN [AMT13C]
WHEN [COD14C] IN ('E','420') THEN [AMT14C]
WHEN [COD15C] IN ('E','420') THEN [AMT15C] ELSE 0 END AS [DUNNAGE_DISP]
,CASE WHEN [COD1C] IN ('I','020') THEN [AMT1C]
WHEN [COD2C] IN ('I','020') THEN [AMT2C]
WHEN [COD3C] IN ('I','020') THEN [AMT3C]
WHEN [COD4C] IN ('I','020') THEN [AMT4C]
WHEN [COD5C] IN ('I','020') THEN [AMT5C]
WHEN [COD6C] IN ('I','020') THEN [AMT6C]
WHEN [COD7C] IN ('I','020') THEN [AMT7C]
WHEN [COD8C] IN ('I','020') THEN [AMT8C]
WHEN [COD9C] IN ('I','020') THEN [AMT9C]
WHEN [COD10C] IN ('I','020') THEN [AMT10C]
WHEN [COD11C] IN ('I','020') THEN [AMT11C]
WHEN [COD12C] IN ('I','020') THEN [AMT12C]
WHEN [COD13C] IN ('I','020') THEN [AMT13C]
WHEN [COD14C] IN ('I','020') THEN [AMT14C]
WHEN [COD15C] IN ('I','020') THEN [AMT15C] ELSE 0 END AS [OVERTIME_HRS]
,CASE WHEN [COD1C] IN ('J','030') THEN [AMT1C]
WHEN [COD2C] IN ('J','030') THEN [AMT2C]
WHEN [COD3C] IN ('J','030') THEN [AMT3C]
WHEN [COD4C] IN ('J','030') THEN [AMT4C]
WHEN [COD5C] IN ('J','030') THEN [AMT5C]
WHEN [COD6C] IN ('J','030') THEN [AMT6C]
WHEN [COD7C] IN ('J','030') THEN [AMT7C]
WHEN [COD8C] IN ('J','030') THEN [AMT8C]
WHEN [COD9C] IN ('J','030') THEN [AMT9C]
WHEN [COD10C] IN ('J','030') THEN [AMT10C]
WHEN [COD11C] IN ('J','030') THEN [AMT11C]
WHEN [COD12C] IN ('J','030') THEN [AMT12C]
WHEN [COD13C] IN ('J','030') THEN [AMT13C]
WHEN [COD14C] IN ('J','030') THEN [AMT14C]
WHEN [COD15C] IN ('J','030') THEN [AMT15C] ELSE 0 END AS [CLERICAL_HRS]
,CASE WHEN [COD1C] IN ('K','040') THEN [AMT1C]
WHEN [COD2C] IN ('K','040') THEN [AMT2C]
WHEN [COD3C] IN ('K','040') THEN [AMT3C]
WHEN [COD4C] IN ('K','040') THEN [AMT4C]
WHEN [COD5C] IN ('K','040') THEN [AMT5C]
WHEN [COD6C] IN ('K','040') THEN [AMT6C]
WHEN [COD7C] IN ('K','040') THEN [AMT7C]
WHEN [COD8C] IN ('K','040') THEN [AMT8C]
WHEN [COD9C] IN ('K','040') THEN [AMT9C]
WHEN [COD10C] IN ('K','040') THEN [AMT10C]
WHEN [COD11C] IN ('K','040') THEN [AMT11C]
WHEN [COD12C] IN ('K','040') THEN [AMT12C]
WHEN [COD13C] IN ('K','040') THEN [AMT13C]
WHEN [COD14C] IN ('K','040') THEN [AMT14C]
WHEN [COD15C] IN ('K','040') THEN [AMT15C] ELSE 0 END AS [FORKLIFT_HRS]
,CASE WHEN [COD1C] IN ('N','440') THEN [AMT1C]
WHEN [COD2C] IN ('N','440') THEN [AMT2C]
WHEN [COD3C] IN ('N','440') THEN [AMT3C]
WHEN [COD4C] IN ('N','440') THEN [AMT4C]
WHEN [COD5C] IN ('N','440') THEN [AMT5C]
WHEN [COD6C] IN ('N','440') THEN [AMT6C]
WHEN [COD7C] IN ('N','440') THEN [AMT7C]
WHEN [COD8C] IN ('N','440') THEN [AMT8C]
WHEN [COD9C] IN ('N','440') THEN [AMT9C]
WHEN [COD10C] IN ('N','440') THEN [AMT10C]
WHEN [COD11C] IN ('N','440') THEN [AMT11C]
WHEN [COD12C] IN ('N','440') THEN [AMT12C]
WHEN [COD13C] IN ('N','440') THEN [AMT13C]
WHEN [COD14C] IN ('N','440') THEN [AMT14C]
WHEN [COD15C] IN ('N','440') THEN [AMT15C] ELSE 0 END AS [ORDER_CHANGES]
,CASE WHEN [COD1C] IN ('Q','210') THEN [AMT1C]
WHEN [COD2C] IN ('Q','210') THEN [AMT2C]
WHEN [COD3C] IN ('Q','210') THEN [AMT3C]
WHEN [COD4C] IN ('Q','210') THEN [AMT4C]
WHEN [COD5C] IN ('Q','210') THEN [AMT5C]
WHEN [COD6C] IN ('Q','210') THEN [AMT6C]
WHEN [COD7C] IN ('Q','210') THEN [AMT7C]
WHEN [COD8C] IN ('Q','210') THEN [AMT8C]
WHEN [COD9C] IN ('Q','210') THEN [AMT9C]
WHEN [COD10C] IN ('Q','210') THEN [AMT10C]
WHEN [COD11C] IN ('Q','210') THEN [AMT11C]
WHEN [COD12C] IN ('Q','210') THEN [AMT12C]
WHEN [COD13C] IN ('Q','210') THEN [AMT13C]
WHEN [COD14C] IN ('Q','210') THEN [AMT14C]
WHEN [COD15C] IN ('Q','210') THEN [AMT15C] ELSE 0 END AS [PACKLIST]
,CASE WHEN [COD1C] IN ('T','540') THEN [AMT1C]
WHEN [COD2C] IN ('T','540') THEN [AMT2C]
WHEN [COD3C] IN ('T','540') THEN [AMT3C]
WHEN [COD4C] IN ('T','540') THEN [AMT4C]
WHEN [COD5C] IN ('T','540') THEN [AMT5C]
WHEN [COD6C] IN ('T','540') THEN [AMT6C]
WHEN [COD7C] IN ('T','540') THEN [AMT7C]
WHEN [COD8C] IN ('T','540') THEN [AMT8C]
WHEN [COD9C] IN ('T','540') THEN [AMT9C]
WHEN [COD10C] IN ('T','540') THEN [AMT10C]
WHEN [COD11C] IN ('T','540') THEN [AMT11C]
WHEN [COD12C] IN ('T','540') THEN [AMT12C]
WHEN [COD13C] IN ('T','540') THEN [AMT13C]
WHEN [COD14C] IN ('T','540') THEN [AMT14C]
WHEN [COD15C] IN ('T','540') THEN [AMT15C] ELSE 0 END AS [LOT_SERIAL_NUM]
,CASE WHEN [COD1C] IN ('U','330') THEN [AMT1C]
WHEN [COD2C] IN ('U','330') THEN [AMT2C]
WHEN [COD3C] IN ('U','330') THEN [AMT3C]
WHEN [COD4C] IN ('U','330') THEN [AMT4C]
WHEN [COD5C] IN ('U','330') THEN [AMT5C]
WHEN [COD6C] IN ('U','330') THEN [AMT6C]
WHEN [COD7C] IN ('U','330') THEN [AMT7C]
WHEN [COD8C] IN ('U','330') THEN [AMT8C]
WHEN [COD9C] IN ('U','330') THEN [AMT9C]
WHEN [COD10C] IN ('U','330') THEN [AMT10C]
WHEN [COD11C] IN ('U','330') THEN [AMT11C]
WHEN [COD12C] IN ('U','330') THEN [AMT12C]
WHEN [COD13C] IN ('U','330') THEN [AMT13C]
WHEN [COD14C] IN ('U','330') THEN [AMT14C]
WHEN [COD15C] IN ('U','330') THEN [AMT15C] ELSE 0 END AS [UPS_LABEL_CHANGES]
,CASE WHEN [COD1C] IN ('X','150') THEN [AMT1C]
WHEN [COD2C] IN ('X','150') THEN [AMT2C]
WHEN [COD3C] IN ('X','150') THEN [AMT3C]
WHEN [COD4C] IN ('X','150') THEN [AMT4C]
WHEN [COD5C] IN ('X','150') THEN [AMT5C]
WHEN [COD6C] IN ('X','150') THEN [AMT6C]
WHEN [COD7C] IN ('X','150') THEN [AMT7C]
WHEN [COD8C] IN ('X','150') THEN [AMT8C]
WHEN [COD9C] IN ('X','150') THEN [AMT9C]
WHEN [COD10C] IN ('X','150') THEN [AMT10C]
WHEN [COD11C] IN ('X','150') THEN [AMT11C]
WHEN [COD12C] IN ('X','150') THEN [AMT12C]
WHEN [COD13C] IN ('X','150') THEN [AMT13C]
WHEN [COD14C] IN ('X','150') THEN [AMT14C]
WHEN [COD15C] IN ('X','150') THEN [AMT15C] ELSE 0 END AS [XTRA_CARTON]
,CASE WHEN [COD1C] IN ('G','140') THEN [AMT1C]
WHEN [COD2C] IN ('G','140') THEN [AMT2C]
WHEN [COD3C] IN ('G','140') THEN [AMT3C]
WHEN [COD4C] IN ('G','140') THEN [AMT4C]
WHEN [COD5C] IN ('G','140') THEN [AMT5C]
WHEN [COD6C] IN ('G','140') THEN [AMT6C]
WHEN [COD7C] IN ('G','140') THEN [AMT7C]
WHEN [COD8C] IN ('G','140') THEN [AMT8C]
WHEN [COD9C] IN ('G','140') THEN [AMT9C]
WHEN [COD10C] IN ('G','140') THEN [AMT10C]
WHEN [COD11C] IN ('G','140') THEN [AMT11C]
WHEN [COD12C] IN ('G','140') THEN [AMT12C]
WHEN [COD13C] IN ('G','140') THEN [AMT13C]
WHEN [COD14C] IN ('G','140') THEN [AMT14C]
WHEN [COD15C] IN ('G','140') THEN [AMT15C] ELSE 0 END AS [MASTER_PACK]
,CASE WHEN [COD1C] IN ('V','220') THEN [AMT1C]
WHEN [COD2C] IN ('V','220') THEN [AMT2C]
WHEN [COD3C] IN ('V','220') THEN [AMT3C]
WHEN [COD4C] IN ('V','220') THEN [AMT4C]
WHEN [COD5C] IN ('V','220') THEN [AMT5C]
WHEN [COD6C] IN ('V','220') THEN [AMT6C]
WHEN [COD7C] IN ('V','220') THEN [AMT7C]
WHEN [COD8C] IN ('V','220') THEN [AMT8C]
WHEN [COD9C] IN ('V','220') THEN [AMT9C]
WHEN [COD10C] IN ('V','220') THEN [AMT10C]
WHEN [COD11C] IN ('V','220') THEN [AMT11C]
WHEN [COD12C] IN ('V','220') THEN [AMT12C]
WHEN [COD13C] IN ('V','220') THEN [AMT13C]
WHEN [COD14C] IN ('V','220') THEN [AMT14C]
WHEN [COD15C] IN ('V','220') THEN [AMT15C] ELSE 0 END AS [MSDS_SHEETS]
,CASE WHEN [COD1C] IN ('W','230') THEN [AMT1C]
WHEN [COD2C] IN ('W','230') THEN [AMT2C]
WHEN [COD3C] IN ('W','230') THEN [AMT3C]
WHEN [COD4C] IN ('W','230') THEN [AMT4C]
WHEN [COD5C] IN ('W','230') THEN [AMT5C]
WHEN [COD6C] IN ('W','230') THEN [AMT6C]
WHEN [COD7C] IN ('W','230') THEN [AMT7C]
WHEN [COD8C] IN ('W','230') THEN [AMT8C]
WHEN [COD9C] IN ('W','230') THEN [AMT9C]
WHEN [COD10C] IN ('W','230') THEN [AMT10C]
WHEN [COD11C] IN ('W','230') THEN [AMT11C]
WHEN [COD12C] IN ('W','230') THEN [AMT12C]
WHEN [COD13C] IN ('W','230') THEN [AMT13C]
WHEN [COD14C] IN ('W','230') THEN [AMT14C]
WHEN [COD15C] IN ('W','230') THEN [AMT15C] ELSE 0 END AS [COA_SHEETS]
,CASE WHEN [COD1C] IN ('Y','260') THEN [AMT1C]
WHEN [COD2C] IN ('Y','260') THEN [AMT2C]
WHEN [COD3C] IN ('Y','260') THEN [AMT3C]
WHEN [COD4C] IN ('Y','260') THEN [AMT4C]
WHEN [COD5C] IN ('Y','260') THEN [AMT5C]
WHEN [COD6C] IN ('Y','260') THEN [AMT6C]
WHEN [COD7C] IN ('Y','260') THEN [AMT7C]
WHEN [COD8C] IN ('Y','260') THEN [AMT8C]
WHEN [COD9C] IN ('Y','260') THEN [AMT9C]
WHEN [COD10C] IN ('Y','260') THEN [AMT10C]
WHEN [COD11C] IN ('Y','260') THEN [AMT11C]
WHEN [COD12C] IN ('Y','260') THEN [AMT12C]
WHEN [COD13C] IN ('Y','260') THEN [AMT13C]
WHEN [COD14C] IN ('Y','260') THEN [AMT14C]
WHEN [COD15C] IN ('Y','260') THEN [AMT15C] ELSE 0 END AS [PLACARDS]
,CASE WHEN [COD1C] IN ('Z','490') THEN [AMT1C]
WHEN [COD2C] IN ('Z','490') THEN [AMT2C]
WHEN [COD3C] IN ('Z','490') THEN [AMT3C]
WHEN [COD4C] IN ('Z','490') THEN [AMT4C]
WHEN [COD5C] IN ('Z','490') THEN [AMT5C]
WHEN [COD6C] IN ('Z','490') THEN [AMT6C]
WHEN [COD7C] IN ('Z','490') THEN [AMT7C]
WHEN [COD8C] IN ('Z','490') THEN [AMT8C]
WHEN [COD9C] IN ('Z','490') THEN [AMT9C]
WHEN [COD10C] IN ('Z','490') THEN [AMT10C]
WHEN [COD11C] IN ('Z','490') THEN [AMT11C]
WHEN [COD12C] IN ('Z','490') THEN [AMT12C]
WHEN [COD13C] IN ('Z','490') THEN [AMT13C]
WHEN [COD14C] IN ('Z','490') THEN [AMT14C]
WHEN [COD15C] IN ('Z','490') THEN [AMT15C] ELSE 0 END AS [UPS_FEDEX_LINE]
,CASE WHEN [COD1C] IN ('0','100') THEN [AMT1C]
WHEN [COD2C] IN ('0','100') THEN [AMT2C]
WHEN [COD3C] IN ('0','100') THEN [AMT3C]
WHEN [COD4C] IN ('0','100') THEN [AMT4C]
WHEN [COD5C] IN ('0','100') THEN [AMT5C]
WHEN [COD6C] IN ('0','100') THEN [AMT6C]
WHEN [COD7C] IN ('0','100') THEN [AMT7C]
WHEN [COD8C] IN ('0','100') THEN [AMT8C]
WHEN [COD9C] IN ('0','100') THEN [AMT9C]
WHEN [COD10C] IN ('0','100') THEN [AMT10C]
WHEN [COD11C] IN ('0','100') THEN [AMT11C]
WHEN [COD12C] IN ('0','100') THEN [AMT12C]
WHEN [COD13C] IN ('0','100') THEN [AMT13C]
WHEN [COD14C] IN ('0','100') THEN [AMT14C]
WHEN [COD15C] IN ('0','100') THEN [AMT15C] ELSE 0 END AS [PCK_PLETS]
,CASE WHEN [COD1C] IN ('1','110') THEN [AMT1C]
WHEN [COD2C] IN ('1','110') THEN [AMT2C]
WHEN [COD3C] IN ('1','110') THEN [AMT3C]
WHEN [COD4C] IN ('1','110') THEN [AMT4C]
WHEN [COD5C] IN ('1','110') THEN [AMT5C]
WHEN [COD6C] IN ('1','110') THEN [AMT6C]
WHEN [COD7C] IN ('1','110') THEN [AMT7C]
WHEN [COD8C] IN ('1','110') THEN [AMT8C]
WHEN [COD9C] IN ('1','110') THEN [AMT9C]
WHEN [COD10C] IN ('1','110') THEN [AMT10C]
WHEN [COD11C] IN ('1','110') THEN [AMT11C]
WHEN [COD12C] IN ('1','110') THEN [AMT12C]
WHEN [COD13C] IN ('1','110') THEN [AMT13C]
WHEN [COD14C] IN ('1','110') THEN [AMT14C]
WHEN [COD15C] IN ('1','110') THEN [AMT15C] ELSE 0 END AS [PCK_ECH]
,CASE WHEN [COD1C] IN ('2','300') THEN [AMT1C]
WHEN [COD2C] IN ('2','300') THEN [AMT2C]
WHEN [COD3C] IN ('2','300') THEN [AMT3C]
WHEN [COD4C] IN ('2','300') THEN [AMT4C]
WHEN [COD5C] IN ('2','300') THEN [AMT5C]
WHEN [COD6C] IN ('2','300') THEN [AMT6C]
WHEN [COD7C] IN ('2','300') THEN [AMT7C]
WHEN [COD8C] IN ('2','300') THEN [AMT8C]
WHEN [COD9C] IN ('2','300') THEN [AMT9C]
WHEN [COD10C] IN ('2','300') THEN [AMT10C]
WHEN [COD11C] IN ('2','300') THEN [AMT11C]
WHEN [COD12C] IN ('2','300') THEN [AMT12C]
WHEN [COD13C] IN ('2','300') THEN [AMT13C]
WHEN [COD14C] IN ('2','300') THEN [AMT14C]
WHEN [COD15C] IN ('2','300') THEN [AMT15C] ELSE 0 END AS [ITM_LBLS]
,CASE WHEN [COD1C]='460' THEN [AMT1C]
WHEN [COD2C]='460' THEN [AMT2C]
WHEN [COD3C]='460' THEN [AMT3C]
WHEN [COD4C]='460' THEN [AMT4C]
WHEN [COD5C]='460' THEN [AMT5C]
WHEN [COD6C]='460' THEN [AMT6C]
WHEN [COD7C]='460' THEN [AMT7C]
WHEN [COD8C]='460' THEN [AMT8C]
WHEN [COD9C]='460' THEN [AMT9C]
WHEN [COD10C]='460' THEN [AMT10C]
WHEN [COD11C]='460' THEN [AMT11C]
WHEN [COD12C]='460' THEN [AMT12C]
WHEN [COD13C]='460' THEN [AMT13C]
WHEN [COD14C]='460' THEN [AMT14C]
WHEN [COD15C]='460' THEN [AMT15C] ELSE 0 END AS [CANCEL_ORD]
,CASE WHEN [COD1C]='470' THEN [AMT1C]
WHEN [COD2C]='470' THEN [AMT2C]
WHEN [COD3C]='470' THEN [AMT3C]
WHEN [COD4C]='470' THEN [AMT4C]
WHEN [COD5C]='470' THEN [AMT5C]
WHEN [COD6C]='470' THEN [AMT6C]
WHEN [COD7C]='470' THEN [AMT7C]
WHEN [COD8C]='470' THEN [AMT8C]
WHEN [COD9C]='470' THEN [AMT9C]
WHEN [COD10C]='470' THEN [AMT10C]
WHEN [COD11C]='470' THEN [AMT11C]
WHEN [COD12C]='470' THEN [AMT12C]
WHEN [COD13C]='470' THEN [AMT13C]
WHEN [COD14C]='470' THEN [AMT14C]
WHEN [COD15C]='470' THEN [AMT15C] ELSE 0 END AS [MISSED_NO_APPT]
,CASE WHEN [COD1C]='480' THEN [AMT1C]
WHEN [COD2C]='480' THEN [AMT2C]
WHEN [COD3C]='480' THEN [AMT3C]
WHEN [COD4C]='480' THEN [AMT4C]
WHEN [COD5C]='480' THEN [AMT5C]
WHEN [COD6C]='480' THEN [AMT6C]
WHEN [COD7C]='480' THEN [AMT7C]
WHEN [COD8C]='480' THEN [AMT8C]
WHEN [COD9C]='480' THEN [AMT9C]
WHEN [COD10C]='480' THEN [AMT10C]
WHEN [COD11C]='480' THEN [AMT11C]
WHEN [COD12C]='480' THEN [AMT12C]
WHEN [COD13C]='480' THEN [AMT13C]
WHEN [COD14C]='480' THEN [AMT14C]
WHEN [COD15C]='480' THEN [AMT15C] ELSE 0 END AS [FAX_EMAIL]
,CASE WHEN [COD1C]='530' THEN [AMT1C]
WHEN [COD2C]='530' THEN [AMT2C]
WHEN [COD3C]='530' THEN [AMT3C]
WHEN [COD4C]='530' THEN [AMT4C]
WHEN [COD5C]='530' THEN [AMT5C]
WHEN [COD6C]='530' THEN [AMT6C]
WHEN [COD7C]='530' THEN [AMT7C]
WHEN [COD8C]='530' THEN [AMT8C]
WHEN [COD9C]='530' THEN [AMT9C]
WHEN [COD10C]='530' THEN [AMT10C]
WHEN [COD11C]='530' THEN [AMT11C]
WHEN [COD12C]='530' THEN [AMT12C]
WHEN [COD13C]='530' THEN [AMT13C]
WHEN [COD14C]='530' THEN [AMT14C]
WHEN [COD15C]='530' THEN [AMT15C] ELSE 0 END AS [RAIL]
,CASE WHEN [COD1C]='340' THEN [AMT1C]
WHEN [COD2C]='340' THEN [AMT2C]
WHEN [COD3C]='340' THEN [AMT3C]
WHEN [COD4C]='340' THEN [AMT4C]
WHEN [COD5C]='340' THEN [AMT5C]
WHEN [COD6C]='340' THEN [AMT6C]
WHEN [COD7C]='340' THEN [AMT7C]
WHEN [COD8C]='340' THEN [AMT8C]
WHEN [COD9C]='340' THEN [AMT9C]
WHEN [COD10C]='340' THEN [AMT10C]
WHEN [COD11C]='340' THEN [AMT11C]
WHEN [COD12C]='340' THEN [AMT12C]
WHEN [COD13C]='340' THEN [AMT13C]
WHEN [COD14C]='340' THEN [AMT14C]
WHEN [COD15C]='340' THEN [AMT15C] ELSE 0 END AS [BCODE_DSGN]
,CASE WHEN [COD1C]='350' THEN [AMT1C]
WHEN [COD2C]='350' THEN [AMT2C]
WHEN [COD3C]='350' THEN [AMT3C]
WHEN [COD4C]='350' THEN [AMT4C]
WHEN [COD5C]='350' THEN [AMT5C]
WHEN [COD6C]='350' THEN [AMT6C]
WHEN [COD7C]='350' THEN [AMT7C]
WHEN [COD8C]='350' THEN [AMT8C]
WHEN [COD9C]='350' THEN [AMT9C]
WHEN [COD10C]='350' THEN [AMT10C]
WHEN [COD11C]='350' THEN [AMT11C]
WHEN [COD12C]='350' THEN [AMT12C]
WHEN [COD13C]='350' THEN [AMT13C]
WHEN [COD14C]='350' THEN [AMT14C]
WHEN [COD15C]='350' THEN [AMT15C] ELSE 0 END AS [BCODE_MADE]
,CASE WHEN [COD1C]='360' THEN [AMT1C]
WHEN [COD2C]='360' THEN [AMT2C]
WHEN [COD3C]='360' THEN [AMT3C]
WHEN [COD4C]='360' THEN [AMT4C]
WHEN [COD5C]='360' THEN [AMT5C]
WHEN [COD6C]='360' THEN [AMT6C]
WHEN [COD7C]='360' THEN [AMT7C]
WHEN [COD8C]='360' THEN [AMT8C]
WHEN [COD9C]='360' THEN [AMT9C]
WHEN [COD10C]='360' THEN [AMT10C]
WHEN [COD11C]='360' THEN [AMT11C]
WHEN [COD12C]='360' THEN [AMT12C]
WHEN [COD13C]='360' THEN [AMT13C]
WHEN [COD14C]='360' THEN [AMT14C]
WHEN [COD15C]='360' THEN [AMT15C] ELSE 0 END AS [PHOTO]
,CASE WHEN [COD1C]='370' THEN [AMT1C]
WHEN [COD2C]='370' THEN [AMT2C]
WHEN [COD3C]='370' THEN [AMT3C]
WHEN [COD4C]='370' THEN [AMT4C]
WHEN [COD5C]='370' THEN [AMT5C]
WHEN [COD6C]='370' THEN [AMT6C]
WHEN [COD7C]='370' THEN [AMT7C]
WHEN [COD8C]='370' THEN [AMT8C]
WHEN [COD9C]='370' THEN [AMT9C]
WHEN [COD10C]='370' THEN [AMT10C]
WHEN [COD11C]='370' THEN [AMT11C]
WHEN [COD12C]='370' THEN [AMT12C]
WHEN [COD13C]='370' THEN [AMT13C]
WHEN [COD14C]='370' THEN [AMT14C]
WHEN [COD15C]='370' THEN [AMT15C] ELSE 0 END AS [MARK_TAG]
,CASE WHEN [COD1C]='280' THEN [AMT1C]
WHEN [COD2C]='280' THEN [AMT2C]
WHEN [COD3C]='280' THEN [AMT3C]
WHEN [COD4C]='280' THEN [AMT4C]
WHEN [COD5C]='280' THEN [AMT5C]
WHEN [COD6C]='280' THEN [AMT6C]
WHEN [COD7C]='280' THEN [AMT7C]
WHEN [COD8C]='280' THEN [AMT8C]
WHEN [COD9C]='280' THEN [AMT9C]
WHEN [COD10C]='280' THEN [AMT10C]
WHEN [COD11C]='280' THEN [AMT11C]
WHEN [COD12C]='280' THEN [AMT12C]
WHEN [COD13C]='280' THEN [AMT13C]
WHEN [COD14C]='280' THEN [AMT14C]
WHEN [COD15C]='280' THEN [AMT15C] ELSE 0 END AS [DOC_PROC]
,CASE WHEN [COD1C]='270' THEN [AMT1C]
WHEN [COD2C]='270' THEN [AMT2C]
WHEN [COD3C]='270' THEN [AMT3C]
WHEN [COD4C]='270' THEN [AMT4C]
WHEN [COD5C]='270' THEN [AMT5C]
WHEN [COD6C]='270' THEN [AMT6C]
WHEN [COD7C]='270' THEN [AMT7C]
WHEN [COD8C]='270' THEN [AMT8C]
WHEN [COD9C]='270' THEN [AMT9C]
WHEN [COD10C]='270' THEN [AMT10C]
WHEN [COD11C]='270' THEN [AMT11C]
WHEN [COD12C]='270' THEN [AMT12C]
WHEN [COD13C]='270' THEN [AMT13C]
WHEN [COD14C]='270' THEN [AMT14C]
WHEN [COD15C]='270' THEN [AMT15C] ELSE 0 END AS [SLIP_SHTS]
,CASE WHEN [COD1C]='250' THEN [AMT1C]
WHEN [COD2C]='250' THEN [AMT2C]
WHEN [COD3C]='250' THEN [AMT3C]
WHEN [COD4C]='250' THEN [AMT4C]
WHEN [COD5C]='250' THEN [AMT5C]
WHEN [COD6C]='250' THEN [AMT6C]
WHEN [COD7C]='250' THEN [AMT7C]
WHEN [COD8C]='250' THEN [AMT8C]
WHEN [COD9C]='250' THEN [AMT9C]
WHEN [COD10C]='250' THEN [AMT10C]
WHEN [COD11C]='250' THEN [AMT11C]
WHEN [COD12C]='250' THEN [AMT12C]
WHEN [COD13C]='250' THEN [AMT13C]
WHEN [COD14C]='250' THEN [AMT14C]
WHEN [COD15C]='250' THEN [AMT15C] ELSE 0 END AS [HAZMAT_SHP]
,CASE WHEN [COD1C]='240' THEN [AMT1C]
WHEN [COD2C]='240' THEN [AMT2C]
WHEN [COD3C]='240' THEN [AMT3C]
WHEN [COD4C]='240' THEN [AMT4C]
WHEN [COD5C]='240' THEN [AMT5C]
WHEN [COD6C]='240' THEN [AMT6C]
WHEN [COD7C]='240' THEN [AMT7C]
WHEN [COD8C]='240' THEN [AMT8C]
WHEN [COD9C]='240' THEN [AMT9C]
WHEN [COD10C]='240' THEN [AMT10C]
WHEN [COD11C]='240' THEN [AMT11C]
WHEN [COD12C]='240' THEN [AMT12C]
WHEN [COD13C]='240' THEN [AMT13C]
WHEN [COD14C]='240' THEN [AMT14C]
WHEN [COD15C]='240' THEN [AMT15C] ELSE 0 END AS [IATA_IMDG]
,CASE WHEN [COD1C]='120' THEN [AMT1C]
WHEN [COD2C]='120' THEN [AMT2C]
WHEN [COD3C]='120' THEN [AMT3C]
WHEN [COD4C]='120' THEN [AMT4C]
WHEN [COD5C]='120' THEN [AMT5C]
WHEN [COD6C]='120' THEN [AMT6C]
WHEN [COD7C]='120' THEN [AMT7C]
WHEN [COD8C]='120' THEN [AMT8C]
WHEN [COD9C]='120' THEN [AMT9C]
WHEN [COD10C]='120' THEN [AMT10C]
WHEN [COD11C]='120' THEN [AMT11C]
WHEN [COD12C]='120' THEN [AMT12C]
WHEN [COD13C]='120' THEN [AMT13C]
WHEN [COD14C]='120' THEN [AMT14C]
WHEN [COD15C]='120' THEN [AMT15C] ELSE 0 END AS [PCK_CASES]
,CASE WHEN [COD1C]='160' THEN [AMT1C]
WHEN [COD2C]='160' THEN [AMT2C]
WHEN [COD3C]='160' THEN [AMT3C]
WHEN [COD4C]='160' THEN [AMT4C]
WHEN [COD5C]='160' THEN [AMT5C]
WHEN [COD6C]='160' THEN [AMT6C]
WHEN [COD7C]='160' THEN [AMT7C]
WHEN [COD8C]='160' THEN [AMT8C]
WHEN [COD9C]='160' THEN [AMT9C]
WHEN [COD10C]='160' THEN [AMT10C]
WHEN [COD11C]='160' THEN [AMT11C]
WHEN [COD12C]='160' THEN [AMT12C]
WHEN [COD13C]='160' THEN [AMT13C]
WHEN [COD14C]='160' THEN [AMT14C]
WHEN [COD15C]='160' THEN [AMT15C] ELSE 0 END AS [OUT_HAND_PLTS]
,CASE WHEN [COD1C]='515' THEN [AMT1C]
WHEN [COD2C]='515' THEN [AMT2C]
WHEN [COD3C]='515' THEN [AMT3C]
WHEN [COD4C]='515' THEN [AMT4C]
WHEN [COD5C]='515' THEN [AMT5C]
WHEN [COD6C]='515' THEN [AMT6C]
WHEN [COD7C]='515' THEN [AMT7C]
WHEN [COD8C]='515' THEN [AMT8C]
WHEN [COD9C]='515' THEN [AMT9C]
WHEN [COD10C]='515' THEN [AMT10C]
WHEN [COD11C]='515' THEN [AMT11C]
WHEN [COD12C]='515' THEN [AMT12C]
WHEN [COD13C]='515' THEN [AMT13C]
WHEN [COD14C]='515' THEN [AMT14C]
WHEN [COD15C]='515' THEN [AMT15C] ELSE 0 END AS [CORR_PLTS]
,CASE WHEN [COD1C]='155' THEN [AMT1C]
WHEN [COD2C]='155' THEN [AMT2C]
WHEN [COD3C]='155' THEN [AMT3C]
WHEN [COD4C]='155' THEN [AMT4C]
WHEN [COD5C]='155' THEN [AMT5C]
WHEN [COD6C]='155' THEN [AMT6C]
WHEN [COD7C]='155' THEN [AMT7C]
WHEN [COD8C]='155' THEN [AMT8C]
WHEN [COD9C]='155' THEN [AMT9C]
WHEN [COD10C]='155' THEN [AMT10C]
WHEN [COD11C]='155' THEN [AMT11C]
WHEN [COD12C]='155' THEN [AMT12C]
WHEN [COD13C]='155' THEN [AMT13C]
WHEN [COD14C]='155' THEN [AMT14C]
WHEN [COD15C]='155' THEN [AMT15C] ELSE 0 END AS [FEDX_SHP_CTN]
,CASE WHEN [COD1C]='550' THEN [AMT1C]
WHEN [COD2C]='550' THEN [AMT2C]
WHEN [COD3C]='550' THEN [AMT3C]
WHEN [COD4C]='550' THEN [AMT4C]
WHEN [COD5C]='550' THEN [AMT5C]
WHEN [COD6C]='550' THEN [AMT6C]
WHEN [COD7C]='550' THEN [AMT7C]
WHEN [COD8C]='550' THEN [AMT8C]
WHEN [COD9C]='550' THEN [AMT9C]
WHEN [COD10C]='550' THEN [AMT10C]
WHEN [COD11C]='550' THEN [AMT11C]
WHEN [COD12C]='550' THEN [AMT12C]
WHEN [COD13C]='550' THEN [AMT13C]
WHEN [COD14C]='550' THEN [AMT14C]
WHEN [COD15C]='550' THEN [AMT15C] ELSE 0 END AS [REPLACE_CORD]
,CASE WHEN [COD1C]='580' THEN [AMT1C]
WHEN [COD2C]='580' THEN [AMT2C]
WHEN [COD3C]='580' THEN [AMT3C]
WHEN [COD4C]='580' THEN [AMT4C]
WHEN [COD5C]='580' THEN [AMT5C]
WHEN [COD6C]='580' THEN [AMT6C]
WHEN [COD7C]='580' THEN [AMT7C]
WHEN [COD8C]='580' THEN [AMT8C]
WHEN [COD9C]='580' THEN [AMT9C]
WHEN [COD10C]='580' THEN [AMT10C]
WHEN [COD11C]='580' THEN [AMT11C]
WHEN [COD12C]='580' THEN [AMT12C]
WHEN [COD13C]='580' THEN [AMT13C]
WHEN [COD14C]='580' THEN [AMT14C]
WHEN [COD15C]='580' THEN [AMT15C] ELSE 0 END AS [LKER_8162]
,CASE WHEN [COD1C]='581' THEN [AMT1C]
WHEN [COD2C]='581' THEN [AMT2C]
WHEN [COD3C]='581' THEN [AMT3C]
WHEN [COD4C]='581' THEN [AMT4C]
WHEN [COD5C]='581' THEN [AMT5C]
WHEN [COD6C]='581' THEN [AMT6C]
WHEN [COD7C]='581' THEN [AMT7C]
WHEN [COD8C]='581' THEN [AMT8C]
WHEN [COD9C]='581' THEN [AMT9C]
WHEN [COD10C]='581' THEN [AMT10C]
WHEN [COD11C]='581' THEN [AMT11C]
WHEN [COD12C]='581' THEN [AMT12C]
WHEN [COD13C]='581' THEN [AMT13C]
WHEN [COD14C]='581' THEN [AMT14C]
WHEN [COD15C]='581' THEN [AMT15C] ELSE 0 END AS [LKER_8162C]
,CASE WHEN [COD1C]='582' THEN [AMT1C]
WHEN [COD2C]='582' THEN [AMT2C]
WHEN [COD3C]='582' THEN [AMT3C]
WHEN [COD4C]='582' THEN [AMT4C]
WHEN [COD5C]='582' THEN [AMT5C]
WHEN [COD6C]='582' THEN [AMT6C]
WHEN [COD7C]='582' THEN [AMT7C]
WHEN [COD8C]='582' THEN [AMT8C]
WHEN [COD9C]='582' THEN [AMT9C]
WHEN [COD10C]='582' THEN [AMT10C]
WHEN [COD11C]='582' THEN [AMT11C]
WHEN [COD12C]='582' THEN [AMT12C]
WHEN [COD13C]='582' THEN [AMT13C]
WHEN [COD14C]='582' THEN [AMT14C]
WHEN [COD15C]='582' THEN [AMT15C] ELSE 0 END AS [LKER_8136]
,CASE WHEN [COD1C]='583' THEN [AMT1C]
WHEN [COD2C]='583' THEN [AMT2C]
WHEN [COD3C]='583' THEN [AMT3C]
WHEN [COD4C]='583' THEN [AMT4C]
WHEN [COD5C]='583' THEN [AMT5C]
WHEN [COD6C]='583' THEN [AMT6C]
WHEN [COD7C]='583' THEN [AMT7C]
WHEN [COD8C]='583' THEN [AMT8C]
WHEN [COD9C]='583' THEN [AMT9C]
WHEN [COD10C]='583' THEN [AMT10C]
WHEN [COD11C]='583' THEN [AMT11C]
WHEN [COD12C]='583' THEN [AMT12C]
WHEN [COD13C]='583' THEN [AMT13C]
WHEN [COD14C]='583' THEN [AMT14C]
WHEN [COD15C]='583' THEN [AMT15C] ELSE 0 END AS [LKER_8189]
,CASE WHEN [COD1C]='584' THEN [AMT1C]
WHEN [COD2C]='584' THEN [AMT2C]
WHEN [COD3C]='584' THEN [AMT3C]
WHEN [COD4C]='584' THEN [AMT4C]
WHEN [COD5C]='584' THEN [AMT5C]
WHEN [COD6C]='584' THEN [AMT6C]
WHEN [COD7C]='584' THEN [AMT7C]
WHEN [COD8C]='584' THEN [AMT8C]
WHEN [COD9C]='584' THEN [AMT9C]
WHEN [COD10C]='584' THEN [AMT10C]
WHEN [COD11C]='584' THEN [AMT11C]
WHEN [COD12C]='584' THEN [AMT12C]
WHEN [COD13C]='584' THEN [AMT13C]
WHEN [COD14C]='584' THEN [AMT14C]
WHEN [COD15C]='584' THEN [AMT15C] ELSE 0 END AS [LKER_8189C]
,CASE WHEN [COD1C]='585' THEN [AMT1C]
WHEN [COD2C]='585' THEN [AMT2C]
WHEN [COD3C]='585' THEN [AMT3C]
WHEN [COD4C]='585' THEN [AMT4C]
WHEN [COD5C]='585' THEN [AMT5C]
WHEN [COD6C]='585' THEN [AMT6C]
WHEN [COD7C]='585' THEN [AMT7C]
WHEN [COD8C]='585' THEN [AMT8C]
WHEN [COD9C]='585' THEN [AMT9C]
WHEN [COD10C]='585' THEN [AMT10C]
WHEN [COD11C]='585' THEN [AMT11C]
WHEN [COD12C]='585' THEN [AMT12C]
WHEN [COD13C]='585' THEN [AMT13C]
WHEN [COD14C]='585' THEN [AMT14C]
WHEN [COD15C]='585' THEN [AMT15C] ELSE 0 END AS [LKER_8225]
,CASE WHEN [COD1C]='586' THEN [AMT1C]
WHEN [COD2C]='586' THEN [AMT2C]
WHEN [COD3C]='586' THEN [AMT3C]
WHEN [COD4C]='586' THEN [AMT4C]
WHEN [COD5C]='586' THEN [AMT5C]
WHEN [COD6C]='586' THEN [AMT6C]
WHEN [COD7C]='586' THEN [AMT7C]
WHEN [COD8C]='586' THEN [AMT8C]
WHEN [COD9C]='586' THEN [AMT9C]
WHEN [COD10C]='586' THEN [AMT10C]
WHEN [COD11C]='586' THEN [AMT11C]
WHEN [COD12C]='586' THEN [AMT12C]
WHEN [COD13C]='586' THEN [AMT13C]
WHEN [COD14C]='586' THEN [AMT14C]
WHEN [COD15C]='586' THEN [AMT15C] ELSE 0 END AS [LKER_8225C]
,CASE WHEN [COD1C]='587' THEN [AMT1C]
WHEN [COD2C]='587' THEN [AMT2C]
WHEN [COD3C]='587' THEN [AMT3C]
WHEN [COD4C]='587' THEN [AMT4C]
WHEN [COD5C]='587' THEN [AMT5C]
WHEN [COD6C]='587' THEN [AMT6C]
WHEN [COD7C]='587' THEN [AMT7C]
WHEN [COD8C]='587' THEN [AMT8C]
WHEN [COD9C]='587' THEN [AMT9C]
WHEN [COD10C]='587' THEN [AMT10C]
WHEN [COD11C]='587' THEN [AMT11C]
WHEN [COD12C]='587' THEN [AMT12C]
WHEN [COD13C]='587' THEN [AMT13C]
WHEN [COD14C]='587' THEN [AMT14C]
WHEN [COD15C]='587' THEN [AMT15C] ELSE 0 END AS [LKER_8230]
,CASE WHEN [COD1C]='588' THEN [AMT1C]
WHEN [COD2C]='588' THEN [AMT2C]
WHEN [COD3C]='588' THEN [AMT3C]
WHEN [COD4C]='588' THEN [AMT4C]
WHEN [COD5C]='588' THEN [AMT5C]
WHEN [COD6C]='588' THEN [AMT6C]
WHEN [COD7C]='588' THEN [AMT7C]
WHEN [COD8C]='588' THEN [AMT8C]
WHEN [COD9C]='588' THEN [AMT9C]
WHEN [COD10C]='588' THEN [AMT10C]
WHEN [COD11C]='588' THEN [AMT11C]
WHEN [COD12C]='588' THEN [AMT12C]
WHEN [COD13C]='588' THEN [AMT13C]
WHEN [COD14C]='588' THEN [AMT14C]
WHEN [COD15C]='588' THEN [AMT15C] ELSE 0 END AS [LKER_8287]
,CASE WHEN [COD1C]='589' THEN [AMT1C]
WHEN [COD2C]='589' THEN [AMT2C]
WHEN [COD3C]='589' THEN [AMT3C]
WHEN [COD4C]='589' THEN [AMT4C]
WHEN [COD5C]='589' THEN [AMT5C]
WHEN [COD6C]='589' THEN [AMT6C]
WHEN [COD7C]='589' THEN [AMT7C]
WHEN [COD8C]='589' THEN [AMT8C]
WHEN [COD9C]='589' THEN [AMT9C]
WHEN [COD10C]='589' THEN [AMT10C]
WHEN [COD11C]='589' THEN [AMT11C]
WHEN [COD12C]='589' THEN [AMT12C]
WHEN [COD13C]='589' THEN [AMT13C]
WHEN [COD14C]='589' THEN [AMT14C]
WHEN [COD15C]='589' THEN [AMT15C] ELSE 0 END AS [LKER_8950]
,CASE WHEN [COD1C]='590' THEN [AMT1C]
WHEN [COD2C]='590' THEN [AMT2C]
WHEN [COD3C]='590' THEN [AMT3C]
WHEN [COD4C]='590' THEN [AMT4C]
WHEN [COD5C]='590' THEN [AMT5C]
WHEN [COD6C]='590' THEN [AMT6C]
WHEN [COD7C]='590' THEN [AMT7C]
WHEN [COD8C]='590' THEN [AMT8C]
WHEN [COD9C]='590' THEN [AMT9C]
WHEN [COD10C]='590' THEN [AMT10C]
WHEN [COD11C]='590' THEN [AMT11C]
WHEN [COD12C]='590' THEN [AMT12C]
WHEN [COD13C]='590' THEN [AMT13C]
WHEN [COD14C]='590' THEN [AMT14C]
WHEN [COD15C]='590' THEN [AMT15C] ELSE 0 END AS [LKER_10502]
,CASE WHEN [COD1C]='591' THEN [AMT1C]
WHEN [COD2C]='591' THEN [AMT2C]
WHEN [COD3C]='591' THEN [AMT3C]
WHEN [COD4C]='591' THEN [AMT4C]
WHEN [COD5C]='591' THEN [AMT5C]
WHEN [COD6C]='591' THEN [AMT6C]
WHEN [COD7C]='591' THEN [AMT7C]
WHEN [COD8C]='591' THEN [AMT8C]
WHEN [COD9C]='591' THEN [AMT9C]
WHEN [COD10C]='591' THEN [AMT10C]
WHEN [COD11C]='591' THEN [AMT11C]
WHEN [COD12C]='591' THEN [AMT12C]
WHEN [COD13C]='591' THEN [AMT13C]
WHEN [COD14C]='591' THEN [AMT14C]
WHEN [COD15C]='591' THEN [AMT15C] ELSE 0 END AS [LKER_14940]
,CASE WHEN [COD1C]='592' THEN [AMT1C]
WHEN [COD2C]='592' THEN [AMT2C]
WHEN [COD3C]='592' THEN [AMT3C]
WHEN [COD4C]='592' THEN [AMT4C]
WHEN [COD5C]='592' THEN [AMT5C]
WHEN [COD6C]='592' THEN [AMT6C]
WHEN [COD7C]='592' THEN [AMT7C]
WHEN [COD8C]='592' THEN [AMT8C]
WHEN [COD9C]='592' THEN [AMT9C]
WHEN [COD10C]='592' THEN [AMT10C]
WHEN [COD11C]='592' THEN [AMT11C]
WHEN [COD12C]='592' THEN [AMT12C]
WHEN [COD13C]='592' THEN [AMT13C]
WHEN [COD14C]='592' THEN [AMT14C]
WHEN [COD15C]='592' THEN [AMT15C] ELSE 0 END AS [LKER_8973]
,CASE WHEN [COD1C]='593' THEN [AMT1C]
WHEN [COD2C]='593' THEN [AMT2C]
WHEN [COD3C]='593' THEN [AMT3C]
WHEN [COD4C]='593' THEN [AMT4C]
WHEN [COD5C]='593' THEN [AMT5C]
WHEN [COD6C]='593' THEN [AMT6C]
WHEN [COD7C]='593' THEN [AMT7C]
WHEN [COD8C]='593' THEN [AMT8C]
WHEN [COD9C]='593' THEN [AMT9C]
WHEN [COD10C]='593' THEN [AMT10C]
WHEN [COD11C]='593' THEN [AMT11C]
WHEN [COD12C]='593' THEN [AMT12C]
WHEN [COD13C]='593' THEN [AMT13C]
WHEN [COD14C]='593' THEN [AMT14C]
WHEN [COD15C]='593' THEN [AMT15C] ELSE 0 END AS [LKER_8935]
,CASE WHEN [COD1C]='594' THEN [AMT1C]
WHEN [COD2C]='594' THEN [AMT2C]
WHEN [COD3C]='594' THEN [AMT3C]
WHEN [COD4C]='594' THEN [AMT4C]
WHEN [COD5C]='594' THEN [AMT5C]
WHEN [COD6C]='594' THEN [AMT6C]
WHEN [COD7C]='594' THEN [AMT7C]
WHEN [COD8C]='594' THEN [AMT8C]
WHEN [COD9C]='594' THEN [AMT9C]
WHEN [COD10C]='594' THEN [AMT10C]
WHEN [COD11C]='594' THEN [AMT11C]
WHEN [COD12C]='594' THEN [AMT12C]
WHEN [COD13C]='594' THEN [AMT13C]
WHEN [COD14C]='594' THEN [AMT14C]
WHEN [COD15C]='594' THEN [AMT15C] ELSE 0 END AS [LKER_8935C]
,CASE WHEN [COD1C]='595' THEN [AMT1C]
WHEN [COD2C]='595' THEN [AMT2C]
WHEN [COD3C]='595' THEN [AMT3C]
WHEN [COD4C]='595' THEN [AMT4C]
WHEN [COD5C]='595' THEN [AMT5C]
WHEN [COD6C]='595' THEN [AMT6C]
WHEN [COD7C]='595' THEN [AMT7C]
WHEN [COD8C]='595' THEN [AMT8C]
WHEN [COD9C]='595' THEN [AMT9C]
WHEN [COD10C]='595' THEN [AMT10C]
WHEN [COD11C]='595' THEN [AMT11C]
WHEN [COD12C]='595' THEN [AMT12C]
WHEN [COD13C]='595' THEN [AMT13C]
WHEN [COD14C]='595' THEN [AMT14C]
WHEN [COD15C]='595' THEN [AMT15C] ELSE 0 END AS [LKER_11100]
,CASE WHEN [COD1C]='112' THEN [AMT1C]
WHEN [COD2C]='112' THEN [AMT2C]
WHEN [COD3C]='112' THEN [AMT3C]
WHEN [COD4C]='112' THEN [AMT4C]
WHEN [COD5C]='112' THEN [AMT5C]
WHEN [COD6C]='112' THEN [AMT6C]
WHEN [COD7C]='112' THEN [AMT7C]
WHEN [COD8C]='112' THEN [AMT8C]
WHEN [COD9C]='112' THEN [AMT9C]
WHEN [COD10C]='112' THEN [AMT10C]
WHEN [COD11C]='112' THEN [AMT11C]
WHEN [COD12C]='112' THEN [AMT12C]
WHEN [COD13C]='112' THEN [AMT13C]
WHEN [COD14C]='112' THEN [AMT14C]
WHEN [COD15C]='112' THEN [AMT15C] ELSE 0 END AS [PCK_DRUMS]
,CASE WHEN [COD1C]='114' THEN [AMT1C]
WHEN [COD2C]='114' THEN [AMT2C]
WHEN [COD3C]='114' THEN [AMT3C]
WHEN [COD4C]='114' THEN [AMT4C]
WHEN [COD5C]='114' THEN [AMT5C]
WHEN [COD6C]='114' THEN [AMT6C]
WHEN [COD7C]='114' THEN [AMT7C]
WHEN [COD8C]='114' THEN [AMT8C]
WHEN [COD9C]='114' THEN [AMT9C]
WHEN [COD10C]='114' THEN [AMT10C]
WHEN [COD11C]='114' THEN [AMT11C]
WHEN [COD12C]='114' THEN [AMT12C]
WHEN [COD13C]='114' THEN [AMT13C]
WHEN [COD14C]='114' THEN [AMT14C]
WHEN [COD15C]='114' THEN [AMT15C] ELSE 0 END AS [PCK_PAILS]
FROM #SPECIALCHARGES
order by [WMS Order No]
GO
April 24, 2017 at 11:21 am
maybe this will give you some ideas
WITH CTE AS
(SELECT s.OWNERB,
s.ORDNOB AS WMSOrderNo,
s.HSTRFB AS CUSTORDNO,
d.scode,
d.samt
FROM SpecialCharges s
CROSS APPLY (VALUES
(COD1C, AMT1C)
,(COD2C, AMT2C)
,(COD3C, AMT3C) -- continue as required
) d (scode, samt)
)
SELECT OWNERB,
WMSOrderNo,
CUSTORDNO,
SUM(CASE WHEN [scode] IN ('H','010') THEN [samt] ELSE 0 END) AS MANHOURS,
SUM(CASE WHEN [scode] IN ('P','500') THEN [samt] ELSE 0 END) AS PALLETS,
SUM(CASE WHEN [scode] IN ('F','130') THEN [samt] ELSE 0 END) AS FULLCASE -- continue as required
FROM CTE
GROUP BY OWNERB, WMSOrderNo, CUSTORDNO
ORDER BY WMSOrderNo
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
April 24, 2017 at 11:27 am
Hi J -
Thank you so much. I'm testing it now and will provide a status update!!
April 24, 2017 at 11:28 am
If it were me, I'd probably process the data from your vendor into a table which has been normalised and then use the normalised table as the basis for all subsequent queries.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
April 24, 2017 at 11:33 am
Phil Parkin - Monday, April 24, 2017 11:28 AMIf it were me, I'd probably process the data from your vendor into a table which has been normalised and then use the normalised table as the basis for all subsequent queries.
edit misread Phil's reply
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
April 24, 2017 at 11:45 am
J Livingston SQL - Monday, April 24, 2017 11:33 AMPhil Parkin - Monday, April 24, 2017 11:28 AMIf it were me, I'd probably process the data from your vendor into a table which has been normalised and then use the normalised table as the basis for all subsequent queries.I agree Phil....but if the vendor is anything like some I have worked with in the past....the cost is probably a reason to pursue a roll your own solution
I was not suggesting asking the vendor to change what they provide, merely that some ETL be performed to make their data more manageable (assuming that this whole process is an import rather than an export). It's not totally clear who or what is consuming the results.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
April 24, 2017 at 11:47 am
Phil Parkin - Monday, April 24, 2017 11:45 AMJ Livingston SQL - Monday, April 24, 2017 11:33 AMPhil Parkin - Monday, April 24, 2017 11:28 AMIf it were me, I'd probably process the data from your vendor into a table which has been normalised and then use the normalised table as the basis for all subsequent queries.I agree Phil....but if the vendor is anything like some I have worked with in the past....the cost is probably a reason to pursue a roll your own solution
I was not suggesting asking the vendor to change what they provide, merely that some ETL be performed to make their data more manageable (assuming that this whole process is an import rather than an export). It's not totally clear who or what is consuming the results.
Agree.....misread your post and have edited mine
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
April 25, 2017 at 10:24 am
anissw - Monday, April 24, 2017 11:27 AMHi J -Thank you so much. I'm testing it now and will provide a status update!!
how did you get on with your testing?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
April 25, 2017 at 11:34 am
Good afternoon -
J - Your suggestion worked like a charm!! This has helped me to cut the redundant entry as well as better organize the statements as I will have to add on additional charges in the future.
Thanks again!!
Anissa
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply