help me to get this out put ?

I everybody

I have table Call EmpTran like this

EmpId TranId Opt S_Date Ar_Date
1 2 1 01/01/2007 NULL
1 2 15 03/02/2007 NULL
1 2 12 04/03/2007 01/04/2006
2 3 55 01/01/2006 NULL
3 4 53 01/01/2006 NULL
3 4 58 01/01/2006 01/04/2007

SQL for Create Table and values--
create table EmpTran
(
EmpId int,
TranId int ,
Opt int,
S_Date DATETIME,
Ar_Date DATETIME
)

insert into EmpTran values(1,2,1,'01/01/2007',NULL)
insert into EmpTran values(1,2,15,'03/02/2007',NULL)
insert into EmpTran values(1,2,12,'04/03/2007','01/04/2006')
insert into EmpTran values(2,3,55,'01/01/2006',NULL)
insert into EmpTran values(3,4,53,'01/01/2006',NULL)
insert into EmpTran values(3,4,58,'01/01/2006','01/04/2007')

No i am bit stuck when applying these condition in to this table and pupulate the out put
so my conditions is like this
I need to find out a EmpId,TranId,Opt,S_Date,Ar_Date where Ar_Date Is Null
and then if a one employee is get reapeated morethan once and if that EmpId got Ar_Date then i need to get earliest Ar_Date in that emp_Id and check that Ar_Date is less than the S_date of that Perticular Row then display the values

bellow i explained by each row data condition,

EmpId TranId Opt S_Date Ar_Date
1 2 1 01/01/2007 NULL 'Should display,cos Ar_date is null + Max Ar_date is <S_Date
1 2 15 03/02/2007 NULL 'Should display,cos Ar_date is null + Max Ar_date is <S_Date
1 2 12 04/03/2007 01/04/2006 'should not include cos this got Ar_date
2 3 55 01/01/2006 NULL 'Should not include this cos EmpId not repeated
3 4 53 01/01/2006 NULL 'should not get this row,cos Ar_date is > S_Date
3 4 58 01/01/2006 01/04/2007 'should not include cos this got Ar_date

so after all these requirement satisfy my table should looks like this
EmpId TranId Opt S_Date Ar_Date
1 2 1 01/01/2007 NULL
1 2 15 03/02/2007 NULL

any idea ?

regards
suis

If you use sql server 2005,

Code Snippet

Create Table #EmpTran (

EmpId int,

TranId int ,

Opt int,

S_Date DATETIME,

Ar_Date DATETIME

)

insert into #EmpTran values(1,2,1,'01/01/2007',NULL)

insert into #EmpTran values(1,2,15,'03/02/2007',NULL)

insert into #EmpTran values(1,2,12,'04/03/2007','01/04/2006')

insert into #EmpTran values(2,3,55,'01/01/2006',NULL)

insert into #EmpTran values(3,4,53,'01/01/2006',NULL)

insert into #EmpTran values(3,4,58,'01/01/2006','01/04/2007')

;With CTE

as

(

Select

*

, Max(Ar_Date) Over (Partition By EmpId) Max_Ar_Date

, Count(EmpId) Over(Partition By EmpId) Count_EmpId

From

#EmpTran

)

Select

EmpId

, TranId

, Opt

, S_Date

, Ar_Date

from

CTE

Where

Count_EmpId > 1 And Ar_Date is NULL And S_Date > Max_Ar_Date

If you are using Sql Server 2000,

Code Snippet

Select

EmpId

, TranId

, Opt

, S_Date

, Ar_Date

From

#EmpTran Main

Join (Select

EmpId MaxEmpId,

Max(Ar_Date) Max_Ar_Date,

Count(EmpId) Count_EmpId

From #EmpTran

Group By EmpId) as MaxData On MaxData.MaxEmpId = Main.EmpID

Where

Count_EmpId > 1

And Ar_Date is NULL

And S_Date > Max_Ar_Date

|||Hi i have a small question out of that SQL ?
my question is what is the reason u start to write u r sql using

;With CTE

as

?

Whats that mean ?

I am using Sql server 2000 ? not 2005 ?

can i write the same sql in sql2000 ?

regards

Suis

|||

This is one of the new feature in SQL Server 2005 called Common Table Expression.

You can use derived table also here. But I like to use CTE; it has more advantages than Derived tables.

|||Hi
Problem is i am using sql server 2000 ?
how can i write the same sql in SQL server 2000 ?
regards
suis|||check my first post i edited it; you can get the query for sql server 2000 also.|||hi
sorry i didnt read the bottom part of u r comments,
i will try the sql server 2000 part and let u know the result
thanks
regards
suis|||Hi kent,
According to my data SQL looks alright ? i got the correct result !
thanks
regards
suis