Customer Profitability / Cost to Serve Report

— Customer Profitability / Cost to Serve Report

— Variables Below …
— Set the XXXX in year(docdate) to the desired reporting year
— Set the XX month(docdate) to the desired reporting month
— Note the Cost Per Order, Cost Per Order X, Towards Net Profit are set to blank because it was easier to run the query and export the data to Excel to do the math formulas there to avoid dealing with “division by 0” errors in SQL
— Note the CASE statement that deals with cost of goods is using 0.78 as a multiplier for any item that had a zero cost. You need to change this to an average of your points to get a good number.

set nocount on

select
sales.CUSTNMBR [Customer Number],
cust.CUSTNAME [Customer Name],
Sales,
Cost,
sales – cost [Gross Profit $],
case when sales = 0 then 0 else (sales – cost) / sales end [Gross Profit %],
num_of_sales [# of Orders],
‘ ‘ [Cost Per Order(C.P.O)],
‘ ‘ [C.P.O x (# Orders)],
‘ ‘ [Towards Net Profit],
cust.AVGDTPYR [AR Days],
cust.PYMTRMID [Payment Method],
cust.PRCLEVEL [Price Level]

from

(select CUSTNMBR, sum(SALES) sales, sum(num_of_sales) num_of_sales
from
(select CUSTNMBR,
sum(case when RMDTYPAL in (1,3) then SLSAMNT + MISCAMNT – TRDISAMT else -1*(SLSAMNT + MISCAMNT – TRDISAMT) end) SALES,
count(*) num_of_sales
from RM20101
Where
VOIDSTTS = 0 and year(DOCDATE) = ‘XXXX’ and month(docdate) = ‘XX’
and RMDTYPAL in (1,3,7,8)
group by CUSTNMBR

union all
select CUSTNMBR,
sum(case when RMDTYPAL in (1,3) then SLSAMNT + MISCAMNT – TRDISAMT else -1*(SLSAMNT + MISCAMNT – TRDISAMT) end) SALES,
count(*) num_of_sales
from RM30101
where
VOIDSTTS = 0 and year(DOCDATE) = ‘XXXX’ and month(docdate) = ‘XX’
and RMDTYPAL in (1,3,7,8)
group by CUSTNMBR) x
group by CUSTNMBR) sales

inner join
(select custnmbr, custname, max(AVGDTPYR) AVGDTPYR, max(PYMTRMID) PYMTRMID, max(PRCLEVEL) PRCLEVEL
from
(select rm00101.custnmbr, rm00101.custname, rm00103.AVGDTPYR, rm00101.PYMTRMID, rm00101.PRCLEVEL
from rm00101
inner join rm00103 on
rm00101.custnmbr = rm00103.custnmbr
) x
group by CUSTNMBR, CUSTNAME) cust
on sales.CUSTNMBR = cust.CUSTNMBR

left outer join
(select CUSTNMBR, sum(COST) cost
from
(select
h.CUSTNMBR,
sum(case when d.SOPTYPE = 3 and d.EXTDCOST <> 0 then d.EXTDCOST
when d.SOPTYPE = 3 and d.EXTDCOST = 0 then d.XTNDPRCE *.78
when d.SOPTYPE = 4 and d.EXTDCOST <> 0 then d.EXTDCOST * -1
when d.SOPTYPE = 4 and d.EXTDCOST = 0 then d.XTNDPRCE *-.78 end) COST
from SOP30200 h
inner join SOP30300 d
on h.SOPTYPE = d.SOPTYPE and h.SOPNUMBE = d.SOPNUMBE
where
h.VOIDSTTS = 0 and year(h.DOCDATE) = ‘XXXX’ and month(docdate) = ‘XX’
and h.SOPTYPE in (3,4)
group by h.CUSTNMBR) x
group by CUSTNMBR) costs
on cust.CUSTNMBR = costs.CUSTNMBR

order by sales.custnmbr

set nocount off