John Ellis
asked on
T-SQL--Two Different Totals Even with the Same Concept
Hello:
Both of the two sets of code below are to show the open accounts receivable balances of each customer.
I could swear that, by reviewing these, they would derive the same amounts for each customer record. But, they don't.
Why the heck not?
Thanks!
John
Both of the two sets of code below are to show the open accounts receivable balances of each customer.
I could swear that, by reviewing these, they would derive the same amounts for each customer record. But, they don't.
Why the heck not?
Thanks!
John
SELECT CM.CUSTNMBR, CM.CUSTNAME, CM.PYMTRMID, CM.CUSTCLAS, CM.COMMENT1, CM.COMMENT2, CN.CRDTMGR, CM.PRCLEVEL,
RM.RMDTYPAL AS DOCTYPE,
CASE RM.RMDTYPAL WHEN 1 THEN 'Sale/Invoice' WHEN 3 THEN 'Debit Memo' WHEN 4 THEN 'Finance Charge'
WHEN 5 THEN 'Service Repair' WHEN 6 THEN 'Warranty'
WHEN 7 THEN 'Credit Memo' WHEN 8 THEN 'Return' WHEN 9 THEN 'Payment' ELSE '0' END AS DOCTYPENAME, RM.DOCNUMBR,
RM.DOCDATE,
RM.DUEDATE,
RM.ORTRXAMT AS DOCAMNT, RM.CURTRXAM,
CASE WHEN RM.DUEDATE >= (SELECT LDOITAGD FROM RM40101) THEN 0
WHEN RM.RMDTYPAL IN (9) THEN DATEDIFF(DD, RM.DOCDATE, (SELECT LDOITAGD FROM RM40101))
ELSE DATEDIFF(DD, RM.DUEDATE, (SELECT LDOITAGD FROM RM40101)) END AS DAYSPASTDUE,
CS.LASTPYDT, CS.LPYMTAMT, CM.USERDEF1, CM.USERDEF2
FROM dbo.RM20101 AS RM INNER JOIN
dbo.RM00101 AS CM ON RM.CUSTNMBR = CM.CUSTNMBR INNER JOIN
dbo.RM00103 AS CS ON RM.CUSTNMBR = CS.CUSTNMBR LEFT OUTER JOIN
dbo.CN00500 AS CN ON RM.CUSTNMBR = CN.CUSTNMBR
WHERE (RM.VOIDSTTS = 0) AND (RM.CURTRXAM <> 0)
DECLARE @AGE DATETIME;
DECLARE @RUN DATETIME;
SET @AGE = '2015-09-30 00:00:00.000';
SET @RUN = '2016-07-31 00:00:00.000';
SELECT RM20101.CUSTNMBR as [CustomerID], RM00101.CUSTNAME as [CustomerName],
SUM(CASE WHEN RM20101.CURTRXAM = 0 THEN RM20101.ORTRXAMT ELSE RM20101.CURTRXAM END *
CASE WHEN RM20101.RMDTYPAL <= 6 THEN 1 ELSE -1 END)
AS [OPEN A/R]
from RM20101
INNER JOIN RM00101 ON RM20101.CUSTNMBR = RM00101.CUSTNMBR
where RM20101.VOIDSTTS = 0 and
((RM20101.RMDTYPAL <=6 AND RM20101.DUEDATE <= @AGE) OR
(RM20101.RMDTYPAL > 6 AND RM20101.DOCDATE <= @AGE))
GROUP BY RM20101.CUSTNMBR, RM00101.CUSTNAME
ORDER BY RM20101.CUSTNMBR
If you placed the same select clause on each, and removed any grouping of data, would you get identical data? e.g.
SELECT
RM.CUSTNMBR AS [CustomerID]
, RM.CUSTNAME AS [CustomerName]
, CASE
WHEN RM.CURTRXAM = 0 THEN RM.ORTRXAMT
ELSE RM.CURTRXAM
END
*
CASE WHEN RM.RMDTYPAL <= 6 THEN 1 ELSE -1 END
AS [OPEN A/R]
FROM dbo.RM20101 AS RM
INNER JOIN dbo.RM00101 AS CM ON RM.CUSTNMBR = CM.CUSTNMBR
INNER JOIN dbo.RM00103 AS CS ON RM.CUSTNMBR = CS.CUSTNMBR
LEFT OUTER JOIN dbo.CN00500 AS CN ON RM.CUSTNMBR = CN.CUSTNMBR
WHERE (RM.VOIDSTTS = 0)
AND (RM.CURTRXAM <> 0)
;
DECLARE @AGE DATETIME;
SET @AGE = '2015-09-30 00:00:00.000';
SELECT
RM.CUSTNMBR AS [CustomerID]
, RM.CUSTNAME AS [CustomerName]
, CASE
WHEN RM.CURTRXAM = 0 THEN RM.ORTRXAMT
ELSE RM.CURTRXAM
END
*
CASE WHEN RM.RMDTYPAL <= 6 THEN 1 ELSE -1 END
AS [OPEN A/R]
FROM RM20101 AS RM
INNER JOIN RM00101 AS CM ON RM.CUSTNMBR = CM.CUSTNMBR
WHERE RM.VOIDSTTS = 0
AND (
(RM.RMDTYPAL <= 6 AND RM.DUEDATE <= @AGE)
OR (RM.RMDTYPAL > 6 AND RM.DOCDATE <= @AGE)
)
;
Given that the upper query has 2 more tables, and they both have a very different where clause, I would be surprised if they produced identical results.
ASKER
Hi Portlet Paul:
I have always valued your expertise and wizardry. But, I'm afraid that I'm unclear on what you're asking and attempting to convey. Can you please expand upon what you're saying?
Thanks!
John
I have always valued your expertise and wizardry. But, I'm afraid that I'm unclear on what you're asking and attempting to convey. Can you please expand upon what you're saying?
Thanks!
John
Hi John,
What Paul sir is saying please run the last 2 selects that he has given and compare the data. If you are getting the same data or not?
Hope it helps!
What Paul sir is saying please run the last 2 selects that he has given and compare the data. If you are getting the same data or not?
Hope it helps!
ASKER
Yes, they are different. I see what you mean.
But, some different programmers here on the site and Back East basically intimated that they are the same programming.
How do I get the two sets of T-SQL programmings to be the same?
Thanks!
John
But, some different programmers here on the site and Back East basically intimated that they are the same programming.
How do I get the two sets of T-SQL programmings to be the same?
Thanks!
John
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
What exactly is your question?
Both the queries are totally different. First query is joined to below but these tables are not there in 2nd query.
dbo.RM00103 AS CS ON RM.CUSTNMBR = CS.CUSTNMBR LEFT OUTER JOIN
dbo.CN00500 AS CN ON RM.CUSTNMBR = CN.CUSTNMBR
Even the where clause is also different in both the cases.
Also columns in the SELECT are different.
Since both are different they will provide different results.
Hope it helps!