Interesting 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)

Interesting Query

Author

Topic

Lijo Cheeran Joseph
Posting Yak Master

123 Posts

Posted - 2010-09-29 : 06:06:54

Hi Team,

Following tables are associated with project allocation of employees.
The aim is to show the first peoject allocation of employees after they completed 23 years of age.

Note: Subquery is not preferred.

-- Expected Result is
EmpID = 1, PersonName = Lijo, ProjectStartDate = 2009-01-01, ProjectName = 'ZZ Audit'

DECLARE @Employee TABLE (EmpID INT,PersonID INT, JoiningDate SMALLDATETIME)
INSERT INTO @Employee (EmpID ,PersonID , JoiningDate ) VALUES (1,101,'1/1/2004')

DECLARE @Person TABLE (PersonID INT, PersonName VARCHAR(100), DOB SMALLDATETIME)
INSERT INTO @Person (PersonID , PersonName , DOB )VALUES (101,'Lijo','1/1/1985')

DECLARE @ProjectAllocation TABLE (AllocationID INT,EmpID INT, ProjectName VARCHAR(100),StartDate SMALLDATETIME, EndDate SMALLDATETIME)
INSERT INTO @ProjectAllocation (AllocationID,EmpID, ProjectName,StartDate, EndDate ) VALUES (1,1,'Synergy','1/1/2005',NULL)
INSERT INTO @ProjectAllocation (AllocationID,EmpID, ProjectName,StartDate, EndDate ) VALUES (2,1,'ZZ Audit','1/1/2009','12/31/2009')
INSERT INTO @ProjectAllocation (AllocationID,EmpID, ProjectName,StartDate, EndDate ) VALUES (3,1,'MFranchise','1/1/2010',NULL)

--Partial query without showing ProjectName (Challenge - to show ProjectName also and achieve the expected result)
SELECT E.EmpID,P.PersonName,MIN(PAL.StartDate) AS ProjectStartDate
FROM @Employee E
INNER JOIN @Person P
ON P.PersonID = E.PersonID
LEFT OUTER JOIN @ProjectAllocation PAL
ON PAL.EmpID = E.EmpID
AND PAL.StartDate >= DATEADD(Year,23,P.DOB)
GROUP BY E.EmpID,P.PersonName

Could you please help to achieve the following expected result with the best query?

-- Expected Result is
EmpID = 1, PersonName = Lijo, ProjectStartDate = 2009-01-01, ProjectName = 'ZZ Audit'

Note: Subquery is not preferred.

Thanks
Lijo

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-29 : 06:25:35

here is the query:



SELECT TOP 1 E.EmpID,P.PersonName,PAL.StartDate AS ProjectStartDate,PAL.ProjectName
FROM #Employee E
INNER JOIN #Person P
ON P.PersonID = E.PersonID
LEFT OUTER JOIN #ProjectAllocation PAL
ON PAL.EmpID = E.EmpID
AND PAL.StartDate >= DATEADD(Year,23,P.DOB)
ORDER BY PAL.StartDate

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-29 : 06:28:16

I think he wnat to get the first project out of projects alloted after acheiving 23 yrs. of age.

quote:

Originally posted by Sachin.Nand


Why should only 'ZZ Audit'  come in o/p when  when even 'MFranchise' satisfies the date &empid condition?

PBUH

Sachin.Nand

2937 Posts

Posted - 2010-09-29 : 06:31:24

quote:
Originally posted by rohitvishwakarma

I think he wnat to get the first project out of projects alloted after acheiving 23 yrs. of age.

quote:
Originally posted by Sachin.Nand

Why should only 'ZZ Audit' come in o/p when when even 'MFranchise' satisfies the date &empid condition?

PBUH

Sorry I didnt read his requirement properly.In between I also had deleted my question which you quoted.

PBUH

Lijo Cheeran Joseph
Posting Yak Master

123 Posts

Posted - 2010-09-29 : 06:36:11

quote:
Originally posted by rohitvishwakarma

here is the query:

Thaks for resposne; but it will not work.

TOP 1 will give only for one employee. I am looking for each employee.

Note: Subquery is NOT preferred.

Sachin.Nand

2937 Posts

Posted - 2010-09-29 : 06:45:06

[code]
SELECT
E.EmpID,
P.PersonName,
T.ProjectStartDate,
T.ProjectName
FROM @Employee E
INNER JOIN @Person P
ON P.PersonID = E.PersonID
CROSS APPLY
(
SELECT TOP 1 StartDate AS ProjectStartDate,PAL.ProjectName FROM
@ProjectAllocation PAL
WHERE PAL.EmpID = E.EmpID AND PAL.StartDate >= DATEADD(Year,23,P.DOB)
)T
[/code]

PBUH

Lijo Cheeran Joseph
Posting Yak Master

123 Posts

Posted - 2010-09-29 : 07:58:07

It works. Thanks
Is there any other way without using CROSS APPLY/OUTER APPLY ?

Sachin.Nand

2937 Posts

Posted - 2010-09-29 : 08:02:36