CODEWITHSUNDEEP
mysql
John Magnolia
John Magnolia

Reputation: 16793

mysql calculate total amount of days absent

How would I calculate the total amount of days a user has been absent within a given date period?

This is the basic idea of how I think it would work but I cant get the total part to work.

SELECT total(from - until) FROM absences WHERE absences.user_id = 123
AND absence_date_until <= '1999-12-01' AND absence_date_from >=
'2000-12-01'


CREATE TABLE IF NOT EXISTS `absences` (
  `absence_id` int(10) NOT NULL AUTO_INCREMENT,
  `absence_created` datetime DEFAULT NULL,
  `absence_date_from` datetime DEFAULT NULL,
  `absence_date_until` datetime DEFAULT NULL,
  `absence_status` enum('PENDING','APPROVED') NOT NULL,
  `user_id` int(6) NOT NULL,
  PRIMARY KEY (`absence_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

Upvotes: 0

Views: 561

Answers (2)

Tom
Tom

Reputation: 6663

SELECT SUM(datediff(until, from) + 1) total_absences 
FROM   absences 
WHERE  absences.user_id = 123
       AND absence_date_until <= '1999-12-01' 
       AND absence_date_from  >= '2000-12-01'

Upvotes: 1

Joe G Joseph
Joe G Joseph

Reputation: 24046

select datediff() im mysql

SELECT datediff(absence_date_from- absence_date_until) 
FROM absences WHERE absences.user_id = 123
AND absence_date_until <= '1999-12-01' 
AND absence_date_from >=  '2000-12-01'

Upvotes: 0

Related Questions