I would like to get the latest completion record of the Job detail of the job. However, i encounter the Max() issue in getting the text value. Would anyone help advise on my sql query.
My example here:
I would like to get the Max completion record
Below is the the job details of a job, the result of my SQL need to get the highlighted record, but I dun know how to get the phase description(signed accounts retturned from auditors after completion), and my query result get the right phase code but wrong description (signed accounts sent to auditors for completion)
- I believe that if i get the max(phase.description), it is ordered by alphabet.
My query :
Select {JobMaintenance2}.[Id],
{JobMaintenance2}.[JobCode],
{CompanyGroup}.[CompanyGroup],
{Company2}.[FullName],
{JobMaintenance2}.[YearEndDate], {Team}.[Team],
{Auditor}.[Auditor],{ReportType}.[ReportType],
MAX({JobDetail}.[CompletionDate]),
CONCAT(MAX({Phase}.[Phasecode]),' ',max({Phase}.[Description])),
from {JobDetail}
LEFT JOIN {JobMaintenance2} on {JobMaintenance2}.[Id]={JobDetail}.[JobMaintenance2Id]
LEFT JOIN {Company2} on {JobMaintenance2}.[Company2Id]={Company2}.[Id]
LEFT JOIN {Team} on {Team}.[Id]={JobMaintenance2}.[TeamId]LEFT JOIN {Phase} on {Phase}.[Id]={JobDetail}.[PhaseId]
LEFT JOIN {CompanyGroup} ON {CompanyGroup}.[Id]={JobMaintenance2}.[CompanyGroupId]
LEFT JOIN {Auditor} on {Auditor}.[Id]={JobMaintenance2}.[AuditorId]
LEFT JOIN {ReportType} on {ReportType}.[Id]={JobMaintenance2}.[ReportTypeId]
WHERE {JobMaintenance2}.[Id]<>0 and {JobMaintenance2}.[Id]={JobDetail}.[JobMaintenance2Id] and {JobDetail}.[CompletionDate]<>'1900-01-01'
GROUP BY {JobMaintenance2}.[JobCode], {JobMaintenance2}.Id, {Team}.[Team], {JobMaintenance2}.[YearEndDate], {Company2}.[FullName], {CompanyGroup}.[CompanyGroup], {Auditor}.[Auditor], {ReportType}.[ReportType]
ORDER BY {JobMaintenance2}.[YearEndDate], {JobMaintenance2}.[Id] ASC, {JobMaintenance2}.[JobCode] ASC;
And my result:
- Phase description are wrong, 18000 is not refer to this description
-
So i got problem in this part,
1. how can i not max the phase.description but get the related phase.description of Max(phase.phasecode).
Please advise. Thanks.
Hi Winnie Lam,
It seems PhaseCode is integer
Try then
MAX( STR( {Phase}.[PhaseCode] ) + ' ' + {Phase}.[Description] ) ,
For line 10
Hope that it helps you
It works. Thanks for your advice!!
Hi @Winnie Lam , indeed if you use MAX() of a text, evaluates the values in alphabetic order and will return the "bigger"/last one.
But just a genuine question (I may be missing something but) if you want to fetch the latest completed record, why don't you ORDER BY JobDetail.CompletionDate DESC?
This would return on top the record with the latest completion date, which from I understood is what you need. Then you would simply pick up the phase code & description of that record, no need to do MAX, as you already know that's the record you want.
Also it is indeed possible to have a different phasecode and description not belonging to that phasecode, since you are applying MAX to both of them. What this means is imagine you have the following phases:
CODE DESC
1500 ZED
1600 ABC
Your query in this case would return 1600 ZED, because it picks up the MAX code and MAX desc
Let me know if ordering by JobDetail.CompletionDate DESC does the trick!
Thank you,
Paulo
Try
MAX( {Phase}.[PhaseCode] + ' ' + {Phase}.[Description] ) ,
If I use the suggested one, seems show error on it.