Hi Folks,
Going round in circles with this, maybe you can help.
I have a list report showing in progress/completed work across three unique sections.
As work is completed in a particular section, the percentage goes up until it reaches 100% (Or null if the section has no work)
Sections is this case are 'FULL/TWR%/MEZZ%'
Timestamp column is 'COMPLETED'
What I need to create is a query that displays the final(maximum) timestamp once all work (all sections) is completed,
and to display nothing until this happens.
As I mentioned occasionally a section can have no work so the approach I have taken is as follows:
CASE WHEN([FULL] IS NULL OR [FULL]= 100)
AND ([TWR %] IS NULL OR [TWR %]= 100)
AND ([MEZZ %] IS NULL OR [MEZZ %]= 100)
THEN(MAXIMUM([COMPLETED]))
ELSE NULL END
While I do get the odd hit that lines up, it is clearly not correct.
Any thoughts??
Cheers,
Locus
Try flipping it inside out:
maximum
(
CASE WHEN ([FULL] IS NULL OR [FULL]= 100)
AND ([TWR %] IS NULL OR [TWR %]= 100)
AND ([MEZZ %] IS NULL OR [MEZZ %]= 100)
THEN([COMPLETED])
ELSE NULL
END
)