How to handle NULL data values?

user_19310512 0
Viewed 32
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);

I used the following query:

SELECT StaffID, concat(Given,' ', Surname) as 'full name', Joined, 
    DATEDIFF(Resigned, Joined) AS 'total days' 
FROM staff;

But, for the null values in the resigned column, the total number of days is not calculated. (The null values in the resigned column means staff has not resigned yet.)

datetime mysql
1 Answers

Use IFNULL() to provide a default in place of the NULL values.

SELECT StaffID, concat(Given,' ', Surname) as 'full name', Joined, 
    DATEDIFF(IFNULL(Resigned, NOW()), Joined) AS 'total days' 
FROM staff;

To get the longest serving member, add:

ORDER BY `total days` DESC 
LIMIT 1

Notice that backticks are used there; in this place, single quotes are for string literals, not column names or aliases. See When to use single quotes, double quotes, and backticks in MySQL

user_14918950
Related