|
The CTE: I will get there. At some point. :)
On Fri, Apr 13, 2018 at 1:16 PM, Charles Wilt <charles.wilt@xxxxxxxxx>
wrote:
Look at the intermediate table...and
SELECT m.cusnr,
201802 AS YYYYMM,
cat.ctcode AS category,
s.invdat,
s.itnsa AS Sales,
c.dtwrtiso,
c.cdtam AS Credits
FROM DBMSTF.CUSRBTMST m
CROSS JOIN DBMSTF.DMCATMST cat
LEFT JOIN SLSHST.SLSALL s ON m.cusnr = s.cusnr AND s.catcd1 =
cat.ctcode
LEFT JOIN DBMSTF.CRDMEDTL c ON m.cusnr = c.cusnr AND c.catcd1 =
cat.ctcode
WHERE s.INVDT BETWEEN '2018-02-01' AND '2018-02-28' AND
c.DTWRTISO BETWEEN '2018-02-01' AND '2018-02-28'
ORDER BY 1, 2
I suspect we're seeing a cross-join between sales & credits on (cusnr,
catcd1)
I was afraid of that yesturday, but thought of it after I'd signed off
the tables I was testing with were in QTEMP :)so I
The CTE solution would work ;)
Charles
On Fri, Apr 13, 2018 at 10:45 AM, Jeff Crosby <jlcrosby@xxxxxxxxxxxxxxxx
wrote:
I used cat.ctcode first. Then I thought maybe that was the problem,
ofswitched to COALESCE(s.catcd1, c.catcd1, ' ') . Same results eitherway.
I probably won't get to work on it anymore today. :(
On Fri, Apr 13, 2018 at 12:30 PM, Charles Wilt <charles.wilt@xxxxxxxxx
wrote:it?
The CTE is actually easier to understand IMO...
As you can see what's built at each step...
But if you don't want to go the CTE route, how about the post without
your
I see you added DMCATMST...but you're not using the ctcode column in
jlcrosby@xxxxxxxxxxxxxxxx>SELECT & GROUP BY
Try
SELECT m.cusnr,
201802 AS YYYYMM,
cat.ctcode AS category,
COALESCE(SUM(s.itnsa), 0) AS Sales,
COALESCE(SUM(c.cdtam), 0) AS Credits
FROM DBMSTF.CUSRBTMST m
CROSS JOIN DBMSTF.DMCATMST cat
LEFT JOIN SLSHST.SLSALL s ON m.cusnr = s.cusnr AND s.catcd1 =
cat.ctcode
LEFT JOIN DBMSTF.CRDMEDTL c ON m.cusnr = c.cusnr AND c.catcd1 =
cat.ctcode
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, cat.ctcode
ORDER BY 1, 2
Charles
On Fri, Apr 13, 2018 at 9:57 AM, Jeff Crosby <
maywrote:
Charles
Did you see my other posts?
With the CTE? Yea. Trying to avoid that. If it's that complex I
charles.wilt@xxxxxxxxxnotstuff.
go the SQL route at all. When I look at it a year from now I won't
remember . . .
My issue is my job is more corporate management, by far, than tech
Don't get enough immersion in it.
On Fri, Apr 13, 2018 at 11:32 AM, Charles Wilt <
assume
wrote:jlcrosby@xxxxxxxxxxxxxxxx>
On Fri, Apr 13, 2018 at 7:51 AM, Jeff Crosby <
wrote:standpoint
I get these results:
Cust YYYYMM Category Sales Credits
==== ====== ======== ======= =======
1892 201802 E 6967.72 8400.20
1892 201802 F 3540.22 5097.12
4554 201802 B 3556.01 3406.56
4554 201802 D 1004.97 163.76
4554 201802 E 5706.34 6344.00
(Total) 20775.26 23411.64
I note a few things:
1) more credit than sales, never a good thing from a business
:)
2) numbers obviously not right
3) if there are sales and no credits, no record appears and I
mailingthe
reverse would be true
As expected, the fan out is the problem...
Did you see my other posts?
Charles
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L)
affiliatelist
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
link: http://amzn.to/2dEadiD
--
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
mymailing
mailingcompany. Unless I say so.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L)
affiliatelist
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
link: http://amzn.to/2dEadiD--
This is the Midrange Systems Technical Discussion (MIDRANGE-L)
affiliatelist
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
listlistlink: http://amzn.to/2dEadiD
--
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
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
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
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
--
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
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.