How to get result in a proper hierarchy

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

General SQL Server Forums

New to SQL Server Programming

How to get result in a proper hierarchy

Author

Topic

arorarahul.0688
Posting Yak Master

125 Posts

Posted - 2009-05-06 : 03:46:16

Hi all i have two tables

EmpDetail
EMPID EMPNAME EMPTITLE FROMDATE TODATE
001 AAA BDM '2008-01-01' '2008-05-25'
001 AAA RBM '2008-05-26' '2008-12-31'
001 AAA ZBM '2009-01-01' '1900-01-01'
(Means Till Date)

002 BBB ZBM '1900-01-01' '2008-12-31'
002 BBB ZBM '2009-01-01' '2009-02-28'

EMPINCHARGEDETAILS

EmpID EMPNAME INCHARGEID INCHNAME InchargeFromDATE InchargeToDATE
003 CCC 001 AAA '2008-05-30' '2009-01-31'
003 CCC 002 BBB '2007-05-30' '2008-05-30'
004 CCC 002 BBB '1900-01-01' '2009-04-30'

now i wantr the final output in the for
EMP'SImmediateSubbordinate -> EMPIMMSUB

EmpID EMPNAME EMPTITLE EMPFROMDATE EMPToDATE EMPIMMSUBID

EMPIMMSUBNAME EMPIMMSUBTITLE EMPIMMSUBFROMDATE EMPIMMSUBTODATE

CAN Someone Please help to write a sql to get the corect output.

ihave written a query as below please have a look on that

Select
A.EMPLOYEEID AS EmployeeID,
A.EMPLOYEENAME AS EmployeeName,
A.TITLE AS Title,
B.EMPLOYEEID EmpDownwrds,
--C.EMPLOYEEID A1,
C.EmployeeNAME EmpDownWardsName,
C.TITLE EmpDownWardsTitle,
A.FromDATE A1,
A.TODATE A2,
B.FromDATE B1,
B.TODATE B2,
C.FromDATE C1,
C.TOdate C2
--Into #TEMP1
From
vwHRMVirtualNetworkDetails23Test A
Left Outer join vwInChargeDetails23TEST B
ON A.EmployeeID = B.InchargeID
AND (
((B.FROMDATE <= A.FROMDATE) AND (B.TODATE = '1900-01-01' OR(B.TODATE >= A.TODATE AND A.TODATE <> '1900-01-01')))
OR ((B.FROMDATE <= A.FROMDATE) AND (B.TODATE >= A.FROMDATE AND (B.TODATE <= A.TODATE OR A.TODATE = '1900-01-01')))
OR ( B.FROMDATE >= A.FROMDATE AND (B.FROMDATE <= A.TODATE OR A.TODATE = '1900-01-01'))
)
Left OUTER JOIN vwHRMVirtualNetworkDetails23TEST C
ON B.EMPLOYEEID = C.EMPLOYEEID
AND (
( C.FROMDATE <= A.FROMDATE AND
(C.TODATE = '1900-01-01' OR (C.TODATE >= A.FROMDATE AND (C.TODATE <= A.TODATE OR (A.TODATE = '1900-01-01')))) OR
(C.TODATE >= A.TODATE OR A.TODATE = '1900-01-01')
)
OR
( C.FROMDATE >= A.FROMDATE AND (C.FROMDATE < = A.TODATE OR A.TODATE = '1900-01-01'))
OR(C.FROMDATE >= B.FROMDATE AND B.FROMDATE ='1900-01-01' AND B.TODATE = '1900-01-01')
)

please help me to resolve this query thanx :)

Rahul Arora
NCCE, Panipat

######################
IMPOSSIBLE = I+M+POSSIBLE

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-11 : 01:51:00

If you are using SQL Server 2005, try rewrite your query using recursive CTE.

E 12°55'05.63"
N 56°04'39.26"