Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- Sql Server Query to get Department ID's of above question
- --------------------------------------------------------
- create table #Department(DeptId int,DepartmentName varchar(20))
- create table #Employee(EmpId int,DeptId int,EmpName varchar(20),JoiningDate date)
- insert into #Department values(1,'Admin')
- insert into #Department values(2,'HR')
- insert into #Department values(3,'Facility')
- insert into #Employee values(1,1,'Pallavi','02-17-2012')
- insert into #Employee values(2,1,'Dilip','11-25-2011')
- insert into #Employee values(3,2,'Minu','04-13-2010')
- insert into #Employee values(4,3,'Laxmi','12-11-2012')
- insert into #Employee values(5,1,'Supriya','08-22-2010')
- insert into #Employee values(6,2,'Archana','09-19-2015')
- insert into #Employee values(7,2,'Namita','05-01-2009')
- select * from #Department
- select * from #Employee
- select d.DeptId from #Department d JOIN #Employee e on d.DeptId=e.DeptId
- where DATEPART(yyyy,e.JoiningDate) < 2013
- group by e.DeptId
- having count(e.EmpId)>2
- drop table #Department
- drop table #Employee
- Sql Server Query to get Department Names of above question
- ----------------------------------------------------------
- create table #Department(DeptId int,DepartmentName varchar(20))
- create table #Employee(EmpId int,DeptId int,EmpName varchar(20),JoiningDate date)
- insert into #Department values(1,'Admin')
- insert into #Department values(2,'HR')
- insert into #Department values(3,'Facility')
- insert into #Employee values(1,1,'Pallavi','02-17-2012')
- insert into #Employee values(2,1,'Dilip','11-25-2011')
- insert into #Employee values(3,2,'Minu','04-13-2010')
- insert into #Employee values(4,3,'Laxmi','12-11-2012')
- insert into #Employee values(5,1,'Supriya','08-22-2010')
- insert into #Employee values(6,2,'Archana','09-19-2015')
- insert into #Employee values(7,2,'Namita','05-01-2009')
- select * from #Department
- select * from #Employee
- select d.DepartmentName from #Department d JOIN #Employee e on d.DeptId=e.DeptId
- where DATEPART(yyyy,e.JoiningDate) < 2013
- group by e.DeptId,d.DepartmentName
- having count(e.EmpId)>2
- drop table #Department
- drop table #Employee
- #dkt
Add Comment
Please, Sign In to add comment