StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
primarykey
Id
17709050
data
AcceptedAnswerId
0
AnswerCount
0
ClosedDate
CommentCount
0
CommunityOwnedDate
CreationDate
2013-07-17T19:55:23.393
FavoriteCount
0
LastActivityDate
2015-07-30T01:45:02.670
LastEditDate
2015-07-30T01:45:02.670
LastEditorUserId
2383846
OwnerUserId
2592788
ParentId
17708518
PostTypeId
2
Score
0
ViewCount
0
LastEditorDisplayName
text
Body
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 <pre><code>Select ResourceAssignmentID, ResourceID, [Assigned To], [Allocation %] From ResourceAssignment Where [Start Date] >= dateadd(Month,-3,Now) </code></pre> Then using this data you want to Sum the Allocation %. (I am assuming based on the combination of the 2 foreign keys) <pre><code>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 %]) </code></pre> 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 <pre><code>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 </code></pre> 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.
Tags
Title
singulars
PostAcceptedAnswerId
1. This table or related slice is empty.
PostParentId
1. POSQL Overlapping Date Range
 singulars
 PostTypePostTypeId
 PTQuestion
PostTypePostTypeId
1. PTAnswer
UserLastEditorUserId
1. USToXic73
UserOwnerUserId
1. USuser2592788
plurals
PostLinksPostIdRelatedPostId
1. This table or related slice is empty.
PostLinksRelatedPostIdPostId
1. This table or related slice is empty.
PostsAcceptedAnswerId
1. This table or related slice is empty.
PostsParentIdCreationDate
1. This table or related slice is empty.
VotesPostIdCreationDate
1. This table or related slice is empty.
CommentsPostId
1. This table or related slice is empty.

Querying!

Guidance

A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.