Re: Need an SQL joining lesson

[Charles Wilt <charles.wilt@xxxxxxxxx>]

You're running into a well known issue call "fan-out"

Here's a nice animated picture
https://discourse.looker.com/t/outer-join-on-false-or-how-i-learned-to-stop-fanning-out-and-love-the-null/4786

The FULL OUT JOIN solution that page presents is a new one to me..I'm going
to have to play with it. :)

How I'd normally solve the problem would be with CTEs that summarize the
data first, then join the CTEs.

The key is that you want a single row for Customer, Category,
YearMonth...so you use CTEs to transform the raw data into that...
-- Note make sure DATFMT is *ISO
with sales as (
  select cusnr, catcd1, substr(char(invdt),1,7) as YearMonth
         sum(itnsa) as total
    from slshst.slsall
    where invdat between '2018-02-01' AND '2018-02-28'
    group by cusnr, catcd1, substr(char(invdt),1,7)
), credits as  (
  select cusnr, catcd1, substr(char(dtwrtiso),1,7) as YearMonth
         sum(cdtam) as total
    from dbmstf.crdmedtl
    where dtwrtiso between '2018-02-01' AND '2018-02-28'
    group by cusnr, catcd1, substr(char(dtwrtiso),1,7)
), custs_cats as (
  select cst.cusnr, cat.catcd1, '2018-02' as YearMonth
   from  dbmstf.cusrbtmst cst
         cross join CATEGORY_MASTER cat
)
select cc.cusnr, cc.catcd1, cc.YearMonth
       , coalesce(sls.Total, 0) as sales_total
       , coalesce(crd.total, 0) as credit_total
 from  custs_cats cc
       left outer join sales sls
         on cc.cusnr = sls.cusnr
            and cc.catcd1 = sls.catcd1
            and cc.YearMonth = sls.YearMonth
       left outer join credits crd
         on cc.cusnr = crd.cusnr
            and cc.catcd1 = crd.catcd1
            and cc.YearMonth = crd.YearMonth;

Also note that a "Dates Table" or "Calendar Table", with a YearMonth column
among many others comes in quite handy for things like this.

Lots of date related queries are become much easier with such a table.

Charles




On Thu, Apr 12, 2018 at 1:49 PM, Jeff Crosby <jlcrosby@xxxxxxxxxxxxxxxx>
wrote:

> Not the greatest at SQL but have a project where I think it's a perfect fit
> so I want to figure this out.  It's ultimately going into an RPG program
> (as an INSERT statement), but the question at the moment is the SQL, not
> RPG.  That's why what you see below is a SELECT with stuff hardcoded.  I
> just want the right numbers first.
>
> Some customers are going to start getting rebates.  The rebate will a
> percent of (sales - credits) by item category for the month.  I want to
> insert into a file the following 5 fields:
>
> customer number
> YYYYMM
> category
> total sales for the month
> total credits for the month
>
> There is a file of customers and their rebate percents, the sales file
> (with the category field in it), and a credits file (with the category
> field in it).  There could be both sales and credits for a
> customer/category, only sales for a customer/category, or only credits for
> a customer/category.  In all 3 cases I want 1 record per customer/category.
>
> CUSRBTMST - rebates percents file
> SLSALL - sales file
> CRDMEDTL - credits file
>
> This is the SQL I have now:
>
> SELECT m.cusnr,
>                201802 AS YYYYMM,
>                COALESCE(s.catcd1, c.catcd1, ' ') AS category,
>                COALESCE(SUM(s.itnsa), 0) AS Sales,
>                COALESCE(SUM(c.cdtam), 0) AS Credits
>     FROM DBMSTF.CUSRBTMST m
>     JOIN SLSHST.SLSALL s ON m.cusnr = s.cusnr
>     JOIN DBMSTF.CRDMEDTL c ON m.cusnr = c.cusnr
>     WHERE s.INVDT BETWEEN '2018-02-01' AND '2018-02-28' AND
>                   c.DTWRTISO BETWEEN '2018-02-01' AND '2018-02-28'
>     GROUP BY m.CUSNR, s.catcd1, c.catcd1
>     ORDER BY m.CUSNR, s.catcd1, c.catcd1
>
> That's a copy/paste from ACS run SQL scripts that I formatted a bit
> further.
>
> The results I get have no resemblance to the actual sales and credits.  :)
>
> I'm joining the rebate percent file to both the sales and credits file.
> That might be the problem, I don't know.  Are 2 SELECTS required?
>
> Thanks for any pointers.
>
>
> --
>
>
> Jeff Crosby
> VP Information Systems
> UniPro FoodService/Dilgard
> P.O. Box 13369
> Ft. Wayne, IN 46868-3369
> 260-422-7531
> direct.dilgardfoods.com
>
> The opinions expressed are my own and not necessarily the opinion of my
> company.  Unless I say so.
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at https://archive.midrange.com/midrange-l.
>
> Please contact support@xxxxxxxxxxxx for any subscription related
> questions.
>
> Help support midrange.com by shopping at amazon.com with our affiliate
> link: http://amzn.to/2dEadiD