Fecth Max Date
-
Hello. I am using this expression to try and bring in the MaxFailDate from the table below, which would be 2/1/2022.
=Lookup(Fields!ApplicationNumber.Value, Fields!ApplicationNumber.Value, Fields!FailDate.Value,"MaxFailDate")
I am not getting the desired result with that expression or the query below.
I would greatly appreciate assistance.
SELECT DISTINCT
tblDataPermit.ApplicationNumber,
tblDataPermit.ProjectNumber,
tblDataPermit.Status,
tblDataActions.ActionStatus,
tblDataActions.CompletionDate AS FailDate,
IStatuss.Status AS APPStatus,
MaxFailDates.MaxFailDate
FROM
tblDataPermit INNER JOIN tblDataParcel ON tblDataPermit.ParcelID = tblDataParcel.ParcelID
INNER JOIN tblDataActions ON tblDataActions.ApplicationNumber = tblDataPermit.ApplicationNumber
INNER JOIN
SysCod ON SysCod.CodCode = tblDataPermit.Status
INNER JOIN
(SELECT DISTINCT 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 MaxFailDate, tblDataPermit_1.Type, tblDataPermit_1.ApplicationNumber
FROM tblDataActions
INNER JOIN
tblDataPermit AS tblDataPermit_1 ON tblDataActions.ApplicationNumber = tblDataPermit_1.ApplicationNumber
GROUP BY tblDataPermit_1.Type, tblDataPermit_1.ApplicationNumber
HAVING (tblDataPermit_1.Type = '54')) AS MaxFailDates ON tblDataPermit.ApplicationNumber = MaxFailDates.ApplicationNumber
WHERE (tblDataPermit.Type = '54')
AND
tblDataActions.ActionStatus = ' 6'
AND
tblDataPermit.Status = ' 14'
.
-
I think the easiest way to help you is if you can:
1- provide sample data so we can run your query and get similar results
2- provide expected output
With point number 2 that is important because I don't know if you are looking for an extra column that would have the max fail date OR if you are only wanting the 1 row where the fail date is the max fail date or something else entirely. If I don't know what you expect in your output, it is hard to know how to help.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system. -
To simplify, I think, I just need that query to result in the one row with the maximum FailDate as the result, not all of the rows as shown above.
I can't believe I spelled Fetch wrong in the subject line.
-
What you could do then is change your SQL to include a TOP(1) and an order by FailDate DESC. Will that solve your issue?
Alternately, make your whole query a subquery and have the outside query filter by FailDate = MaxFailDate? That MAY result in more than 1 result though in the event that 2 FailDate = MaxFailDate results in more than 1 row.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system. -
These expressions got me there:
=Lookup(Fields!ProjectNumber.Value, Fields!ProjectNumber.Value, Fields!MaxFailDate.Value,"MaxFailDate")
=IIF(ReportItems!MaxPassDate.Value > ReportItems!Due.Value, "Pass", IIF(ReportItems!MaxFailDate.Value > ReportItems!MaxPassDate.Value ,"Fail", "Due"))
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply