· 9 years ago · Oct 03, 2016, 08:44 AM
1select * FROM group_assigned_users
2 where gau_regdate < '2016/10/01' and (gau_cancelleddate < '2016/10/01' or gau_cancelleddate = '0000-00-00')
3 and gau_group_id = 26
4
5
6CREATE TABLE IF NOT EXISTS `group_assigned_users` (
7 `gau_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Primary Key',
8 `gau_status` enum('Pending','Active','Suspended','Terminated','Cancelled','Froze') NOT NULL DEFAULT 'Pending',
9 `gau_regdate` date NOT NULL,
10 `gau_expirydate` date NOT NULL,
11 `gau_nextdue` date NOT NULL,
12 `gau_cancelleddate` date NOT NULL,
13 PRIMARY KEY (`gau_id`)
14) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=7746 ;
15
16
17INSERT INTO `group_assigned_users` (`gau_id`, `gau_status`, `gau_regdate`, `gau_expirydate`, `gau_nextdue`, `gau_cancelleddate`) VALUES
18(2007, 'Active', '2007-01-10', '2017-06-26', '2017-06-26', '0000-00-00'),
19(2020, 'Cancelled', '2007-12-19', '2015-12-19', '2016-12-19', '2015-12-17'),
20(2044, 'Cancelled', '2007-09-26', '2014-05-27', '2014-05-27', '2014-05-27'),
21(4824, 'Cancelled', '2010-08-26', '2011-08-26', '2011-08-26', '2011-08-26'),
22(6520, 'Cancelled', '2013-06-19', '2015-07-16', '2015-07-16', '2015-07-16'),
23(6680, 'Cancelled', '2013-09-26', '2016-06-26', '2016-06-26', '2016-01-19'),
24(7058, 'Cancelled', '2014-11-07', '2015-12-10', '2015-12-10', '2015-09-25'),
25(7066, 'Cancelled', '2014-11-14', '2015-11-20', '2016-11-20', '2015-11-19'),
26(7174, 'Cancelled', '2015-04-22', '2016-05-20', '2016-05-20', '2016-02-16'),
27(7191, 'Cancelled', '2015-05-17', '2015-12-04', '2015-12-04', '2015-10-07'),
28(7372, 'Cancelled', '2015-10-11', '2016-10-11', '2016-10-11', '2016-02-19'),
29(7389, 'Active', '2015-11-01', '2016-11-01', '2016-10-18', '0000-00-00'),
30(7404, 'Cancelled', '2015-03-06', '2016-03-06', '2017-03-06', '2016-03-09'),
31(7419, 'Active', '2015-11-04', '2016-11-04', '2016-11-04', '0000-00-00'),
32(7422, 'Active', '2019-11-30', '2020-11-30', '2020-11-03', '0000-00-00'),
33(7431, 'Cancelled', '2015-12-03', '2016-12-03', '2016-11-23', '2016-07-07'),
34(7465, 'Cancelled', '2016-01-03', '2017-01-03', '2017-01-03', '2016-02-22'),
35(7491, 'Cancelled', '2016-01-29', '2017-01-29', '2017-01-29', '2016-06-13'),
36(7549, 'Active', '2016-03-09', '2017-03-09', '2017-03-01', '0000-00-00'),
37(7595, 'Active', '2016-04-05', '2017-04-05', '2017-04-05', '0000-00-00'),
38(7616, 'Active', '2016-05-04', '2017-05-04', '2017-04-27', '0000-00-00'),
39(7627, 'Active', '2016-05-15', '2017-05-15', '2017-05-15', '0000-00-00'),
40(7650, 'Active', '2016-06-01', '2017-06-01', '2017-06-08', '0000-00-00'),
41(7684, 'Active', '2016-07-25', '2017-07-24', '2017-07-24', '0000-00-00'),
42(7689, 'Active', '2016-08-02', '2017-08-02', '2016-11-01', '0000-00-00'),
43(7691, 'Suspended', '2016-08-03', '2017-08-08', '2016-11-08', '0000-00-00'),
44(7698, 'Active', '2016-08-10', '2017-02-11', '2017-02-11', '0000-00-00'),
45(7715, 'Active', '2016-08-25', '2017-09-08', '2017-09-08', '0000-00-00'),
46(7716, 'Active', '2016-08-25', '2017-02-08', '2017-02-08', '0000-00-00');