April 6, 2011 at 12:28 pm
Hello,
I was wondering if anyone can help me with this replace function. My
query that I built has multiple columns, two columns are SUBTYPE and BU Type (the columns are side by side which I cannot show here) and some lines of output two of the columns are listed below.
SUBTYPE
PLANT DIRECT COSTS
OTHER
EQUIPMENT P&L
INSURANCE RECOVERY
EQUIPMENT P&L
BU Type --
PO
AD
CO
PO
AD
I am trying to replace the data in the subtype column of EQUIPMENT P&L with OTHER DIRECT COSTS with every output in the BU TY column
that is indicated AD
Thanks
April 6, 2011 at 12:45 pm
Your sample data makes it tough, but here is my shot at it:
SELECT
CASE WHEN T1.BUTY = 'AD' THEN REPLACE(T1.SUBTYPE, 'EQUIPMENT P&L', 'OTHER DIRECT COSTS ')
ELSE T1.SUBTYPE
END
FROM TABLE T1
Is this what you had in mind?
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
April 6, 2011 at 1:02 pm
Please show your query, and then work on explaining the logic a bit more. It will help if you use quotes to show actual text that should be replaced, and give us a table definition (DDL) so we know what columns are involved.
April 6, 2011 at 2:13 pm
I have attached the query below. I am trying to replace the output data in the subtype column of EQUIPMENT P&L with OTHER DIRECT COSTS with every output in the BU TY column
that is indicated AD. The BU TY column comes from table F0006 and the column header from this table is MCSTYL. I hope this helps,
There are three tables in the query, and hundred of columns. the tables are,
F0006
F0902
F0901
This is what I have developed so far but I am not sure if it is sound,
SELECT
CASE WHEN MCSTYL = 'AD' THEN REPLACE (SUBTYPE, 'EQUIPMENT P&L', 'OTHER DIRECT COSTS ')
ELSE SUBTYPE
END
Thanks
SELECT
GBFY AS GBFY_1,
MCRP01,
MCRP02,
" MCRP0201" AS MCRP0201,
MCRP03,
" MCRP0301" AS MCRP0301,
CASE
WHEN GBOBJ between '1000' and '1999' THEN 'ASSETS'
WHEN GBOBJ between '2000' and '2999' THEN 'LIABILITIES'
WHEN GBOBJ between '3000' and '3999' THEN 'SHAREHOLDER EQUITY'
WHEN GBOBJ between '4000' and '4999' THEN 'REVENUE'
WHEN GBOBJ between '5000' and '5999' THEN 'COST OF REVENUE'
WHEN GBOBJ between '6000' and '6999' THEN 'G&A'
WHEN GBOBJ between '7000' and '8999' THEN 'OI&E'
WHEN GBOBJ between '9000' and '9049' THEN 'TAXES'
WHEN GBOBJ between '9050' and '9051' THEN 'MINORITY INTEREST'
WHEN GBOBJ between '9910' and '9999' THEN 'PLANT ACCTG'
ELSE ''
END AS TYPE,
CASE
WHEN GBOBJ between '6121' and '6899' THEN 'OTHER'
WHEN GBOBJ between '6110' and '6120' THEN 'SALARIES AND RELATED'
WHEN GBOBJ = '6900' AND GBSUB = '50' THEN 'CASH BONUS PLAN'
WHEN GBOBJ = '6900' AND GBSUB = '51' THEN 'CASH BONUS PLAN'
WHEN GBOBJ = '6950' THEN 'SALARIES AND RELATED'
WHEN GBOBJ = '5105' AND GBSUB = '32' THEN 'INSURANCE RECOVERY'
WHEN GBOBJ between '5000' and '5105' THEN 'LARGE PROJECT DIRECT COSTS'
WHEN GBOBJ = '5110' AND GBSUB = '32' THEN 'INSURANCE RECOVERY'
WHEN GBOBJ between '5106' and '5119' THEN 'CONSTRUCTION DIRECT COSTS'
WHEN GBOBJ between '5130' and '5140' THEN 'CONSTRUCTION DIRECT COSTS'
WHEN GBOBJ between '5141' and '5142' THEN 'LARGE PROJECT DIRECT COSTS'
WHEN GBOBJ between '5153' and '5154' THEN 'CONSTRUCTION DIRECT COSTS'
WHEN GBOBJ between '5156' and '5299' THEN 'OTHER DIRECT COSTS'
WHEN GBOBJ between '5700' and '5999' THEN 'OTHER DIRECT COSTS'
WHEN GBOBJ = '4110' AND GBSUB = '20' THEN 'CONSTRUCTION REVENUE'
WHEN GBOBJ between '4000' and '4108' THEN 'LARGE PROJECT REVENUE'
WHEN GBOBJ between '4109' and '4113' THEN 'CONSTRUCTION REVENUE'
WHEN GBOBJ between '4130' and '4140' THEN 'CONSTRUCTION REVENUE'
WHEN GBOBJ between '4141' and '4142' THEN 'LARGE PROJECT REVENUE'
WHEN GBOBJ between '4143' and '4151' THEN 'CONSTRUCTION REVENUE'
WHEN GBOBJ between '4140' and '4151' THEN 'CONSTRUCTION REVENUE'
WHEN GBOBJ between '4153' and '4154' THEN 'CONSTRUCTION REVENUE'
WHEN GBOBJ between '4156' and '4190' THEN 'CONSTRUCTION REVENUE'
WHEN GBOBJ between '4191' and '4192' THEN 'LARGE PROJECT REVENUE'
WHEN GBOBJ between '5300' and '5699' THEN 'EQUIPMENT P&L'
WHEN GBOBJ between '7700' and '7799' THEN 'GAIN ON SALES OF PROP & EQUIP'
WHEN GBOBJ = '6900' AND GBSUB = '15' THEN 'INCENTIVE COMP REST'
WHEN GBOBJ = '6900' AND GBSUB = '16' THEN 'INCENTIVE COMP REST'
WHEN GBOBJ between '6900' and '6949' THEN 'INCENTIVE COMP'
WHEN GBOBJ between '6951' and '6999' THEN 'OTHER'
WHEN GBOBJ between '7500' and '7599' THEN 'INTEREST EXPENSE'
WHEN GBOBJ between '7000' and '7499' THEN 'INTEREST INCOME'
WHEN GBOBJ = '5152' THEN 'REAL ESTATE COSTS'
WHEN GBOBJ = '4152' THEN 'REAL ESTATE REVENUE'
WHEN GBOBJ between '9050' and '9051' THEN 'MINORITY INTEREST'
WHEN GBOBJ between '8000' and '8999' THEN 'OTHER, NET'
WHEN GBOBJ = '5120' AND GBSUB = '32' THEN 'INSURANCE RECOVERY'
WHEN GBOBJ between '5120' and '5129' THEN 'PLANT DIRECT COSTS'
WHEN GBOBJ between '4115' and '4129' THEN 'PLANT REVENUE'
WHEN GBOBJ = '5155' THEN 'REAL ESTATE COSTS'
WHEN GBOBJ = '4155' THEN 'REAL ESTATE REVENUE'
WHEN GBOBJ between '9000' and '9021' THEN 'TAXES'
ELSE NULL
END AS SUBTYPE,
CASE
WHEN GBOBJ = '4110' and GBSUB = '20' THEN '1a Construction overbillings'
WHEN GBOBJ = '4105' and GBSUB = '20' THEN '1a Large Project overbillings'
WHEN GBOBJ = '6900' AND GBSUB = '15' THEN '7b Restricted Stock'
WHEN GBOBJ = '6900' AND GBSUB = '16' THEN '7b Restricted Stock FAS123R'
WHEN GBOBJ = '6900' AND GBSUB = '50' THEN '7c PSP'
WHEN GBOBJ = '6900' AND GBSUB = '51' THEN '7c PSP'
WHEN GBOBJ = '8190' AND GBSUB = '40' THEN '8e Equity in Income of Affiliates'
WHEN GBOBJ between '4000' and '4109' THEN '1 Large Project Revenue'
WHEN GBOBJ between '4110' and '4113' THEN '1 Construction Revenue'
WHEN GBOBJ between '4115' and '4129' THEN '2 Plant Revenue'
WHEN GBOBJ between '4130' and '4140' THEN '1 Construction Revenue'
WHEN GBOBJ between '4141' and '4142' THEN '1 Large Project Revenue'
WHEN GBOBJ between '4143' and '4151' THEN '3 Misc Job Adjustments'
WHEN GBOBJ = '4152' THEN '3a Land Sales'
WHEN GBOBJ between '4153' and '4154' THEN '3 Misc Job Adjustments'
WHEN GBOBJ = '4155' THEN '3a Rental Revenue'
WHEN GBOBJ between '4156' and '4190' THEN '3 Misc Job Construction Adjustments'
WHEN GBOBJ between '4191' and '4192' THEN '3 Misc Job Large Project Adjustments'
WHEN GBOBJ = '5105' and GBSUB = '32' THEN '4 Large Project Ins Recovery'
WHEN GBOBJ = '5110' and GBSUB = '32' THEN '4 Construction Ins Recovery'
WHEN GBOBJ = '5120' and GBSUB = '32' THEN '4 Plant Ins Recovery'
WHEN GBOBJ between '5000' and '5105' THEN '4 Large Project Direct Costs'
WHEN GBOBJ between '5106' and '5110' THEN '4 Construction Direct Costs'
WHEN GBOBJ between '5120' and '5129' THEN '5 Plant Direct Costs'
WHEN GBOBJ between '5130' and '5140' THEN '4 Construction Direct Costs'
WHEN GBOBJ between '5141' and '5142' THEN '4 Large Project Direct Costs'
WHEN GBOBJ = '5152' THEN '4a Land Cost'
WHEN GBOBJ between '5153' and '5154' THEN '4 Construction Direct Costs'
WHEN GBOBJ = '5155' THEN '4a Rental Cost'
WHEN GBOBJ between '5156' and '5299' THEN '4 Misc Job Adjustments'
WHEN GBOBJ between '5300' and '5699' THEN '6 Equipment (P) & L'
WHEN GBOBJ between '5700' and '5999' THEN '4 Small Tools and Fuel'
WHEN GBOBJ between '6000' and '6119' THEN '7 Salaries'
WHEN GBOBJ between '6120' and '6149' THEN '7 Burden'
WHEN GBOBJ between '6150' and '6899' THEN '7 Other'
WHEN GBOBJ between '6950' and '6951' THEN '7 NQDC'
WHEN GBOBJ between '6952' and '6999' THEN '7 Other G&A'
WHEN GBOBJ between '6900' and '6949' THEN '7a Incentive Comp'
WHEN GBOBJ between '7000' and '7499' THEN '8a Interest Income'
WHEN GBOBJ between '7500' and '7599' THEN '8b Interest Expense'
WHEN GBOBJ between '7700' and '7799' THEN '8c Gain on Sales Prop & Equip'
WHEN GBOBJ between '8000' and '8999' THEN '8d Other, net'
WHEN GBOBJ between '9000' and '9021' THEN '9 Taxes'
WHEN GBOBJ between '9050' and '9051' THEN '9b Minority Interest'
ELSE NULL
END AS "Line Item",
SUM ( CASE
WHEN &Month = 1 THEN GBAN01
WHEN &Month = 2 THEN GBAN01 + GBAN02
WHEN &Month = 3 THEN GBAN01 + GBAN02 + GBAN03
WHEN &Month = 4 THEN GBAN01 + GBAN02 + GBAN03 + GBAN04
WHEN &Month = 5 THEN GBAN01 + GBAN02 + GBAN03 + GBAN04 + GBAN05
WHEN &Month = 6 THEN GBAN01 + GBAN02 + GBAN03 + GBAN04 + GBAN05 + GBAN06
WHEN &Month = 7 THEN GBAN01 + GBAN02 + GBAN03 + GBAN04 + GBAN05 + GBAN06 + GBAN07
WHEN &Month = 8 THEN GBAN01 + GBAN02 + GBAN03 + GBAN04 + GBAN05 + GBAN06 + GBAN07 + GBAN08
WHEN &Month = 9 THEN GBAN01 + GBAN02 + GBAN03 + GBAN04 + GBAN05 + GBAN06 + GBAN07 + GBAN08 + GBAN09
WHEN &Month = 10 THEN GBAN01 + GBAN02 + GBAN03 + GBAN04 + GBAN05 + GBAN06 + GBAN07 + GBAN08 + GBAN09 + GBAN10
WHEN &Month = 11 THEN GBAN01 + GBAN02 + GBAN03 + GBAN04 + GBAN05 + GBAN06 + GBAN07 + GBAN08 + GBAN09 + GBAN10 + GBAN11
WHEN &Month = 12 THEN GBAN01 + GBAN02 + GBAN03 + GBAN04 + GBAN05 + GBAN06 + GBAN07 + GBAN08 + GBAN09 + GBAN10 + GBAN11 + GBAN12
END ) AS YTD,
SUM ( CASE
WHEN &Month = 1 THEN GBAN01
WHEN &Month = 2 THEN GBAN01 + GBAN02
WHEN &Month = 3 THEN GBAN01 + GBAN02 + GBAN03
WHEN &Month = 4 THEN GBAN04
WHEN &Month = 5 THEN GBAN04 + GBAN05
WHEN &Month = 6 THEN GBAN04 + GBAN05 + GBAN06
WHEN &Month = 7 THEN GBAN07
WHEN &Month = 8 THEN GBAN07 + GBAN08
WHEN &Month = 9 THEN GBAN07 + GBAN08 + GBAN09
WHEN &Month = 10 THEN GBAN10
WHEN &Month = 11 THEN GBAN10 + GBAN11
WHEN &Month = 12 THEN GBAN10 + GBAN11 + GBAN12
END ) AS QTD,
SUM ( CASE
WHEN &Month = 1 THEN GBAN01
WHEN &Month = 2 THEN GBAN02
WHEN &Month = 3 THEN GBAN03
WHEN &Month = 4 THEN GBAN04
WHEN &Month = 5 THEN GBAN05
WHEN &Month = 6 THEN GBAN06
WHEN &Month = 7 THEN GBAN07
WHEN &Month = 8 THEN GBAN08
WHEN &Month = 9 THEN GBAN09
WHEN &Month = 10 THEN GBAN10
WHEN &Month = 11 THEN GBAN11
WHEN &Month = 12 THEN GBAN12
END ) AS MTD,
TRIM( GBMCU ) AS GBMCU,
MCDL01,
MCSTYL,
CASE
WHEN GBOBJ = '4110' AND GBSUB = '20' THEN 'CO'
WHEN GBOBJ between '4000' and '4108' THEN 'LO'
WHEN GBOBJ between '4109' and '4113' THEN 'CO'
WHEN GBOBJ between '4130' and '4140' THEN 'CO'
WHEN GBOBJ between '4141' and '4142' THEN 'LO'
WHEN GBOBJ = '5152' THEN 'RE'
WHEN GBOBJ = '4152' THEN 'RE'
WHEN GBOBJ = '5155' THEN 'RE'
WHEN GBOBJ = '4155' THEN 'RE'
WHEN GBOBJ between '4143' and '4151' THEN 'CO'
WHEN GBOBJ between '4140' and '4151' THEN 'CO'
WHEN GBOBJ between '4153' and '4154' THEN 'CO'
WHEN GBOBJ between '4156' and '4190' THEN 'CO'
WHEN GBOBJ between '4191' and '4192' THEN 'LO'
WHEN GBOBJ between '5106' and '5119' THEN 'CO'
WHEN GBOBJ between '5130' and '5140' THEN 'CO'
WHEN GBOBJ between '5153' and '5154' THEN 'CO'
WHEN GBOBJ between '5120' and '5129' THEN 'PO'
WHEN GBOBJ between '4115' and '4129' THEN 'PO'
WHEN GBOBJ between '5000' and '5105' THEN 'LO'
WHEN GBOBJ between '5141' and '5142' THEN 'LO'
ELSE MCSTYL
END AS "Segment",
CASE
WHEN MCRP01 BETWEEN '211' AND '214' THEN 'OPS'
ELSE NULL
END AS COLUMN0011,
GBLT
FROM
GCCDATA.F0901 F0901,
GCCDATA.F0902 F0902,
GCCDATA.F0006 F0006
WHERE
F0902.GBAID = F0901.GMAID
AND F0902.GBMCU = F0006.MCMCU
AND ( ( ( GBOBJ > '3999'
AND GBFY IN( 10, 11 )
AND GBCO BETWEEN '00001' AND '09999')
AND GBLT IN( 'AA', 'JM' ))
AND MCSTYL IN( 'CO', 'AD', 'IC', 'LO', 'RE', 'PO', 'SS' )
AND - 1 * CASE
WHEN &Month = 1 THEN GBAN01
WHEN &Month = 2 THEN GBAN01 + GBAN02
WHEN &Month = 3 THEN GBAN01 + GBAN02 + GBAN03
WHEN &Month = 4 THEN GBAN01 + GBAN02 + GBAN03 + GBAN04
WHEN &Month = 5 THEN GBAN01 + GBAN02 + GBAN03 + GBAN04 + GBAN05
WHEN &Month = 6 THEN GBAN01 + GBAN02 + GBAN03 + GBAN04 + GBAN05 + GBAN06
WHEN &Month = 7 THEN GBAN01 + GBAN02 + GBAN03 + GBAN04 + GBAN05 + GBAN06 + GBAN07
WHEN &Month = 8 THEN GBAN01 + GBAN02 + GBAN03 + GBAN04 + GBAN05 + GBAN06 + GBAN07 + GBAN08
WHEN &Month = 9 THEN GBAN01 + GBAN02 + GBAN03 + GBAN04 + GBAN05 + GBAN06 + GBAN07 + GBAN08 + GBAN09
WHEN &Month = 10 THEN GBAN01 + GBAN02 + GBAN03 + GBAN04 + GBAN05 + GBAN06 + GBAN07 + GBAN08 + GBAN09 + GBAN10
WHEN &Month = 11 THEN GBAN01 + GBAN02 + GBAN03 + GBAN04 + GBAN05 + GBAN06 + GBAN07 + GBAN08 + GBAN09 + GBAN10 + GBAN11
WHEN &Month = 12 THEN GBAN01 + GBAN02 + GBAN03 + GBAN04 + GBAN05 + GBAN06 + GBAN07 + GBAN08 + GBAN09 + GBAN10 + GBAN11 + GBAN12
END IN ( &COLUMN0003 ))
GROUP BY
GBFY,
MCRP01,
MCRP02,
" MCRP0201",
MCRP03,
" MCRP0301",
CASE
WHEN GBOBJ between '1000' and '1999' THEN 'ASSETS'
WHEN GBOBJ between '2000' and '2999' THEN 'LIABILITIES'
WHEN GBOBJ between '3000' and '3999' THEN 'SHAREHOLDER EQUITY'
WHEN GBOBJ between '4000' and '4999' THEN 'REVENUE'
WHEN GBOBJ between '5000' and '5999' THEN 'COST OF REVENUE'
WHEN GBOBJ between '6000' and '6999' THEN 'G&A'
WHEN GBOBJ between '7000' and '8999' THEN 'OI&E'
WHEN GBOBJ between '9000' and '9049' THEN 'TAXES'
WHEN GBOBJ between '9050' and '9051' THEN 'MINORITY INTEREST'
WHEN GBOBJ between '9910' and '9999' THEN 'PLANT ACCTG'
ELSE ''
END,
CASE
WHEN GBOBJ between '6121' and '6899' THEN 'OTHER'
WHEN GBOBJ between '6110' and '6120' THEN 'SALARIES AND RELATED'
WHEN GBOBJ = '6900' AND GBSUB = '50' THEN 'CASH BONUS PLAN'
WHEN GBOBJ = '6900' AND GBSUB = '51' THEN 'CASH BONUS PLAN'
WHEN GBOBJ = '6950' THEN 'SALARIES AND RELATED'
WHEN GBOBJ = '5105' AND GBSUB = '32' THEN 'INSURANCE RECOVERY'
WHEN GBOBJ between '5000' and '5105' THEN 'LARGE PROJECT DIRECT COSTS'
WHEN GBOBJ = '5110' AND GBSUB = '32' THEN 'INSURANCE RECOVERY'
WHEN GBOBJ between '5106' and '5119' THEN 'CONSTRUCTION DIRECT COSTS'
WHEN GBOBJ between '5130' and '5140' THEN 'CONSTRUCTION DIRECT COSTS'
WHEN GBOBJ between '5141' and '5142' THEN 'LARGE PROJECT DIRECT COSTS'
WHEN GBOBJ between '5153' and '5154' THEN 'CONSTRUCTION DIRECT COSTS'
WHEN GBOBJ between '5156' and '5299' THEN 'OTHER DIRECT COSTS'
WHEN GBOBJ between '5700' and '5999' THEN 'OTHER DIRECT COSTS'
WHEN GBOBJ = '4110' AND GBSUB = '20' THEN 'CONSTRUCTION REVENUE'
WHEN GBOBJ between '4000' and '4108' THEN 'LARGE PROJECT REVENUE'
WHEN GBOBJ between '4109' and '4113' THEN 'CONSTRUCTION REVENUE'
WHEN GBOBJ between '4130' and '4140' THEN 'CONSTRUCTION REVENUE'
WHEN GBOBJ between '4141' and '4142' THEN 'LARGE PROJECT REVENUE'
WHEN GBOBJ between '4143' and '4151' THEN 'CONSTRUCTION REVENUE'
WHEN GBOBJ between '4140' and '4151' THEN 'CONSTRUCTION REVENUE'
WHEN GBOBJ between '4153' and '4154' THEN 'CONSTRUCTION REVENUE'
WHEN GBOBJ between '4156' and '4190' THEN 'CONSTRUCTION REVENUE'
WHEN GBOBJ between '4191' and '4192' THEN 'LARGE PROJECT REVENUE'
WHEN GBOBJ between '5300' and '5699' THEN 'EQUIPMENT P&L'
WHEN GBOBJ between '7700' and '7799' THEN 'GAIN ON SALES OF PROP & EQUIP'
WHEN GBOBJ = '6900' AND GBSUB = '15' THEN 'INCENTIVE COMP REST'
WHEN GBOBJ = '6900' AND GBSUB = '16' THEN 'INCENTIVE COMP REST'
WHEN GBOBJ between '6900' and '6949' THEN 'INCENTIVE COMP'
WHEN GBOBJ between '6951' and '6999' THEN 'OTHER'
WHEN GBOBJ between '7500' and '7599' THEN 'INTEREST EXPENSE'
WHEN GBOBJ between '7000' and '7499' THEN 'INTEREST INCOME'
WHEN GBOBJ = '5152' THEN 'REAL ESTATE COSTS'
WHEN GBOBJ = '4152' THEN 'REAL ESTATE REVENUE'
WHEN GBOBJ between '9050' and '9051' THEN 'MINORITY INTEREST'
WHEN GBOBJ between '8000' and '8999' THEN 'OTHER, NET'
WHEN GBOBJ = '5120' AND GBSUB = '32' THEN 'INSURANCE RECOVERY'
WHEN GBOBJ between '5120' and '5129' THEN 'PLANT DIRECT COSTS'
WHEN GBOBJ between '4115' and '4129' THEN 'PLANT REVENUE'
WHEN GBOBJ = '5155' THEN 'REAL ESTATE COSTS'
WHEN GBOBJ = '4155' THEN 'REAL ESTATE REVENUE'
WHEN GBOBJ between '9000' and '9021' THEN 'TAXES'
ELSE NULL
END,
CASE
WHEN GBOBJ = '4110' and GBSUB = '20' THEN '1a Construction overbillings'
WHEN GBOBJ = '4105' and GBSUB = '20' THEN '1a Large Project overbillings'
WHEN GBOBJ = '6900' AND GBSUB = '15' THEN '7b Restricted Stock'
WHEN GBOBJ = '6900' AND GBSUB = '16' THEN '7b Restricted Stock FAS123R'
WHEN GBOBJ = '6900' AND GBSUB = '50' THEN '7c PSP'
WHEN GBOBJ = '6900' AND GBSUB = '51' THEN '7c PSP'
WHEN GBOBJ = '8190' AND GBSUB = '40' THEN '8e Equity in Income of Affiliates'
WHEN GBOBJ between '4000' and '4109' THEN '1 Large Project Revenue'
WHEN GBOBJ between '4110' and '4113' THEN '1 Construction Revenue'
WHEN GBOBJ between '4115' and '4129' THEN '2 Plant Revenue'
WHEN GBOBJ between '4130' and '4140' THEN '1 Construction Revenue'
WHEN GBOBJ between '4141' and '4142' THEN '1 Large Project Revenue'
WHEN GBOBJ between '4143' and '4151' THEN '3 Misc Job Adjustments'
WHEN GBOBJ = '4152' THEN '3a Land Sales'
WHEN GBOBJ between '4153' and '4154' THEN '3 Misc Job Adjustments'
WHEN GBOBJ = '4155' THEN '3a Rental Revenue'
WHEN GBOBJ between '4156' and '4190' THEN '3 Misc Job Construction Adjustments'
WHEN GBOBJ between '4191' and '4192' THEN '3 Misc Job Large Project Adjustments'
WHEN GBOBJ = '5105' and GBSUB = '32' THEN '4 Large Project Ins Recovery'
WHEN GBOBJ = '5110' and GBSUB = '32' THEN '4 Construction Ins Recovery'
WHEN GBOBJ = '5120' and GBSUB = '32' THEN '4 Plant Ins Recovery'
WHEN GBOBJ between '5000' and '5105' THEN '4 Large Project Direct Costs'
WHEN GBOBJ between '5106' and '5110' THEN '4 Construction Direct Costs'
WHEN GBOBJ between '5120' and '5129' THEN '5 Plant Direct Costs'
WHEN GBOBJ between '5130' and '5140' THEN '4 Construction Direct Costs'
WHEN GBOBJ between '5141' and '5142' THEN '4 Large Project Direct Costs'
WHEN GBOBJ = '5152' THEN '4a Land Cost'
WHEN GBOBJ between '5153' and '5154' THEN '4 Construction Direct Costs'
WHEN GBOBJ = '5155' THEN '4a Rental Cost'
WHEN GBOBJ between '5156' and '5299' THEN '4 Misc Job Adjustments'
WHEN GBOBJ between '5300' and '5699' THEN '6 Equipment (P) & L'
WHEN GBOBJ between '5700' and '5999' THEN '4 Small Tools and Fuel'
WHEN GBOBJ between '6000' and '6119' THEN '7 Salaries'
WHEN GBOBJ between '6120' and '6149' THEN '7 Burden'
WHEN GBOBJ between '6150' and '6899' THEN '7 Other'
WHEN GBOBJ between '6950' and '6951' THEN '7 NQDC'
WHEN GBOBJ between '6952' and '6999' THEN '7 Other G&A'
WHEN GBOBJ between '6900' and '6949' THEN '7a Incentive Comp'
WHEN GBOBJ between '7000' and '7499' THEN '8a Interest Income'
WHEN GBOBJ between '7500' and '7599' THEN '8b Interest Expense'
WHEN GBOBJ between '7700' and '7799' THEN '8c Gain on Sales Prop & Equip'
WHEN GBOBJ between '8000' and '8999' THEN '8d Other, net'
WHEN GBOBJ between '9000' and '9021' THEN '9 Taxes'
WHEN GBOBJ between '9050' and '9051' THEN '9b Minority Interest'
ELSE NULL
END,
TRIM( GBMCU ),
MCDL01,
MCSTYL,
CASE
WHEN GBOBJ = '4110' AND GBSUB = '20' THEN 'CO'
WHEN GBOBJ between '4000' and '4108' THEN 'LO'
WHEN GBOBJ between '4109' and '4113' THEN 'CO'
WHEN GBOBJ between '4130' and '4140' THEN 'CO'
WHEN GBOBJ between '4141' and '4142' THEN 'LO'
WHEN GBOBJ = '5152' THEN 'RE'
WHEN GBOBJ = '4152' THEN 'RE'
WHEN GBOBJ = '5155' THEN 'RE'
WHEN GBOBJ = '4155' THEN 'RE'
WHEN GBOBJ between '4143' and '4151' THEN 'CO'
WHEN GBOBJ between '4140' and '4151' THEN 'CO'
WHEN GBOBJ between '4153' and '4154' THEN 'CO'
WHEN GBOBJ between '4156' and '4190' THEN 'CO'
WHEN GBOBJ between '4191' and '4192' THEN 'LO'
WHEN GBOBJ between '5106' and '5119' THEN 'CO'
WHEN GBOBJ between '5130' and '5140' THEN 'CO'
WHEN GBOBJ between '5153' and '5154' THEN 'CO'
WHEN GBOBJ between '5120' and '5129' THEN 'PO'
WHEN GBOBJ between '4115' and '4129' THEN 'PO'
WHEN GBOBJ between '5000' and '5105' THEN 'LO'
WHEN GBOBJ between '5141' and '5142' THEN 'LO'
ELSE MCSTYL
END,
CASE
WHEN MCRP01 BETWEEN '211' AND '214' THEN 'OPS'
ELSE NULL
END,
GBLT
April 6, 2011 at 5:54 pm
This query is long, but the logic makes sense. It appears that you have the logic, but this isn't T-SQL code. The CASE statements are syntactically correct, but that's about it.
What accounting system is this from? It's not Great Plains. PeopleSoft maybe?
Read Books On Line, or any number of articles on SSC, about ANSI join syntax and aliasing. It will make you code infinitely easier to read.
This is fine:
SELECT
CASE WHEN MCSTYL = 'AD' THEN REPLACE (SUBTYPE, 'EQUIPMENT P&L', 'OTHER DIRECT COSTS ')
ELSE SUBTYPE
END
What is &Month? This isn't a valid field name in T-SQL
TRIM( GBMCU ) will not work. LTRIM and RTRIM are T-SQL Syntax
This case statement won't work:
AND - 1 * CASE
WHEN &Month = 1 THEN GBAN01
WHEN &Month = 2 THEN GBAN01 + GBAN02
WHEN &Month = 3 THEN GBAN01 + GBAN02 + GBAN03
WHEN &Month = 4 THEN GBAN01 + GBAN02 + GBAN03 + GBAN04
WHEN &Month = 5 THEN GBAN01 + GBAN02 + GBAN03 + GBAN04 + GBAN05
WHEN &Month = 6 THEN GBAN01 + GBAN02 + GBAN03 + GBAN04 + GBAN05 + GBAN06
WHEN &Month = 7 THEN GBAN01 + GBAN02 + GBAN03 + GBAN04 + GBAN05 + GBAN06 + GBAN07
WHEN &Month = 8 THEN GBAN01 + GBAN02 + GBAN03 + GBAN04 + GBAN05 + GBAN06 + GBAN07 + GBAN08
WHEN &Month = 9 THEN GBAN01 + GBAN02 + GBAN03 + GBAN04 + GBAN05 + GBAN06 + GBAN07 + GBAN08 + GBAN09
WHEN &Month = 10 THEN GBAN01 + GBAN02 + GBAN03 + GBAN04 + GBAN05 + GBAN06 + GBAN07 + GBAN08 + GBAN09 + GBAN10
WHEN &Month = 11 THEN GBAN01 + GBAN02 + GBAN03 + GBAN04 + GBAN05 + GBAN06 + GBAN07 + GBAN08 + GBAN09 + GBAN10 + GBAN11
WHEN &Month = 12 THEN GBAN01 + GBAN02 + GBAN03 + GBAN04 + GBAN05 + GBAN06 + GBAN07 + GBAN08 + GBAN09 + GBAN10 + GBAN11 + GBAN12
END IN ( &COLUMN0003 ))
You used in properly when you did this AND GBFY IN( 10, 11 ), but it will not work in the above context.
There are many ways to do this, some quite elegant, but since you are a newbie, try some of these suggestions:
1. Create some temp tables and create the summary data in them first. Divide and conquer!
For example, the totals for each month by product(?) would be a good place to start.
CREATE TABLE #MonthlyTotals
(
JanuaryTotal money,
FebTotal money,
and so forth.
Fill these smaller tables, validate what you have, and then join and select from the temp tables.
2. Same basic premise as above, but use the temp tables as a subset of the bigger tables.
3. And, I can't believe I'm saying this, but create a cursor and fill a temp table row by row. This will force you to break it into smaller parts, which will hopefully lead you to the final result.
4. Once you figutre the logic out, you can use subqueries to do the grouping and summing, and then select from there.
Lastly, unless you can post actual DDL, as Steve suggested, we can only provide very generic advice.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
April 6, 2011 at 6:07 pm
I believe the accounting system is JD Edwards, and I am using Strategy/Showcase to generate this SQL Statement. These are both old systems.
I do not know what you mean when you say "Lastly, unless you can post actual DDL", what is DDL.
I have actually ordered a SQL book so when I read it hopefully I was understand more of this,
Thanks for all your help,
Regards
April 6, 2011 at 6:23 pm
DDL is "Data Definition Language" Or, in other words, the actual schema of the tables.
There is a wealth of info on this site. Books On Line is my first place to go.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
April 7, 2011 at 12:07 pm
In Management Studio, right click a table, select Script Table, as Create, to wherever and paste that in here. You'll have something like:
CREATE TABLE [HumanResources].[EmployeeDepartmentHistory](
[BusinessEntityID] [int] NOT NULL,
[DepartmentID] [smallint] NOT NULL,
[ShiftID] [tinyint] NOT NULL,
[StartDate] [date] NOT NULL,
[EndDate] [date] NULL,
[ModifiedDate] [datetime] NOT NULL,
CONSTRAINT [PK_EmployeeDepartmentHistory_BusinessEntityID_StartDate_DepartmentID] PRIMARY KEY CLUSTERED
(
[BusinessEntityID] ASC,
[StartDate] ASC,
[DepartmentID] ASC,
[ShiftID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
April 7, 2011 at 1:36 pm
Wow, that's a huge series of CASE statements. Unless you are sure that this is the only time you will have to contend with this code, I would consider creating a lookup table for this rather than having to deal with the long CASEs.
The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply