We help IT Professionals succeed at work.

T-SQL: Using the PARTITION Clause to Retrieve Only One Record

76 Views
Last Modified: 2017-06-20
Hello:

At the end of this posting is T-SQL code that I modified by using a PARTITION clause, and the first screenshot shows the results of running this code.

The third record shown in that screenshot is the record that I do not want.  

If you review the second screenshot, you'll see that this record appears in row 3 of the "bottom" RM20201 table.  Its corresponding record in the RM20101 table is row 26 of RM20101.

Pertaining to these two screenshots, there are three records in RM20101 and four records in RM20201.  

Rows 1 and 2 of RM20201 correspond to rows 27 and 28 of RM20101, respectively.  This is seen in RM20101's ORTRXAMT field.  

Rows 3 and 4 of RM20201, however, correspond to only one record in RM20101--row 26.

I don't want data derived from my code that represents two records in RM20201 and only one corresponding record in RM20101.  So, I want the third record derived from my code and shown in the first screenshot "removed".

How do I modify my syntax, to accommodate this?

Thank you!

John

Results from CodeRM20101 and RM20201
DECLARE @AGE DATE
DECLARE @BEGINDATE DATE
DECLARE @ENDDATE DATE

SET @AGE = '09/30/2015'
SET @BEGINDATE = '10/01/2015' 
SET @ENDDATE = '09/30/2016'

--the following clause contains returns that are partially applied:
--UNION
select 
RM20101.CUSTNMBR as [CustomerID], RM00101.CUSTNAME as [CustomerName], CN00500.USERDEF2 as [GroupBillingName], RM20101.DOCNUMBR,
SUM(RM20201.APFRMAPLYAMT * -1) 
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 RM20201.ApplyFromGLPostDate, RM20201.APFRMAPLYAMT
                              ORDER BY DATE1) rn 
            FROM RM20201 
     where RM20201.DATE1 >= @AGE and RM20201.APFRDCDT <= @AGE and RM20201.APTODCDT <= @AGE) RM20201 
ON RM20101.CUSTNMBR = RM20201.CUSTNMBR AND RM20101.DOCNUMBR = RM20201.APFRDCNM AND RM20201.rn = 1
--INNER JOIN RM20201 ON RM20101.CUSTNMBR = RM20201.CUSTNMBR AND RM20101.DOCNUMBR = RM20201.APFRDCNM
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 RM20201.APFRDCDT <= @AGE and RM20101.POSTDATE <= @AGE and RM20201.APTODCDT <= @AGE
and RM20101.DUEDATE <> '' and RM20101.DUEDATE <> @AGE 
and RM20101.RMDTYPAL IN (8) 
and RM20101.VOIDSTTS = 0 and RM20101.CUSTNMBR IN ('0100014190')
GROUP BY RM20101.CUSTNMBR, RM00101.CUSTNAME, CN00500.USERDEF2, RM20101.DOCNUMBR

Open in new window

Comment
Watch Question

Sharath SData Engineer
CERTIFIED EXPERT

Commented:
Rows 3 and 4 of RM20201, however, correspond to only one record in RM20101--row 26.
How do you conclude that? Is there a  column in RM20201that says that row 3 and 4 are together?

Author

Commented:
Hi Sharath:

For rows 3 and 4 of the RM20201 table, if you add 568.53 and 10.44 from the APFRMAPLYAMT field, you derive 578.97.  

This figure is in row 26 of the ORTRXAMT field in the RM20101 table.

John
Sharath SData Engineer
CERTIFIED EXPERT

Commented:
Understand that. But why do you add row 3 and 4 only? why not row 1 and 2 or 1 and 3 or any other combination and check if APFRMAPLYAMT matches with any record in RM20101? What is the criteria to add row 3 and 4 only? Is there any other column that drives this?

Author

Commented:
RM20101.DOCNUMBR drives this.
Sharath SData Engineer
CERTIFIED EXPERT

Commented:
Ok. If ORTRXAMT  doesn't match with APFRMAPLYAMT, do you still want to remove row 3?

Author

Commented:
Yes.  Thank you!  :)

John
Sharath SData Engineer
CERTIFIED EXPERT

Commented:
try this.
DECLARE @AGE DATE
DECLARE @BEGINDATE DATE
DECLARE @ENDDATE DATE

SET @AGE = '09/30/2015'
SET @BEGINDATE = '10/01/2015' 
SET @ENDDATE = '09/30/2016'

--the following clause contains returns that are partially applied:
--UNION
select 
RM20101.CUSTNMBR as [CustomerID], RM00101.CUSTNAME as [CustomerName], CN00500.USERDEF2 as [GroupBillingName], RM20101.DOCNUMBR,
SUM(RM20201.APFRMAPLYAMT * -1) 
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 RM20201.ApplyFromGLPostDate, RM20201.APFRMAPLYAMT
                              ORDER BY DATE1) rn,
                              COUNT(*) OVER (PARTITION BY CUSTNMBR, APFRDCNM) Cnt  
            FROM RM20201 
     where RM20201.DATE1 >= @AGE and RM20201.APFRDCDT <= @AGE and RM20201.APTODCDT <= @AGE) RM20201 
ON RM20101.CUSTNMBR = RM20201.CUSTNMBR AND RM20101.DOCNUMBR = RM20201.APFRDCNM AND RM20201.rn = 1 AND Cnt = 1
--INNER JOIN RM20201 ON RM20101.CUSTNMBR = RM20201.CUSTNMBR AND RM20101.DOCNUMBR = RM20201.APFRDCNM
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 RM20201.APFRDCDT <= @AGE and RM20101.POSTDATE <= @AGE and RM20201.APTODCDT <= @AGE
and RM20101.DUEDATE <> '' and RM20101.DUEDATE <> @AGE 
and RM20101.RMDTYPAL IN (8) 
and RM20101.VOIDSTTS = 0 and RM20101.CUSTNMBR IN ('0100014190')
GROUP BY RM20101.CUSTNMBR, RM00101.CUSTNAME, CN00500.USERDEF2, RM20101.DOCNUMBR

Open in new window

Author

Commented:
Hi Sharath:

The 10.44 record still appears.

John
Data Engineer
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
The customer number is the same for all of these records.
Sharath SData Engineer
CERTIFIED EXPERT

Commented:
Can you provide sample data from your tables in csv or like INSERT statements that I can test at my end.

Author

Commented:
Attached are the files.

Thank you!

John

RM20101.csv
RM20201.csv
Vitor Montalv√£oIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
John, do you still need help with this question?

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.