4 Replies Latest reply: Sep 7, 2011 9:30 AM by Rupert Cavendish RSS

    Simple Selective Aggregation

    Rupert Cavendish

      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 .......