Guest User

Untitled

a guest
May 25th, 2018
87
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 2.07 KB | None | 0 0
  1. Sql Server Query to get Department ID's of above question
  2. --------------------------------------------------------
  3. create table #Department(DeptId int,DepartmentName varchar(20))
  4. create table #Employee(EmpId int,DeptId int,EmpName varchar(20),JoiningDate date)
  5.  
  6. insert into #Department values(1,'Admin')
  7. insert into #Department values(2,'HR')
  8. insert into #Department values(3,'Facility')
  9.  
  10. insert into #Employee values(1,1,'Pallavi','02-17-2012')
  11. insert into #Employee values(2,1,'Dilip','11-25-2011')
  12. insert into #Employee values(3,2,'Minu','04-13-2010')
  13. insert into #Employee values(4,3,'Laxmi','12-11-2012')
  14. insert into #Employee values(5,1,'Supriya','08-22-2010')
  15. insert into #Employee values(6,2,'Archana','09-19-2015')
  16. insert into #Employee values(7,2,'Namita','05-01-2009')
  17.  
  18. select * from #Department
  19. select * from #Employee
  20.  
  21. select d.DeptId from #Department d JOIN #Employee e on d.DeptId=e.DeptId
  22. where DATEPART(yyyy,e.JoiningDate) < 2013
  23. group by e.DeptId
  24. having count(e.EmpId)>2
  25.  
  26.  
  27. drop table #Department
  28. drop table #Employee
  29.  
  30. Sql Server Query to get Department Names of above question
  31. ----------------------------------------------------------
  32. create table #Department(DeptId int,DepartmentName varchar(20))
  33. create table #Employee(EmpId int,DeptId int,EmpName varchar(20),JoiningDate date)
  34.  
  35. insert into #Department values(1,'Admin')
  36. insert into #Department values(2,'HR')
  37. insert into #Department values(3,'Facility')
  38.  
  39. insert into #Employee values(1,1,'Pallavi','02-17-2012')
  40. insert into #Employee values(2,1,'Dilip','11-25-2011')
  41. insert into #Employee values(3,2,'Minu','04-13-2010')
  42. insert into #Employee values(4,3,'Laxmi','12-11-2012')
  43. insert into #Employee values(5,1,'Supriya','08-22-2010')
  44. insert into #Employee values(6,2,'Archana','09-19-2015')
  45. insert into #Employee values(7,2,'Namita','05-01-2009')
  46.  
  47. select * from #Department
  48. select * from #Employee
  49.  
  50. select d.DepartmentName from #Department d JOIN #Employee e on d.DeptId=e.DeptId
  51. where DATEPART(yyyy,e.JoiningDate) < 2013
  52. group by e.DeptId,d.DepartmentName
  53. having count(e.EmpId)>2
  54.  
  55.  
  56. drop table #Department
  57. drop table #Employee
  58.  
  59. #dkt
Add Comment
Please, Sign In to add comment