Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 .......
sum((aggr(if(sum(Days)>=1 and sum(Days)<=4,1,0), RES_CODE))
count({<Work_Type={Available},Days={">=1 <=4"}>} Distinct RES_CODE)
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 !!!!!
sum((aggr(if(sum(Days)>=1 and sum(Days)<=4,1,0), RES_CODE))
Thank you very much - My rule is much more complex, but that part now works !!!