Simple Selective Aggregation

[Roop]

I have what I thought was a simple query in that I wish to return all those staff who are utilised part time during any 1 week.

In a table I need to return all those people who have between 1 and 4 available days during a standard week.

Available data:

RES_CODE      Work_Type        Days

AAAAAAAA       WorkDays         5

AAAAAAAA       Holiday              2

AAAAAAAA       FirmBook           3

AAAAAAAA       Available           0

BBBBBBBB       WorkDays          5

BBBBBBBB       Available           5

CCCCCCC        WorkDays          5

CCCCCCC        Holiday               3

CCCCCCC        Available            2

DDDDDDD        WorkDays          5

DDDDDDD        Holiday               1

DDDDDDD        FirmBook           1

DDDDDDD        Available            3

Available = WorkDays - Holiday - Firm

Therefore from the above set, I would want to return 2 (as both CCCCCCC and DDDDDDD fulfill the crtieria required of Available greter than 0 and less than 5). Obviously the set is a lot larger than the above.

Any help would be gratefully received!

All of the work types are from different data sources .......

[lironbaram]

count({<Work_Type={Available},Days={">=1 <=4"}>} Distinct RES_CODE)

[Roop]

A great speedy response ... ! I should have said that the line Available does not exist as all the other values are from separate sources. Sorry, I should have made myself clearer !!!!!

[lironbaram · Accepted solution]

sum((aggr(if(sum(Days)>=1 and sum(Days)<=4,1,0), RES_CODE))

[Roop]

Thank you very much - My rule is much more complex, but that part now works !!!