CODEWITHSUNDEEP
sqlsql-server
user1336907
user1336907

Reputation: 21

SQL Overlapping Date Range

I have a table with 3 fields "Start Date" and "End Date" and "Allocation %". I will need to find out all the records in this table which falls within 3 months from the current date,

And (this one is tricky) only the records with overlapped date ranges where the Sum(Allocation %) > 100.

Please help me to come up with a query.

This is the table schema.

Table (ResourceAssignment)
   - ResourceAssignmentID (PK)
   - ResourceID (FK)
   - Assigned To (FK)
   - Start Date
   - End Date
   - Allocation %

I will basically need to find all over allocated resources within a certain period (from current day to 3 months).

Thanks in advance!

Upvotes: 0

Views: 1556

Answers (4)

user2592788
user2592788

Reputation: 1

I assume that Start Date and End date are formatted as Date time or Date.

I'm not sure if you are trying to determine if the start date records fall within the past 3 months or the end date records.

Also you'll need a limit to determine the Sum(Allocation %). What unique records are you wanting to sum. Since you want to Sum Allocation % and then select only the ones that are greater than 100, you have to figure out which ones to pull then to sum.

You'll want to accomplish this using a With statement or creating sub "tables" (queries) that pull the information you want in stages. I'll present the subquery statements.

Written for Sql-Server 2008 R2

First Select data that is within the Past 3 months

Select ResourceAssignmentID, ResourceID, [Assigned To], [Allocation %]
From ResourceAssignment
Where [Start Date] >= dateadd(Month,-3,Now)

Then using this data you want to Sum the Allocation %. (I am assuming based on the combination of the 2 foreign keys)

Select Query1.ResourceID, Query1.[Assigned To], sum(Query1.[Allocation %]) as Sumofallocation
From
(Select ResourceAssignmentID, ResourceID, [Assigned To], [Allocation %]
From ResourceAssignment
Where [Start Date] >= dateadd(Month,-3,Now)) Query1
Group By Query1.ResourceID, Query1.[Assigned To], sum(Query1.[Allocation %])

You may be able to add the Where statement before the group by to filter out for your >100 requirement, but I would just add another Select statement to pull it all together

Select * From
(Select Query1.ResourceID, Query1.[Assigned To], sum(Query1.[Allocation %]) as Sumofallocation
From
(Select ResourceAssignmentID, ResourceID, [Assigned To], [Allocation %]
From ResourceAssignment
Where [Start Date] >= dateadd(Month,-3,Now)) Query1
Group By Query1.ResourceID, Query1.[Assigned To], sum(Query1.[Allocation %]) 
) Query2
Where Query2.Sumofallocation > 100

Also, it seems like you are working on a project management type program. Would Microsoft Project not work? You would be able to see who's over allocated in whatever time-frame you wished. If you have multiple projects, you could just merge them into one large Project file and see it that way...

Anyway, I hopes this helps. The SQL might not be perfect, but it should point you in a workable direction.

Upvotes: 0

jazzytomato
jazzytomato

Reputation: 7239

I think this is what you try to achieve : 

SELECT SUM(Allocation), ResourceID
FROM ResourceAssignment
WHERE EndDate BETWEEN GETDATE() AND  DATEADD(m,3,GETDATE())
GROUP BY ResourceID
HAVING SUM(Allocation) > 100

Upvotes: 0

Anon
Anon

Reputation: 10918

You have two general solutions. Which is best is dependent on the size of ResourceAllocation, the frequency of conflicts, and how you intend to present the data.

Solution 1: Make a list of dates in the next 3 months and join against that to identify which dates are overallocated

WITH
  datelist AS (
    SELECT CAST(GETDATE() AS date) [d] UNION ALL SELECT DATEADD(day,1,[d]) FROM datelist WHERE [d] < DATEADD(month,3,GETDATE())
  ),
  allocations AS (
    SELECT *,SUM([Allocation %]) OVER(PARTITION BY [ResourceID],[d]) AS [Total Allocation %]
    FROM datelist
    INNER JOIN ResourceAssignment ON ([d] BETWEEN [Start Date] AND [End Date])
  )
SELECT *
FROM allocations
WHERE [Total Allocation %] >= 100

Solution 2: Use a range-conflict query with a self-join on ResourceAssignment

SELECT *
FROM (
  SELECT
    t1.*,SUM(t1.[Allocation %]) OVER(PARTITION BY t1.[ResourceID]) AS [Total Allocation %]
  FROM ResourceAssignment t1
  INNER JOIN ResourceAssignment t2 ON (
    t1.[ResourceID] = t2.[ResourceID] AND
    t1.[ResourceAssignmentID] <> t2.[ResourceAssignmentID] AND
    t1.[Start Date] < t2.[End Date] AND
    t2.[Start Date] < t1.[End Date]
  )
  WHERE
    t1.[Start Date] <= CAST(GETDATE()+30 as date) AND
    t2.[Start Date] <= CAST(GETDATE()+30 as date) AND
    t1.[End Date] > CAST(GETDATE() as date) AND
    t2.[End Date] > CAST(GETDATE() as date)
) t
WHERE [Total Allocation %] >= 100

Upvotes: 1

Hart CO
Hart CO

Reputation: 34784

Not exactly sure if this is what you're after, but this would show you all ResourceID with allocation above 100% where their begin or end date is within the last 3 months:

SELECT ResourceID 
FROM ResourceAssignment
WHERE StartDate BETWEEN DATEADD(month,-3,GETDATE())  AND GETDATE()
  OR EndDate BETWEEN DATEADD(month,-3,GETDATE())  AND GETDATE()
GROUP BY ResourceID 
HAVING SUM([Allocation %]) > 100

Upvotes: 1

Related Questions