need help in select /self join

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)

need help in select /self join

Author

Topic

abul_mohsin
Starting Member

21 Posts

Posted - 2013-06-13 : 04:53:30

EMP_NO DATE_OF_BIRTH REC_DATE STATUS FULL_NAME
13771 1/1/1977 6/9/2013 Terminated Emp1
28042 12/10/1991 6/9/2013 NEW Emp2
28043 1/14/1988 6/9/2013 NEW Emp3
28044 11/14/1983 6/9/2013 NEW Emp4
28051 1/5/1988 6/9/2013 NEW Emp5
28052 12/21/1990 6/9/2013 NEW Emp6
28053 10/25/1979 6/9/2013 NEW Emp7
28054 11/27/1968 6/9/2013 NEW Emp8
2092143 11/27/1968 6/9/2013 Terminated Emp8
2092889 1/5/1988 6/9/2013 Terminated Emp5

Above is the result for this query.

select distinct A.EMP_NO,A.DATE_OF_BIRTH,A.REC_DATE,A.STATUS,A.FULL_NAME from Table1 as A
Inner join Table2 as B
on A.EMP_NO = B.EMP_NO
Where A.STATUS in ('Terminated','NEW')
And (CONVERT(char(10), A.REC_DATE,126) = CONVERT(char(10), B.REC_DATE,126) And A.DATE_OF_BIRTH = B.DATE_OF_BIRTH)
And (A.DATE_OF_BIRTH = B.DATE_OF_BIRTH or CONVERT(char(10), A.REC_DATE,126) = CONVERT(char(10), B.REC_DATE,126) or CONVERT(char(10), B.REC_DATE,126) = '2013-06-09')
and CONVERT(char(10), A.REC_DATE,126) = '2013-06-09'

i need only the records of Emp5 and emp8.
your help in this regard will be highly appreciated.

Thanks & Best Regard's
Abul Mohsin

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-13 : 04:56:31

add a filter for that


select distinct A.EMP_NO,A.DATE_OF_BIRTH,A.REC_DATE,A.STATUS,A.FULL_NAME from ALJ_EMP_ACT_DIR_H as A
Inner join ALJ_EMP_ACT_DIR_H as B 
on A.EMP_NO = B.EMP_NO
Where A.STATUS in ('Terminated','NEW') 
And (CONVERT(char(10), A.REC_DATE,126) = CONVERT(char(10), B.REC_DATE,126) And A.DATE_OF_BIRTH = B.DATE_OF_BIRTH) 
And (A.DATE_OF_BIRTH = B.DATE_OF_BIRTH or CONVERT(char(10), A.REC_DATE,126) = CONVERT(char(10), B.REC_DATE,126) or CONVERT(char(10), B.REC_DATE,126) = '2013-06-09')
and CONVERT(char(10), A.REC_DATE,126) = '2013-06-09'
and A.FULL_NAME IN ('Emp5','Emp8')

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

abul_mohsin
Starting Member

21 Posts

Posted - 2013-06-13 : 04:58:50

Dear there are many emp not only two emp5 and emp8

Thanks & Best Regard's
Abul Mohsin

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-13 : 05:09:05

DECLARE @param VARCHAR(100) = 'Emp5,Emp8' -- here declare your list of names with comma separation
SELECT
.
.
WHERE ....
AND (','+@param+',' LIKE '%,'+A.FULL_NAME+',%')

--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-13 : 05:11:07

quote:
Originally posted by abul_mohsin

Dear there are many emp not only two emp5 and emp8

Thanks & Best Regard's
Abul Mohsin

then pass what all values you want in the filter.
or if you've required employee codes alone stored in another table do a join with that on employee code

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

abul_mohsin
Starting Member

21 Posts

Posted - 2013-06-13 : 05:17:26

Dears to be more specific there more than 10000 records in this table so i cannot put condition on column FULL_NAME.

Thanks & Best Regard's
Abul Mohsin

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-13 : 05:19:42

quote:
Originally posted by abul_mohsin

Dears to be more specific there more than 10000 records in this table so i cannot put condition on column FULL_NAME.

Thanks & Best Regard's
Abul Mohsin

But you want only few of them right? the suggestion was to put only required subset of values in the where condition, not all the 10000 values.
I dont understand why its not possible for you to put a condition on FULL_NAME column.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-13 : 05:20:51

Follow Visakh's suggestion....
(you've required employee codes alone stored in another table do a join with that on employee code)

--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-13 : 05:28:20

it would be much better if you can explain us your requirement clearly in below format

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

abul_mohsin
Starting Member

21 Posts

Posted - 2013-06-13 : 05:35:12

Dear Visakh, thanks for your reply,
Here in this table we get employees record on daily basis EMP5 and EMP8 is appearing twice because these emp was temporary once he is confirmed the previous EMP_NO will be terminated and a new EMP_NO will be created for these employees.

There is nothing unique in these two records I am filtering it with DATE_OF_BIRTH and REC_DATE because the EMP5 will have same DATE_OF_BIRTH and same REC_DATE.

I need only the records which are having same Date_Of_birth and Rec_Date and Rec_Date = Getdate().

in the above example for the rec_date = 6/9/2013 i need

EMP_NO DATE_OF_BIRTH REC_DATE STATUS FULL_NAME
28051 1/5/1988 6/9/2013 NEW Emp5
28054 11/27/1968 6/9/2013 NEW Emp8
2092143 11/27/1968 6/9/2013 Terminated Emp8
2092889 1/5/1988 6/9/2013 Terminated Emp5

quote:
Originally posted by visakh16

quote:
Originally posted by abul_mohsin

Dear there are many emp not only two emp5 and emp8

Thanks & Best Regard's
Abul Mohsin

then pass what all values you want in the filter.
or if you've required employee codes alone stored in another table do a join with that on employee code

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Thanks & Best Regard's
Abul Mohsin

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-13 : 05:40:20

This is we suggest you to state your requirements clearly. it had no relationship with what you suggested early


select *
from
(
select COUNT(1) OVER (PARTITION BY A.DATE_OF_BIRTH,A.FULL_NAME) AS Cnt, A.EMP_NO,A.DATE_OF_BIRTH,A.REC_DATE,A.STATUS,A.FULL_NAME from ALJ_EMP_ACT_DIR_H as A
Inner join ALJ_EMP_ACT_DIR_H as B 
on A.EMP_NO = B.EMP_NO
Where A.STATUS in ('Terminated','NEW') 
And (CONVERT(char(10), A.REC_DATE,126) = CONVERT(char(10), B.REC_DATE,126) And A.DATE_OF_BIRTH = B.DATE_OF_BIRTH) 
And (A.DATE_OF_BIRTH = B.DATE_OF_BIRTH or CONVERT(char(10), A.REC_DATE,126) = CONVERT(char(10), B.REC_DATE,126) or CONVERT(char(10), B.REC_DATE,126) = '2013-06-09')
and CONVERT(char(10), A.REC_DATE,126) = '2013-06-09'
)t
WHERE Cnt > 1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

abul_mohsin
Starting Member

21 Posts

Posted - 2013-06-13 : 05:51:29

Thanks Visakh,
your below query worked, next time i will follow the guide lines before posting anything.

quote:
Originally posted by visakh16

This is we suggest you to state your requirements clearly. it had no relationship with what you suggested early

select *
from
(
select COUNT(1) OVER (PARTITION BY A.DATE_OF_BIRTH,A.FULL_NAME) AS Cnt, A.EMP_NO,A.DATE_OF_BIRTH,A.REC_DATE,A.STATUS,A.FULL_NAME from ALJ_EMP_ACT_DIR_H as A
Inner join ALJ_EMP_ACT_DIR_H as B
on A.EMP_NO = B.EMP_NO
Where A.STATUS in ('Terminated','NEW')
And (CONVERT(char(10), A.REC_DATE,126) = CONVERT(char(10), B.REC_DATE,126) And A.DATE_OF_BIRTH = B.DATE_OF_BIRTH)
And (A.DATE_OF_BIRTH = B.DATE_OF_BIRTH or CONVERT(char(10), A.REC_DATE,126) = CONVERT(char(10), B.REC_DATE,126) or CONVERT(char(10), B.REC_DATE,126) = '2013-06-09')
and CONVERT(char(10), A.REC_DATE,126) = '2013-06-09'
)t
WHERE Cnt > 1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Thanks & Best Regard's
Abul Mohsin

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-13 : 05:53:06

you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

VeeranjaneyuluAnnapureddy
Posting Yak Master

169 Posts

Posted - 2013-09-13 : 06:19:22

select distinct A.EMP_NO,A.DATE_OF_BIRTH,A.REC_DATE,A.STATUS,A.FULL_NAME from Table1 as A
Inner join Table1 as B
on A.EMP_NO = B.EMP_NO
Where A.STATUS in ('Terminated','NEW')
And (CONVERT(char(10), A.REC_DATE,126) = CONVERT(char(10), B.REC_DATE,126)
And A.DATE_OF_BIRTH = B.DATE_OF_BIRTH)
And (A.DATE_OF_BIRTH = B.DATE_OF_BIRTH
or CONVERT(char(10), A.REC_DATE,126) = CONVERT(char(10), B.REC_DATE,126)
or CONVERT(char(10), B.REC_DATE,126) = '2013-06-09')
--and CONVERT(char(10), A.REC_DATE,126) = '2013-06-09'
and A.FULL_NAME IN ('Emp5','Emp8')

veeranjaneyulu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources