RE: Run SQL Scripts - '+++++' in results

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