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

[Bob Cagle <bcagle@xxxxxxxxxxx>]

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