SQL Query Help - Resource Availability

I'm struggling with a suitable approach to the following query in MS SQL 2008-

I have a table tracking employee project allocations, essentially in the following form:

• AllocID int pk
• ProjectID int fk
• EmplID int fk
• AllocPct decimal
• AllocStartDate datetime
• AllocEndDate datetime

That is, an employee is assigned to a project for a specified time period and percentage. As a function of this data, I'm trying to quickly query the date at which an employee has available capacity - that is, the earliest date where the SUM(AllocPct) < 1.

I'm stuck on a set-based approach to this query that can ideally could handle a set of employees (i.e returning this field as a column for all employees). I have a "working" query (below) that could be used for an individual employee, but it's a mess (numbers table, nested, etc); there has to be a better way to approach this.

SELECT     MIN(DateDay) AS DateAvailable
FROM         (SELECT     b.EmplID, a.DateDay, SUM(b.AllocPct) AS SumAllocPct
                       FROM          p_AllocDetailDynView AS b INNER JOIN
                                                  (SELECT     DATEADD(day, Number - 1, GETDATE()) AS DateDay
                                                    FROM          master.dbo.Numbers AS n) AS a ON b.AllocEndDate > a.DateDay
                       WHERE      (b.EmplID = @emplID)
                       GROUP BY a.DateDay, b.EmplID) AS a
WHERE     (SumAllocPct < 1)
GROUP BY EmplID

Any suggestions / direction would be greatly appreciated.

Thanks!

Edit - here's a representative data sample:

   AllocDetailID    ProjectID   EmplID  AllocPct    AllocStartDate                 AllocEndDate
    6204              32         931     0.50        2011-01-01 00:00:00.0000000    2012-01-01 00:00:00.0000000
    6477              64         932     1.00        2011-05-27 00:00:00.0000000    2013-02-08 00:00:00.0000000
    6550              12         931     0.50        2011-06-01 00:00:00.0000000    2012-06-01 00:00:00.0000000

The intended return values for the query in question would be 1/1/2012 and 2/8/2013 for employees 931 and 932 respectively.

Thinking about this a bit more, the "date available" should really only apply from the current date forward - that is, from right now, what is the soonest that this employee has some availability? Hop开发者_JAVA技巧e this makes sense.

I'm pretty sure that this will work:

;WITH
    cteChange_Dates AS
(
   -- "change dates" are the start and end dates for any selected employee
    SELECT EmplID, AllocPct, 1.00 As Start, AllocStartDate   As ChangeDate
    FROM p_AllocDetailDynView
  UNION
    SELECT EmplID, AllocPct, 0.00 As Start, AllocEndDate     As changeDate
    FROM p_AllocDetailDynView
)
SELECT 
    EmplID,
    ChangeDate,
    SUM(AllocPct * Start) As TotalAllocPct
FROM
    cteChange_Dates
GROUP BY
    EmplID,
    ChangeDate
HAVING
    SUM(AllocPct * Start) < 1.00