count groups with given value

[nischwa]

I have the following table:

id	caseID	status	date
1	1	open	01.05.2019
2	1	working	02.05.2019
3	1	pending	03.05.2019
4	1	closed	04.05.2019
5	2	open	02.05.2019
6	2	working	04.05.2019
7	2	pending	05.05.2019
8	3	open	03.05.2019
9	3	working	09.05.2019
10	4	open	04.05.2019

and i want to count cases with status 'working' or 'pending' as the last status.

The result should be 2. It should count caseID 2 and 3.

I tryed alot, but nothing worked. Best result was:

Sum(
 {<status = {'working','pending'}>} Aggr(
     If(
         Max({<status = {'working','pending'}>} TOTAL <caseID> date) = Only({<status = {'working','pending'}>} date), Sum({<status = {'working','pending'}>} 1), 0
        ), caseID, date)
)

On a SQL Server i would do it like this:

SELECT        P.caseID, D.department
FROM            Cases AS P LEFT OUTER JOIN
                         departments AS D ON P.costunit = D.costunit
WHERE        (P.statusDate =
                             (SELECT        MAX(statusDate) AS Expr1
                               FROM            Cases
                               WHERE        (caseID = P.caseID))) AND (P.status IN ('working', 'pending'))

Please help 🙂

[Mauritz_SA]

Hi there

This should work:

Sum(Aggr(If(Max(date)=Max({<status = {'working','pending'}>} date),1,0),caseID))

Regards,

Mauritz

[sunny_talwar]

Another option is to use set analysis...

=Count(DISTINCT {<caseID = {"=Max(date) = Max({<status = {'working','pending'}>} date)"}>} caseID)