How to wrrite select query for this scenario [Resolved]

Posted by Jayakumars under Sql Server on 7/20/2015 | Points: 10 | Views : 458 | Status : [Member] [MVP] | Replies : 4
Create Table Emp1
(
EmpId int primary key identity(1,1),
EmpName varchar(40)
)


Create Table Emp2
(
Id int primary key identity(1,1),
EmpId int,
Concernname varchar(40),
JoinDt date,
Levg Date,
CurnWrkng varchar(40)
)


-- I need output Query those exployee which have to more then 2 years
-- How to write query following situation.

--for ex:
EmpID Empname Join Date LEaving Date TotalExp Concernname currentconcern
1 AA 20/07/2014 20/07/2015 1 ABC Tech YestTec
2 BB 20/07/2010 20/07/2015 5 Infosys Cad infos - See more at: http://www.dotnetfunda.com/forums/show/20077/how-to-fetch-matching-records-following-query#sthash.rWsxh7mN.dpuf

Mark as Answer if its helpful to you


Responses

Posted by: Bandi on: 7/21/2015 [Member] [MVP] Platinum | Points: 50

Up
0
Down

Resolved
SELECT e1.EmpName, e2.*, cast(DATEDIFF(MM, e2.JoinDt, e2.Levg)/12 as varchaR(10)) + ' Years' Years, cast(DATEDIFF(MM, e2.JoinDt, e2.Levg)%12 as varchar(10))+ ' Months' Months
FROM Emp1 e1
join Emp2 e2 on e1.EmpId = e2.EmpId

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 7/21/2015 [Member] [MVP] Platinum | Points: 25

Up
0
Down
SELECT e1.EmpName, e2.*, DATEDIFF(YY, e2.JoinDt, e2.Levg) TotalExp
FROM Emp1 e1
join Emp2 e2 on e1.EmpId = e2.EmpId
WHERE DATEDIFF(YY, e2.JoinDt, e2.Levg) >=2


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Jayakumars on: 7/21/2015 [Member] [MVP] Bronze | Points: 25

Up
0
Down
Hi
bandi

Thanks for your reply but i need also how to show some employee 2 month one conern
some employee 6 month some 1 or year so here how do show month and year

for ex:
Exp
6 month
1 year
2 years

Mark as Answer if its helpful to you

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Dianaazura on: 7/25/2015 [Member] Starter | Points: 25

Up
0
Down
Thanks for your reply but i need also how to show some employee 2 month one conern
some employee 6 month some 1 or year so here how do show month and year - See more at: http://www.dotnetfunda.com/forums/show/20082/how-to-wrrite-select-query-for-this-scenario#sthash.Ps4roIxo.dpuf

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response