|
This script is calculating Gross Sales, Returns, Customer Discounts,
Net Sales, and Commissions. The problem column is the Commissions.
I've tried casting it to CCSID 37 with no change in the results.
This is pulling data from JD Edwards, which stores amounts as 15,0 and
dates as Julian, so that's why there's some odd division going on and
Julian data range selections.
Here it is:
with cte_GrossSales as (
Select sdpa8 as Customer, sdlitm as Item, sum(sduorg) as
GrossQty, sum(sdaexp/100) as GrossSales, sum(sdecst/100) as Cost
from lynkdta.f42119
where sddcto not in ('CO', 'CI', 'ST') and
sdlnty <> 'F' and
sdpa8 <> 846 and
sddgl between 116245 and 116274
group by sdpa8, sdlitm
order by sdpa8, sdlitm
),
cte_Returns as (
Select sdpa8 as Customer, sdlitm as Item, sum(sduorg) as
ReturnQty, sum(sdaexp/100) as Returns
from lynkdta.f42119
where sddcto in ('CO', 'CI') and
sdlnty <> 'F' and
sdpa8 <> 846 and
sddgl between 116245 and 116274
group by sdpa8, sdlitm
order by sdpa8, sdlitm
),
cte_DiscountRate as (
Select OAAN8 as Customer, sum(OADISC) as DiscountRate
from lynkdta.Mrgnex01pf
group by OAAN8
order by OAAN8
),
cte_Discount as (
Select cte_GrossSales.Customer,
cte_GrossSales.Item,
sum(cte_GrossSales.GrossSales *
cte_DiscountRate.DiscountRate/100) as Discount
from cte_GrossSales
left join cte_DiscountRate on cte_GrossSales.Customer=cte_
DiscountRate.Customer
group by cte_GrossSales.Customer, cte_GrossSales.Item
order by cte_GrossSales.Customer, cte_GrossSales.Item
),
cte_NetSales as (
Select cte_GrossSales.Customer,
cte_GrossSales.Item,
sum(cte_GrossSales.GrossSales -
(case when cte_Returns.Returns is null then 0 else
abs(cte_Returns.Returns) end) -
(case when cte_Discount.Discount is null then 0
else cte_Discount.Discount end)) as NetSales
from cte_GrossSales
left join cte_Returns on cte_GrossSales.Customer=cte_Returns.Customer
and
cte_GrossSales.Item=cte_Returns.Item
left join cte_Discount on cte_GrossSales.Customer=cte_Discount.Customer
and
cte_GrossSales.Item=cte_
Discount.Item
group by cte_GrossSales.Customer, cte_GrossSales.Item
order by cte_GrossSales.Customer, cte_GrossSales.Item
),
cte_CommRate as (
Select A5AN8 as Customer, (A5CMR1 + A5CMR2)/1000 as CommRate
from lynkdta.F0301
order by A5AN8
),
cte_Comms as (
Select cte_NetSales.Customer,
cte_NetSales.Item,
sum(cte_NetSales.NetSales *
(cte_CommRate.CommRate/100)) as Commission
from cte_NetSales
left join cte_CommRate on cte_NetSales.Customer=cte_
CommRate.Customer
group by cte_NetSales.Customer, cte_NetSales.Item
order by cte_NetSales.Customer, cte_NetSales.Item
)
select cte_GrossSales.Customer as "Customer",
cte_GrossSales.Item as "Item",
cte_GrossSales.GrossQty as "Gross Qty",
cast(cte_GrossSales.GrossSales as dec(9,2)) as "Gross Sales",
cte_Returns.ReturnQty as "Return Qty",
cast(cte_Returns.Returns as dec(9,2)) as "Returns",
cast(cte_DiscountRate.DiscountRate as dec(5,2)) as
"Discount Percent",
cast(cte_Discount.Discount as dec(9,2)) as "Discount",
cast(cte_NetSales.NetSales as dec(9,2)) as "Net Sales",
cast(cte_CommRate.CommRate as dec(5,2)) as "Comm Rate",
cast(cte_Comms.Commission as char(50) ccsid 37) as
"Commission",
cast(cte_GrossSales.Cost as dec(9,2)) as "Cost"
from cte_GrossSales
left join cte_Returns on cte_GrossSales.Customer=cte_Returns.Customer
and
cte_GrossSales.Item=cte_Returns.Item
left join cte_DiscountRate on cte_GrossSales.Customer=cte_
DiscountRate.Customer
left join cte_Discount on cte_GrossSales.Customer=cte_Discount.Customer
and
cte_GrossSales.Item=cte_Discount.Item
left join cte_NetSales on cte_GrossSales.Customer=cte_NetSales.Customer
and
cte_GrossSales.Item=cte_NetSales.Item
left join cte_CommRate on cte_GrossSales.Customer=cte_CommRate.Customer
left join cte_Comms on cte_GrossSales.Customer=cte_Comms.Customer and
cte_GrossSales.Item=cte_Comms.Item
where cte_GrossSales.GrossSales <> 0 and cte_CommRate.CommRate <> 0
order by cte_GrossSales.Customer, cte_GrossSales.Item
Thanks
Bob Cagle
IT Manager
Lynk
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.