SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 need help in select /self join
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

abul_mohsin
Starting Member

Saudi Arabia
20 Posts

Posted - 06/13/2013 :  04:53:30  Show Profile  Reply with Quote
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

Edited by - abul_mohsin on 06/13/2013 05:00:11

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/13/2013 :  04:56:31  Show Profile  Reply with Quote
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
Go to Top of Page

abul_mohsin
Starting Member

Saudi Arabia
20 Posts

Posted - 06/13/2013 :  04:58:50  Show Profile  Reply with Quote
Dear there are many emp not only two emp5 and emp8

Thanks & Best Regard's
Abul Mohsin
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2215 Posts

Posted - 06/13/2013 :  05:09:05  Show Profile  Reply with Quote
DECLARE @param VARCHAR(100) = 'Emp5,Emp8' -- here declare your list of names with comma separation
SELECT
.
.
WHERE ....
AND (','+@param+',' LIKE '%,'+A.FULL_NAME+',%')

--
Chandu

Edited by - bandi on 06/13/2013 05:09:54
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/13/2013 :  05:11:07  Show Profile  Reply with Quote
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
Go to Top of Page

abul_mohsin
Starting Member

Saudi Arabia
20 Posts

Posted - 06/13/2013 :  05:17:26  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/13/2013 :  05:19:42  Show Profile  Reply with Quote
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
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2215 Posts

Posted - 06/13/2013 :  05:20:51  Show Profile  Reply with Quote
Follow Visakh's suggestion....
(you've required employee codes alone stored in another table do a join with that on employee code)

--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/13/2013 :  05:28:20  Show Profile  Reply with Quote
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
Go to Top of Page

abul_mohsin
Starting Member

Saudi Arabia
20 Posts

Posted - 06/13/2013 :  05:35:12  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/13/2013 :  05:40:20  Show Profile  Reply with Quote
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

Edited by - visakh16 on 06/13/2013 05:41:20
Go to Top of Page

abul_mohsin
Starting Member

Saudi Arabia
20 Posts

Posted - 06/13/2013 :  05:51:29  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/13/2013 :  05:53:06  Show Profile  Reply with Quote
you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

VeeranjaneyuluAnnapureddy
Posting Yak Master

India
169 Posts

Posted - 09/13/2013 :  06:19:22  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000