Software Engineer
asked on
T-SQL: Number of Records is Greater Than One
Hello:
Below is my code and just above that are the results.
Above the code and the results are the RM20101 and RM20201 tables.
To make a longer story short, I don't want record #7 in the results.
The DOCNUMBR of record #7 is listed more than once in RM20201. So, how do I modify my syntax to pierce through the "more than one record for the same field" loophole? :)
Thanks!
John
RM20101.rpt
RM20201.rpt
Below is my code and just above that are the results.
Above the code and the results are the RM20101 and RM20201 tables.
To make a longer story short, I don't want record #7 in the results.
The DOCNUMBR of record #7 is listed more than once in RM20201. So, how do I modify my syntax to pierce through the "more than one record for the same field" loophole? :)
Thanks!
John
RM20101.rpt
RM20201.rpt
select
RM20101.CUSTNMBR as [CustomerID], RM00101.CUSTNAME as [CustomerName], CN00500.USERDEF2 as [GroupBillingName], RM20101.DOCNUMBR,
SUM(DISTINCT RM20101.ORTRXAMT) as [OPEN A/R], 0 as [Payments],
0 as [Credits], 0 as [Returns], 0 as [Writeoffs]
from RM20101
INNER JOIN RM20201 ON RM20101.CUSTNMBR = RM20201.CUSTNMBR AND RM20101.DOCNUMBR = RM20201.APTODCNM
INNER JOIN RM00101 ON RM20101.CUSTNMBR = RM00101.CUSTNMBR
LEFT OUTER JOIN CN00500 ON RM00101.CUSTNMBR = CN00500.CUSTNMBR
where RM20201.DATE1 >= @AGE and RM20101.DOCDATE <= @AGE
and RM20101.DINVPDOF <> @AGE and RM20101.DINVPDOF <> '' and RM20201.APFRDCDT < @AGE and RM20101.DUEDATE < @AGE
and RM20101.ORTRXAMT = RM20201.APPTOAMT
and RM20101.RMDTYPAL IN (1,3,4,5,6) and RM20201.APFRDCTY = 8
and RM20101.VOIDSTTS = 0 and RM20101.CUSTNMBR IN ('0015200', 'SVC989E')
GROUP BY RM20101.CUSTNMBR, RM00101.CUSTNAME, CN00500.USERDEF2, RM20101.DOCNUMBR
give a try. change the ORDER BY column however you want in ROW_NUMBER function.
SELECT RM20101.CUSTNMBR AS [CustomerID],
RM00101.CUSTNAME AS [CustomerName],
CN00500.USERDEF2 AS [GroupBillingName],
RM20101.DOCNUMBR,
Sum(DISTINCT RM20101.ORTRXAMT) AS [OPEN A/R],
0 AS [Payments],
0 AS [Credits],
0 AS [Returns],
0 AS [Writeoffs]
FROM RM20101
INNER JOIN (SELECT *,
ROW_NUMBER()
OVER (
PARTITION BY DOCNUMBR
ORDER BY DATE1) rn
FROM RM20201) RM20201
ON RM20101.CUSTNMBR = RM20201.CUSTNMBR
AND RM20101.DOCNUMBR = RM20201.APTODCNM
AND RM20201.rn = 1
INNER JOIN RM00101
ON RM20101.CUSTNMBR = RM00101.CUSTNMBR
LEFT OUTER JOIN CN00500
ON RM00101.CUSTNMBR = CN00500.CUSTNMBR
WHERE RM20201.DATE1 >= @AGE
AND RM20101.DOCDATE <= @AGE
AND RM20101.DINVPDOF <> @AGE
AND RM20101.DINVPDOF <> ''
AND RM20201.APFRDCDT < @AGE
AND RM20101.DUEDATE < @AGE
AND RM20101.ORTRXAMT = RM20201.APPTOAMT
AND RM20101.RMDTYPAL IN ( 1, 3, 4, 5, 6 )
AND RM20201.APFRDCTY = 8
AND RM20101.VOIDSTTS = 0
AND RM20101.CUSTNMBR IN ( '0015200', 'SVC989E' )
GROUP BY RM20101.CUSTNMBR,
RM00101.CUSTNAME,
CN00500.USERDEF2,
RM20101.DOCNUMBR
ASKER
Hi Sharath:
Thanks, for the help! The only thing is that DOCNUMBR does not exist in the RM20201 table. So, using the APTODCNM field instead in the PARTITION clause yielded the same results.
Any thoughts?
Thanks, again!
John
Thanks, for the help! The only thing is that DOCNUMBR does not exist in the RM20201 table. So, using the APTODCNM field instead in the PARTITION clause yielded the same results.
Any thoughts?
Thanks, again!
John
ASKER
RM20101.csv
RM20201.csv
Attached are .csv versions of the RM20101 and RM20201 tables.
Thank you.
John
RM20201.csv
Attached are .csv versions of the RM20101 and RM20201 tables.
Thank you.
John
The change of partitioning you propose is fine.
ASKER
But, I got the same results that I didn't want.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
At that point you accepted my answer which was to use "select distinct" in a subquery (of just the columns you need) from RM20201
Alternatively you could use "group by" on RM20201 in a subquery instead if you need any aggregated values.
Do you have any other way of providing your data? (.csv? tab delimited? Excel?)
I find those fixed width .rpt files difficult to deal with particularly as there are so many columns not relevant to the query in question.