Hi guys. I have a SQL Report Builder query that pulls retrieves data in part based on the latest action by date. I am trying to get it to also pull in the last fee amount paid based on the date paid. Right now it is pulling in every fee paid, not just the latest.
You will see the fees part at the second INNER JOIN. I really appreciate any help.
SELECT
tblDataPermit.ApplicationNumber,
tblDataParcel.StreetNum,
tblDataParcel.StreetName,
tblDataPermit.Applicant,
tblDataPermit.XmlDataField.value('(//*[local-name()="ExtraText1"])[1]', 'nvarchar(max)') AS Cycle,
tblDataPermit.XmlDataField.value('(//*[local-name()="ExtraText2"])[1]', 'nvarchar(max)') AS ContactName,
tblDataPermit.XmlDataField.value('(//*[local-name()="ExtraText3"])[1]', 'nvarchar(max)') AS ConPhone,
tblDataPermit.XmlDataField.value('(//*[local-name()="ExtraYesNo2"])[1]', 'nvarchar(max)') AS ExtraYesNo2,
tblDataPermit.XmlDataField.value('(//*[local-name()="ExtraYesNo3"])[1]', 'nvarchar(max)') AS ExtraYesNo3,
tblDataPermit.ApplicationType AS Grp,
tblDataPermit.XmlDataField.value('(//*[local-name()="#ofUnits"])[1]', 'nvarchar(max)') AS NoOfUnits,
tblDataPermit.XmlDataField.value('(//*[local-name()="#SleepingUnits"])[1]', 'nvarchar(max)') AS NoOfRentalUnits,
tblDataFees.Amount,
tblDataFees.Date,
tblNumbersApplicationStatus.Label AS Status,
IStatuss.Status AS ADate,
MaxCompletionDates.MaxCompletionDate
FROM
tblDataPermit INNER JOIN tblDataParcel ON tblDataPermit.ParcelID = tblDataParcel.ParcelID
INNER JOIN
tblNumbersApplicationStatus ON tblDataPermit.Status = tblNumbersApplicationStatus.Number
INNER JOIN
tblDataFees ON tblDataPermit.ApplicationNumber = tblDataFees.ApplicationNumber
LEFT OUTER JOIN
(SELECT ProjectNumber, ExpirationDate FROM tblDataPermit AS tblDataPermit_2 WHERE (Type = '54')) AS ExpirationDates ON tblDataPermit.ApplicationNumber = ExpirationDates.ProjectNumber
LEFT OUTER JOIN
(SELECT ProjectNumber, Status FROM tblDataPermit AS tblDataPermit_3 WHERE (Type = '54')) AS IStatuss ON tblDataPermit.ProjectNumber = IStatuss.ProjectNumber
LEFT OUTER JOIN
(SELECT MAX(tblDataActions.CompletionDate) AS MaxCompletionDate, tblDataPermit_1.Type, tblDataPermit_1.ProjectNumber
FROM tblDataActions
INNER JOIN
tblDataPermit AS tblDataPermit_1 ON tblDataActions.ApplicationNumber = tblDataPermit_1.ApplicationNumber
GROUP BY tblDataPermit_1.Type, tblDataPermit_1.ProjectNumber
HAVING (tblDataPermit_1.Type = '54')) AS MaxCompletionDates ON tblDataPermit.ApplicationNumber = MaxCompletionDates.ProjectNumber
WHERE (tblDataPermit.Type = '61') AND Label IN (@AppStatus)
AND XmlDataField.value('(//*[local-name()="ExtraText1"])[1]', 'nvarchar(max)') IN (@Cycle)