July 17, 2017 at 11:02 pm
Can anyone help me with this?I need to find the allocation history of projects for employees.For this we use a table Resources with multiple entries for the same employee with the latest row marked as IsCurrent(value 1).
| EmployeeId | Project | AllocatedOn | RelievedOn | RoleId |
| 12 | P0 | 01-01-17 | 12-05-17 | 3 |
| 12 | P1 | 12-05-17 | 12-06-17 | 3 |
| 12 | P2 | 01-07-17 | NULL | 3 |
This is the sample data(Resources table). It means that the employee was in Project P0 from 01-01-17 to 12-05-17 and he got allocated to P1 on 12-05-17,from which he was relieved on 12-06-17.The next allocation shown is for 01-07-17.So from the period 12-06-17 to 01-07-17,he is in ‘bench’ or ‘pool’(this row needs to be manipulated).
What I did was as shown below(I know that something is wrong with this query,although I am getting the result for the time being):
DECLARE @JoinDate date,@LastDeallocation date,@LastAllocatedOn date
SET @joinDate=(SELECT joiningdate FROM [dbo].Resources WHERE EmpID=@EmpId and IsCurrent=1
)
SET @LastDeallocation=(select max(DeallocationDate) from Allocation WHERE EmployeeID=@EmpId and IsCurrent=1
)
SET @LastAllocatedOn=(select max(AllocationDate) from Allocation WHERE EmployeeID=@EmpId and IsCurrent=1)
SELECT * FROM
(
SELECT r.RoleID,P.ProjectName,r.AssignmentDate AssignedOn,RelievingDate DeallocatedOn
FROM Resources r
INNER JOIN Employees E ON E.ID=r.EmpID
INNER JOIN Project P ON P.ProjectID=r.ProjectID
where r.EmpID=@EmpId --and AssignmentDateIntoProject>=@joinDate
UNION ALL
SELECT r.RoleID,'Bench' AS ProjectName,R.RelievingDate AssignedOn,
R1.AssignmentDate DeallocatedOn
FROM Resources R
INNER JOIN Resources R1 ON R1.ResourceID=R.ResourceID+1
WHERE R.EmpId=@EmpId AND (R.RelievingDate<R1.AssignmentDate)
UNION ALL --this is for entries just deallocated(the current bench)
SELECT r.RoleID,'Bench' AS ProjectName,@LastDeallocation AS AssignedOn,
NULL AS DeallocatedOn
FROM Resources R WHERE EmpId=@EmpId
AND R.RelievingDate =@LastDeallocation AND @LastDeallocation>@LastAllocatedOn
)AS X
INNER JOIN Role RL ON X.RoleID=RL.RoleID
order by X.AssignedOn
+��;dK�
---
Thinking is the hardest work there is, which is the probable reason so few engage in it.
Sunitha
😎
July 18, 2017 at 1:27 am
Can you please post the DDL (create table) scripts, sample data as an insert statement and the expected output?
😎
July 18, 2017 at 6:50 am
Sunitha, you have enough visits and points on SSC.com to know how to post a question on the forum. We need create table scripts, inserts and expected output. There probably should be some comments with the data to show logic/requirements too.
I do note that you mention IsCurrent, but you don't have that in your queries that I saw. I find that curious given my quick read.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply