i need to get the staff having same join month and year-CodePudding

CREATE TABLE `staff` (
  `StaffID` int NOT NULL AUTO_INCREMENT,
  `Surname` varchar(40) DEFAULT NULL,
  `Given` varchar(40) DEFAULT NULL,
  `DOB` datetime DEFAULT NULL,
  `Sex` char(1) DEFAULT NULL,
  `Joined` datetime DEFAULT NULL,
  `Resigned` datetime DEFAULT NULL,
  `Address` varchar(50) DEFAULT NULL,
  `Suburb` varchar(30) DEFAULT NULL,
  `Postcode` varchar(6) DEFAULT NULL,
  `Phone` varchar(15) DEFAULT NULL,
  `SupervisorID` int DEFAULT NULL,
  `Commission` double DEFAULT NULL,
  `RatePerHour` double DEFAULT NULL,
  PRIMARY KEY (`StaffID`)
) ;

INSERT INTO `staff` VALUES 
(1,'VELLA','SARATH AJITH L','1968-09-10 00:00:00','M','2012-04-16 00:00:00','2018-01-10 00:00:00','76 SAUNDERS ST','STH MELBOURNE','3153','8579410',0,0,22.21),
(2,'MARZELLA','PATRICK MICHAEL','1981-12-03 00:00:00','M','2012-03-09 00:00:00','2018-03-29 00:00:00','126 THE PARADE','NTH CARLTON','3146','93374764',1,0,17.87),
(3,'HILTON','HARRY RODNEY E','1994-03-18 00:00:00','F','2011-10-18 00:00:00',NULL,'24/49 WALSH ST','BIRREGURRA VIC','3205','97231600',0,0,18.83),
(4,'JAMIESON','TERRENCE IAN','1967-11-02 00:00:00','F','2012-07-12 00:00:00','2017-09-17 00:00:00','6 LIBRA CRT','ENDEAVOUR HILLS','3764','94392347',1,0,10.23),
(5,'SANDERS','RICHARD ANTHONY','1986-03-03 00:00:00','M','2013-01-21 00:00:00','2018-05-06 00:00:00','513 TOORONGA RD','COBURG','3146','3001193',1,0,16.02),
(6,'SUMMERS','TED','1983-08-04 00:00:00','M','2013-05-29 00:00:00','2020-01-08 00:00:00','4/39 BALSTON ST','NORTHCOTE','3802','9998877',1,0,18.75),
(7,'KNOL','VINCENZO','1968-09-10 00:00:00','F','2012-07-21 00:00:00',NULL,'41 LENNOX ST','NORTHCOTE','3044','98850345',3,0,11.46),
(8,'PORTELLI','CATHERINE MARY','1981-08-18 00:00:00','M','2012-09-23 00:00:00','2018-06-13 00:00:00','41 LENNOX ST','EAST BURWOOD VIC','3095','94994432',1,0,17.7),
(9,'KHOR','GLENDA JEAN','1989-04-27 00:00:00','M','2012-09-15 00:00:00','2018-10-29 00:00:00','10 AUSTIN ST','ESSENDON','3040','38502732',1,0,12.33),
(10,'SCANLON','MICHAEL JOHN','1993-11-26 00:00:00','M','2012-03-18 00:00:00',NULL,'5 NARR MAEN DVE','ESSENDON','3802','93762678',3,0,16.89);

The output should be:

Month       Joined Date0020                 Name
July        2012-07-12 00:00:00         TERRENCE IANJAMIESON
July        2012-07-21 00:00:00         VINCENZOKNOL
March       2012-03-09 00:00:00         PATRICK MICHAELMARZELLA
March       2012-03-18 00:00:00         MICHAEL JOHNSCANLON
September   2012-09-23 00:00:00         CATHERINE MARYPORTELLI
September   2012-09-15 00:00:00         GLENDA JEANKHOR

I used the following code

select monthname(s1.joined) as month , s1.Joined, concat(s1.Given, ' ' ,s1.Surname) as Name  
from staff s1
join staff s2 on s1.joined = s2.Joined
where year(s1.joined)  = year(s2.joined) and month(s1.joined)= month(s2.joined) 
group by month(s1.joined)
having count(month(s1.joined))>1

AFter running my query I get single values, I want both the values having similar joined date

CodePudding user response：

Join the table with a subquery that gets all the months that have multiple employees

SELECT MONTHNAME(s1.joined) AS month, s1.joined, CONCAT_WS(' ', given, surname) AS name
FROM staff AS s1
JOIN (
    SELECT MONTH(joined) AS month
    FROM staff
    GROUP BY month
    HAVING COUNT(*) > 1
) AS m ON MONTH(s1.joined) = m.month
ORDER BY month

DEMO

CodePudding user response：

Here is my solution. I do the aggregation to find the year-month combos that occur more than once, then I join those back to the main table.

SELECT monthname(s.joined) as month , s.Joined, concat(s.Given, ' ' ,s.Surname) as Name
FROM staff s
JOIN (
  SELECT year(joined) year, month(joined) month, count(*) count
  FROM staff
  GROUP BY year(joined), month(joined)
  HAVING count > 1
) as ymc
  ON year(s.joined) = ymc.year AND month(s.joined) = ymc.month
ORDER BY year(s.joined), month(s.joined)