Earliest allocation query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Earliest allocation query

Author

Topic

Lijo Cheeran Joseph
Posting Yak Master

123 Posts

Posted - 2010-10-30 : 06:09:25

Hi Team,

I have the following schema. I want to list out all employee names and their first allocation (based on StartDate). The @ProjectAllocation has only the latest allocation. The previous allocation details are moved into @ProjectAllocHistory when (and ony when) a new allocation is done.

Note: Some employees will have records only in @ProjectAllocation; not in @ProjectAllocHistory.

Following is the expected result. Please help with the best possible query.

Lijo, '1/1/2008', 'COMPLETE'
Kiran, '1/1/2010', 'ACTIVE'

DECLARE @Employee TABLE (EmpID INT, EmpName VARCHAR(50))
INSERT INTO @Employee (EmpID , EmpName) VALUES (1,'Lijo')
INSERT INTO @Employee (EmpID , EmpName) VALUES (2,'Kiran')

DECLARE @ProjectAllocation TABLE (AllocID INT, ProjectID INT, EmpID INT, StartDate DATETIME, [Status] VARCHAR(10))
INSERT INTO @ProjectAllocation(AllocID , ProjectID ,EmpID, StartDate , [Status]) VALUES (3,17,1,'1/1/2010','ACTIVE')
INSERT INTO @ProjectAllocation(AllocID , ProjectID ,EmpID, StartDate , [Status]) VALUES (4,17,2,'1/1/2010','ACTIVE')

DECLARE @ProjectAllocHistory TABLE (AllocID INT, ProjectID INT, EmpID INT,StartDate DATETIME, [Status] VARCHAR(10))
INSERT INTO @ProjectAllocHistory(AllocID , ProjectID ,EmpID, StartDate , [Status]) VALUES (2,15,1,'1/1/2009','COMPLETE')
INSERT INTO @ProjectAllocHistory(AllocID , ProjectID ,EmpID, StartDate , [Status]) VALUES (1,13,1,'1/1/2008', 'COMPLETE')

SELECT *
FROM @Employee E
INNER JOIN @ProjectAllocation P
ON P.EmpID = E.EmpID
LEFT OUTER JOIN @ProjectAllocHistory H
ON H.EmpID = P.EmpID

Thanks
Lijo

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-10-30 : 08:58:31

;with cte as
(
SELECT E.EmpId, E.EmpName, P.StartDate, P.Status
FROM @Employee E
INNER JOIN @ProjectAllocation P
ON P.EmpID = E.EmpID
union all
SELECT E.EmpId, E.EmpName, h.StartDate, h.Status
FROM @Employee E
INNER JOIN @ProjectAllocHistory h
ON h.EmpID = E.EmpID
)
select EmpName, StartDate, Status
from
(
select *, seq = row_number() over (partition by EmpId order by StartDate)
from cte
) a
where seq = 1

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Sachin.Nand

2937 Posts

Posted - 2010-10-30 : 12:15:43


select E.EmpID,EmpName,Status from @Employee E cross apply
(
	select top 1 EmpID,[status] from
	(
		select * from @ProjectAllocation
		union all
		select * from @ProjectAllocHistory
		
	)T where T.EmpID=E.EmpId  order by StartDate 
	
)T1

PBUH

Lijo Cheeran Joseph
Posting Yak Master

123 Posts

Posted - 2010-10-30 : 12:27:30

Thanks. This works.

However, is there any simplified version?

Sachin.Nand

2937 Posts

Posted - 2010-10-30 : 12:42:22

quote:
Originally posted by Lijo Cheeran Joseph

Thanks. This works.

However, is there any simplified version?

Can you please elaborate what do you mean by "simplified version"?

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources