Selecting "amount paid" based on most recent "date paid"

1

Table = tblDataFees
Column 1 = ApplicationNumber
Column 2 = AmountPaid
Column 3 = DatePaid

I use the following code to select the most recent DatePaid:

LEFT OUTER JOIN
(SELECT MAX(tblDataFees.Date) AS MaxPaidDate, ApplicationNumber
FROM tblDataFees
GROUP BY ApplicationNumber) AS MaxPaidDates ON tblDataPermit.ApplicationNumber = MaxPaidDates.ApplicationNumber

How do I code this to select the most recent AmountPaid based upon the most recent DatePaid?

2

Please provide sample table and data as

Create table sample(ApplicationNumber varchar(50) --etc

Insert into sample
Select 1, 'chicken ' union
-etc
3

hi
i tried to do this !!!!
hope this helps :slight_smile: :slight_smile:

please click arrow to the left for SAMPLE data Script 
drop table tblDataFees
go 

create table tblDataFees
(
ApplicationNumber int ,
AmountPaid int ,
DatePaid date 
)
go 

insert into tblDataFees select 1,120,'2019-01-09'
insert into tblDataFees select 2,200,'2019-04-15'
insert into tblDataFees select 3,450,'2019-10-23'
go 

select 'sample data',* from tblDataFees
go

image
image1110×248 5.31 KB

please click arrow to the left for SQL 
; WITH cte 
     AS (SELECT Max(datepaid) AS maxdtpaid 
         FROM   tbldatafees) 
SELECT * 
FROM   tbldatafees 
WHERE  datepaid = (SELECT maxdtpaid 
                   FROM   cte) 

go

image
image880×182 2.51 KB

4

First table:
tblDataPermit:
image

In the second table below, I need to select the PaidAmount based upon the latest PaidDate, which would be $450.

tblDataFees:
image

Here is how I select the latest DatePaid:

LEFT OUTER JOIN
(SELECT MAX(tblDataFees.Date) AS MaxPaidDate, ApplicationNumber
FROM tblDataFees
GROUP BY ApplicationNumber) AS MaxPaidDates ON tblDataPermit.ApplicationNumber = MaxPaidDates.ApplicationNumber

So I end up with:
image

5

Ok i understand you now. .

I will try tomorrow
Right now bed time

6

Give it a try and see if this gives you the result you need.

Test data 
DROP TABLE IF EXISTS dbo.tblDataPermit;
DROP TABLE IF EXISTS dbo.tblDataFees;

CREATE TABLE dbo.tblDataPermit(
	ApplicationNumber	INT	NOT NULL PRIMARY KEY
	, PermitNumber	INT	NOT NULL
	, IssueDate	DATE	NOT NULL
)

INSERT INTO dbo.tblDataPermit(ApplicationNumber, PermitNumber, IssueDate) VALUES
(1, 15, '20190323'),
(2, 16, '20190612'),
(3, 17, '20191013')

CREATE TABLE dbo.tblDataFees(
	id	INT	IDENTITY(1, 1) NOT NULL PRIMARY KEY
	, ApplicationNumber	INT	NOT NULL
	, Amount	INT	NOT NULL
	, PaidDate	DATE	NOT NULL
)

INSERT INTO dbo.tblDataFees(ApplicationNumber, Amount, PaidDate) VALUES
(1, 250, '20190323'),
(1, 150, '20190612'),
(1, 450, '20191013')

This should do it.

SQL script 
;WITH CTE AS(
SELECT ApplicationNumber
	, Amount
	, PaidDate
	, ROW_NUMBER() OVER (PARTITION BY ApplicationNumber ORDER BY PaidDate DESC) as RowNum
FROM dbo.tblDataFees as DF
)
SELECT DP.ApplicationNumber
	, DP.PermitNumber
	, CTE.Amount as AmountPaid
	, CTE.PaidDate
FROM CTE
	INNER JOIN dbo.tblDataPermit as DP
		ON CTE.ApplicationNumber = DP.ApplicationNumber
WHERE CTE.RowNum = 1