Getting result between dates

[ztimer]

Hi.

 

Need some guidance on a query. I'm not sure how this could be done.

First of all the table contains worker contracts. All the contracts that worker has previously and the current contract.

The idea is that when i query data from database i want to get the contract data that is between the date_start and end_date

 

Second problem is that the last contract does not have end_date at all cause its ongoing contract.

 

CREATE TABLE IF NOT EXISTS `contracts` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`user_id` varchar(10) DEFAULT NULL,
`job_type_id` varchar(10) DEFAULT NULL,
`bruto` varchar(10) DEFAULT NULL,
`b_type` varchar(10) DEFAULT NULL,
`bonus` varchar(10) DEFAULT NULL,
`quantity` varchar(10) DEFAULT NULL,
`q_type` varchar(10) DEFAULT NULL,
`start_date` timestamp NULL DEFAULT NULL,
`end_date` date DEFAULT NULL,
`status` varchar(1) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;


--
-- Dumping data for table `contracts`
--


INSERT INTO `contracts` (`id`, `user_id`, `job_type_id`, `bruto`, `b_type`, `bonus`, `quantity`, `q_type`, `start_date`, `end_date`, `status`) VALUES
(1, '76', '1', '300', 'M', '10', '25', 'percent', '2011-05-27 00:00:00', '2012-09-01', '0'),
(2, '76', '1', '350', 'M', '20', '100', 'hours', '2012-09-01 00:00:00', '2012-12-31', '0'),
(3, '76', '1', '350', 'M', '0', '75', 'percent', '2013-01-01 00:00:00', NULL, '1'),
(4, '76', '2', '5', 'H', '0', '25', 'percent', '2013-01-01 00:00:00', NULL, '1');

 

How to query select * from contracts where user_id = 76 and i have like year and month do not need the day as contracts start from beginning of month never does a contract start from the middle of the month while another ends in the middle of the month.

 

Please help if someone knows what to do. It has been a deal breaker for a week now.

[kicken]

Something like this I am guessing is what you're looking for:

SELECT
  *
FROM contracts
WHERE
  start_date >= '2012-1-1' 
  AND COALESCE(end_date, NOW()) <= NOW()

 

That will find any contracts who's start_date occurs after your target start date ('2012-1-1' in the example) and end date occurs before the target end date (the current time, in the example).  For any contract with a NULL end date, it assumes the end_date is NOW().

 

[Barand]

do you mean

 

SELECT * FROM contracts
WHERE user_id = 76
AND
CONCAT_WS('-','$yr','$mth','01') BETWEEN start_date AND IFNULL(end_date,'9999-12-31');

[ztimer]

Something like this I am guessing is what you're looking for:

SELECT
 *
FROM contracts
WHERE
 start_date >= '2012-1-1' 
 AND COALESCE(end_date, NOW()) <= NOW()

 

That will find any contracts who's start_date occurs after your target start date ('2012-1-1' in the example) and end date occurs before the target end date (the current time, in the example). For any contract with a NULL end date, it assumes the end_date is NOW().

do you mean

 

SELECT * FROM contracts
WHERE user_id = 76
AND
CONCAT_WS('-','$yr','$mth','01') BETWEEN start_date AND IFNULL(end_date,'9999-12-31');

 

Both of the ways are great. Thank you all for helping out.