join and exit in same month - 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 2008 Forums

Transact-SQL (2008)

join and exit in same month

Author

Topic

vipinjha123
Starting Member

45 Posts

Posted - 2012-05-30 : 06:20:45

Dear All ,
i am lookinf for a query where i can find the employee who join adter 20th of month and left before the 20th of next month.

my query is

SELECT distinct
A.EMP_STAFFID "Employee code",
A.EMP_FIRSTNAME + ' '+ isnull(A.EMP_MIDDLENAME,'') + ' '+ isnull(A.EMP_LASTNAME,'') "Employee name",
A.EMP_DATEOFJOINING "Date of Joining",
b.ES_LAST_WORKING_DATE dateofleaving
FROM ERM_EMPLOYEE_MASTER A INNER JOIN SEP_EMPLOYEE_SEPARATION B ON A.EMP_STAFFID=B.ES_EMP_STAFFID
inner join ERM_LOCATION_MASTER D ON D.LOCATION_ID=A.EMP_LOCATION_ID

where A.EMP_STATUS='P' and a.EMP_ISACTIVE='1'

regards,
Vipin jha

mani_12345
Starting Member

35 Posts

Posted - 2012-05-30 : 06:56:09

try this ..

create table ex2
(
id int
)
insert into ex2
select 3 union all
select 4 union all
select 5 union all
select 6
select * from ex2

create table try_123
(
emp_id int ,
emp_name varchar(20),
emp_doj datetime ,
emp_dof datetime
)

insert into try_123
select 1 ,'mani', CONVERT(varchar(10),'2012-05-21',111) , CONVERT(varchar(10),'2012-06-16',111)
union all
select 2 ,'ram',CONVERT(varchar(10),'2012-05-23',111) , CONVERT(varchar(10),'2012-06-16',111)
union all
select 3 ,'shyam', CONVERT(varchar(10),'2012-05-19',111),CONVERT(varchar(10),'2012-05-20',111)

select * from try_123
select emp_id ,emp_name from try_123
where emp_doj > '2012-05-20' and emp_dof <'2012-06-20'

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-05-30 : 06:57:04

WHERE DATEPART(DAY, DateOfJoining) > 20 AND DATEPART(DAY, DateofLeaving) < 20 AND DATEDIFF(MONTH, DateOfJoining, DateOfLeaving) = 1

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources