September 16, 2008 at 10:14 am
Please consider following case:
I have 4 tables:
1. Table Companies:
CompID CompName
001 Company A
002 Company B
2. Table Resources:
ResID ResName
101 Resource AA
102 Resource BB
3. Table ResourcesEmploymentHistory:
ResID CompID StartDate EndDate
101 001 01/01/2007 30/06/2007
101 002 01/07/2007 NULL
4. Table TrackingHours
ResID Date TaskID Hours
101 29/06/2007 1001 8,0
101 30/06/2007 1001 8,0
101 01/07/2007 1001 6,0
101 02/07/2007 1001 8,0
What I am trying to get is T-SQL query (view or SP) which will effectively take into account employment history and give me following result:
CompID ResID TaskID Date Hours
001 101 1001 29/06/2007 8,0
001 101 1001 30/06/2007 8,0
002 101 1001 01/07/2007 6,0
002 101 1001 02/07/2007 8,0
Can you point me in right direction, or make some suggestions ?
Thank you.
September 16, 2008 at 10:41 am
hi there,
unless you need the ResName to be part of the output you won't need "2. Table Resources" in your query
Try something like this
select c.CompID, t.ResID, t.TaskID, t.Date, t.Hours
from dbo.Companies c
inner join dbo.ResourcesEmploymentHistory h on h.CompID = c.CompID
inner join dbo.TrackingHours t on t.ResID = h.ResID
I am assuming the schema is dbo... it might be something else... if this breaks just remove the dbo. altogether
September 16, 2008 at 12:22 pm
Thanks for the reply.
However, it won't be so easy. Problem is that history table (3) keeps only first and last date for particular resource belongs to some company. Of course, EndDate of last record for particular resource is NULL, meaning that resource is CURRENTLY STILL belonging to last company.
So, all tracking hours, where date is BETWEEN StartDate(01/01/2007) and EndDate(30/06/2007) for company 001 (first record in table 3), should give us company 001, and the rest (from 01/07/2007 on), should give us company 002.
If I apply your query proposal, I will get double tracking hours (each row in tracking hours will be shown twice - once for company 001, and once for company 002).
September 16, 2008 at 1:24 pm
would that work?
select c.CompID, t.ResID, t.TaskID, t.Date, t.Hours
from dbo.Companies c
inner join dbo.ResourcesEmploymentHistory h on h.CompID = c.CompID
inner join dbo.TrackingHours t on t.ResID = h.ResID and t.Date between h.StartDate and isnull(h.EndDate, '1/1/2100') -- some date in a distant future...
September 16, 2008 at 1:25 pm
if this last one doesn't work it would help if you could post a script to create these tables and populate them with few sample entries
September 16, 2008 at 2:49 pm
Now it works perfectly, many thanks Journeyman...
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply