John Ellis
asked on
T-SQL: How to Display the Final Row of a Group of Rows from a Table
Hello:
Below is T-SQL code that I need help in modifying. After the code are the results that currently display.
I only want to pull the second record that is showing.
The distinguishing feature of this second record is that it is the fourth and final record in table RM20201 for the field RM20201.APFRDCNM. This second row represents one of four records in RM20201.APFRDCNM named "P10315EHF".
The first row shown below is one record above the fourth record in RM20201.APFRDCNM named "P10315EHF".
The third row is only one row in RM20201.APFRDCNM. In other words, the third row is only one record in RM20201.APFRDCNM and is called "P0494449A".
To recap, I want only the second of the three rows that are displaying.
How do I modify this code to pull only that second row?
Thank you!
John
Below is T-SQL code that I need help in modifying. After the code are the results that currently display.
I only want to pull the second record that is showing.
The distinguishing feature of this second record is that it is the fourth and final record in table RM20201 for the field RM20201.APFRDCNM. This second row represents one of four records in RM20201.APFRDCNM named "P10315EHF".
The first row shown below is one record above the fourth record in RM20201.APFRDCNM named "P10315EHF".
The third row is only one row in RM20201.APFRDCNM. In other words, the third row is only one record in RM20201.APFRDCNM and is called "P0494449A".
To recap, I want only the second of the three rows that are displaying.
How do I modify this code to pull only that second row?
Thank you!
DECLARE @AGE DATETIME;
DECLARE @RUN DATETIME;
SET @AGE = '2015-09-30 00:00:00.000';
SET @RUN = '2016-07-31 00:00:00.000';
;with cte8
as (
select RM20101.CUSTNMBR as [CustomerID], RM00101.CUSTNAME as [CustomerName], RM20101.DOCNUMBR as [DocumentNumber],
COUNT(*) OVER (PARTITION BY RM20101.CUSTNMBR, RM20201.APTODCNM) AS Cnt,
CASE WHEN RM20101.RMDTYPAL > 6 and RM20201.DATE1 > @AGE
and RM20201.APTODCDT > @AGE AND RM20201.APFRDCDT < @AGE
AND RM20201.ApplyFromGLPostDate < @AGE and RM20201.ApplyToGLPostDate > @AGE AND RM20101.DINVPDOF = ''
AND RM20101.DOCNUMBR in (select RM20201.APFRDCNM from RM20201
INNER JOIN RM20101 ON RM20201.CUSTNMBR = RM20101.CUSTNMBR AND RM20201.APFRDCNM = RM20101.DOCNUMBR
and RM20201.APPTOAMT <> RM20101.ORTRXAMT
GROUP BY RM20201.APTODCNM, RM20201.APTODCDT, RM20201.APFRDCNM)
AND
RM20101.ORTRXAMT <> RM20101.CURTRXAM
AND RM20201.APFRMAPLYAMT <> RM20101.ORTRXAMT
AND RM20101.DUEDATE <> ''
THEN RM20201.APFRMAPLYAMT * -1 ELSE 0 END
as [OPEN A/R]
from RM20101
INNER JOIN RM00101 on RM20101.CUSTNMBR = RM00101.CUSTNMBR
INNER JOIN RM20201 on RM20101.DOCNUMBR = RM20201.APFRDCNM AND RM20101.CUSTNMBR = RM20201.CUSTNMBR
LEFT OUTER JOIN CN00500 ON RM20101.CUSTNMBR = CN00500.CUSTNMBR
WHERE (RM20101.VOIDSTTS = 0) and (RM20101.CUSTNMBR IN (' 320651', '00065', '00351', '00370', '0035829', '0015200', '0100010149',
'0100011881', '0100011303', '0100011766', '0100012897',
'195145', '0100550714', '238624', '0100013309', '0100010453', '266267', '179520', '0100012517', '0100011519', '0100012160',
'0100012558', '0100075597', '0100075290', '0100354828', '274173', '0100014190', '0100020981', '00144', '0100015049', '0100014968',
'0100018654', '0100019363', '0100019371',
'0100020932', '0100021047', '0100024819', '0100030188', '0100014661', '0100015270', '0100032895', '0100022052',
'0100033398', 'WW_FLIGHT12', 'WEST_MD_HC01'))
GROUP BY RM20101.CUSTNMBR, RM00101.CUSTNAME, RM20201.ApplyFromGLPostDate, RM20201.ApplyToGLPostDate, RM20101.DINVPDOF,
RM20101.RMDTYPAL, RM20101.DUEDATE, RM20201.APFRMAPLYAMT, RM20101.DOCNUMBR, RM20201.APFRDCNM, RM20101.DOCDATE,
RM20201.DATE1, RM20201.APTODCDT, RM20201.APPTOAMT, RM20201.APTODCNM, RM20201.APFRDCDT, RM20101.ORTRXAMT, RM20101.CURTRXAM
HAVING
CASE WHEN RM20101.RMDTYPAL > 6 and RM20201.DATE1 > @AGE
and RM20201.APTODCDT > @AGE AND RM20201.APFRDCDT < @AGE
AND RM20201.ApplyFromGLPostDate < @AGE and RM20201.ApplyToGLPostDate > @AGE AND RM20101.DINVPDOF = ''
AND RM20101.DOCNUMBR in (select RM20201.APFRDCNM from RM20201
INNER JOIN RM20101 ON RM20201.CUSTNMBR = RM20101.CUSTNMBR AND RM20201.APFRDCNM = RM20101.DOCNUMBR
and RM20201.APPTOAMT <> RM20101.ORTRXAMT
GROUP BY RM20201.APTODCNM, RM20201.APTODCDT, RM20201.APFRDCNM)
AND
RM20101.ORTRXAMT <> RM20101.CURTRXAM
AND RM20201.APFRMAPLYAMT <> RM20101.ORTRXAMT
AND RM20101.DUEDATE <> ''
THEN RM20201.APFRMAPLYAMT * -1 ELSE 0 END
<> 0)
select * from cte8
where Cnt = 1John
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Again, Pawan:
Actually, I figured it out. It was simply a matter of changing a few things. First, I had to put in a clause in the first select statement to pull in the [ApplyFromDocumentNumber] from RM20201.APFRDCNM.
Secondly, I made a couple of modifications to your second select statement shown below. I substituted "[ApplyFromDocumentNumber]
That all worked! Thanks, for your excellent service!
John
SELECT * FROM
(
select * , ROW_NUMBER() OVER (PARTITION BY [CustomerID] ORDER BY [ApplyFromDocumentNumber] DESC) rnk from cte8
)K where rnk = 2
Actually, I figured it out. It was simply a matter of changing a few things. First, I had to put in a clause in the first select statement to pull in the [ApplyFromDocumentNumber] from RM20201.APFRDCNM.
Secondly, I made a couple of modifications to your second select statement shown below. I substituted "[ApplyFromDocumentNumber]
That all worked! Thanks, for your excellent service!
John
SELECT * FROM
(
select * , ROW_NUMBER() OVER (PARTITION BY [CustomerID] ORDER BY [ApplyFromDocumentNumber] DESC) rnk from cte8
)K where rnk = 2
Great John !
Cheers!
Cheers!
ASKER
I'm afraid that no results were returned.
John