Consider the following SQL. I expect the last column of the results
to contain a calculated percentage. Instead, the column contains all
zeros except when the profit margin is negative,where I get a number
in multiples of 100 (300, 700, 1600, etc)?
Column 6 is "dollars charged per yard", column 9 is "column6 -
production_cost". I want to calculate COL6/COL9*100 to arrive at a
profit margin percentage.
This smells of one of those forehead-slapping, "duh!" problems, but
I've not been able to find the solution. Any ideas?
A related question: Why can't I use '...AS COLUMNNAME' to identify
the columns? If I do, it ignores it and uses 0000## as the column
name where the colums are summed or calculated.
TIA!
Jm
SELECT
STLOMTYP,
CFSTYLE,
CFLINWID,
CFSHASPC,
Sum(CFYARDS),
Sum(CFYARDS)*ODPRICEY,
Sum(CFYARDS)*(PMCOST3+PMCOST4+PMCOST2+CRCOST1+PMCOST6) ,
Sum(CFYARDS)*(PMCOST3+PMCOST4+PMCOST2+CRCOST1+PMCOST6+PMCOST1) ,
(Sum(CFYARDS)*ODPRICEY)-(Sum(CFYARDS)*(PMCOST3+PMCOST4+PMCOST2+CRCOST1+PMCOST6)),
(Sum(CFYARDS)*ODPRICEY)-(Sum(CFYARDS)*(PMCOST3+PMCOST4+PMCOST2+CRCOST1+PMCOST6+PMCOST1)),
(Sum(CFYARDS)*(PMCOST3+PMCOST4+PMCOST2+CRCOST1+PMCOST6))/(Sum(CFYARDS)*ODPRICEY)*100
FROM
OBPRDDTA.OB061L04 OB061L04,
OBPRDDTA.OB1002L03 OB1002L03,
OBPRDDTA.OB005L01 OB005L01,
OBPRDDTA.OB1000L39 OB1000L39,
OBPRDDTA.OB064L01 OB064L01
WHERE
CFCOLOR = CRCOLOR AND
CFSHASPC = CRSHASPC AND
CFCOLOR = PMCOLOR AND
CFSTYLE = PMSTYLE AND
CFLINWID = PMLINWID AND
CFSHASPC = PMSHASPC AND
CFORDER# = ODORDER# AND
CFORDLN# = ODORDLN# AND
CFSTYLE = STSTYLE AND
(CFINVDAT>={d '2008-03-30'} And CFINVDAT<={d '2008-04-26'})
GROUP BY
PMCOST1,
PMCOST2,
PMCOST3,
PMCOST4,
PMCOST5,
PMCOST6,
CRCOST1,
ODPRICEY,
STLOMTYP,
CFSTYLE,
CFLINWID,
CFSHASPC
ORDER BY
STLOMTYP,
CFSTYLE,
CFLINWID,
CFSHASPC
|
