1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119
| --------------
SET AUTOCOMMIT = 0
--------------
--------------
START TRANSACTION
--------------
--------------
DROP DATABASE IF EXISTS `base`
--------------
--------------
CREATE DATABASE `base`
DEFAULT CHARACTER SET `latin1`
DEFAULT COLLATE `latin1_general_ci`
--------------
--------------
DROP TABLE IF EXISTS `stay_nfo`
--------------
--------------
CREATE TABLE IF NOT EXISTS `stay_nfo`
(
`post_id` integer unsigned NOT NULL AUTO_INCREMENT,
`ch_start_stay_date` date NOT NULL,
`ch_end_stay_date` date NOT NULL,
`idstays_nfo` integer NOT NULL,
PRIMARY KEY (`post_id`)
) ENGINE=InnoDB
DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
ROW_FORMAT=COMPRESSED
--------------
--------------
insert into `stay_nfo` (`post_id`,`idstays_nfo`,`ch_start_stay_date`,`ch_end_stay_date`) values
(259, 275, '2015-11-21', '2016-01-09'),
(261, 277, '2015-12-04', '2016-01-09'),
(260, 276, '2015-12-26', '2016-01-09'),
(213, 267, '2016-01-02', '2016-01-09')
--------------
--------------
select * from stay_nfo order by ch_start_stay_date
--------------
+---------+--------------------+------------------+-------------+
| post_id | ch_start_stay_date | ch_end_stay_date | idstays_nfo |
+---------+--------------------+------------------+-------------+
| 259 | 2015-11-21 | 2016-01-09 | 275 |
| 261 | 2015-12-04 | 2016-01-09 | 277 |
| 260 | 2015-12-26 | 2016-01-09 | 276 |
| 213 | 2016-01-02 | 2016-01-09 | 267 |
+---------+--------------------+------------------+-------------+
--------------
select max(ch_end_stay_date) into @endperiod from stay_nfo
--------------
--------------
SELECT date_format(ch_start_stay_date,'%m-%d-%Y') AS BeginPeriod,
date_format(@endperiod, '%m-%d-%Y') AS EndPeriod,
idstays_nfo AS number_contract,
post_id AS number_customer
FROM stay_nfo
WHERE ch_start_stay_date>=NOW()
ORDER BY `stay_nfo`.`ch_start_stay_date` ASC
--------------
+-------------+------------+-----------------+-----------------+
| BeginPeriod | EndPeriod | number_contract | number_customer |
+-------------+------------+-----------------+-----------------+
| 11-21-2015 | 01-09-2016 | 275 | 259 |
| 12-04-2015 | 01-09-2016 | 277 | 261 |
| 12-26-2015 | 01-09-2016 | 276 | 260 |
| 01-02-2016 | 01-09-2016 | 267 | 213 |
+-------------+------------+-----------------+-----------------+
--------------
select post_id,
(SELECT distinct ch_end_stay_date FROM stay_nfo WHERE ch_end_stay_date >= ALL (SELECT ch_end_stay_date FROM stay_nfo)) as EndPeriod
from stay_nfo
--------------
+---------+------------+
| post_id | EndPeriod |
+---------+------------+
| 213 | 2016-01-09 |
| 259 | 2016-01-09 |
| 260 | 2016-01-09 |
| 261 | 2016-01-09 |
+---------+------------+
--------------
SELECT date_format(ch_start_stay_date,'%m-%d-%Y') AS BeginPeriod,
(select date_format(max(ch_end_stay_date),'%m-%d-%Y') from stay_nfo) AS EndPeriod,
idstays_nfo AS number_contract,
post_id AS number_customer
FROM stay_nfo
WHERE ch_start_stay_date>=NOW()
ORDER BY `stay_nfo`.`ch_start_stay_date` ASC
--------------
+-------------+------------+-----------------+-----------------+
| BeginPeriod | EndPeriod | number_contract | number_customer |
+-------------+------------+-----------------+-----------------+
| 11-21-2015 | 01-09-2016 | 275 | 259 |
| 12-04-2015 | 01-09-2016 | 277 | 261 |
| 12-26-2015 | 01-09-2016 | 276 | 260 |
| 01-02-2016 | 01-09-2016 | 267 | 213 |
+-------------+------------+-----------------+-----------------+
--------------
COMMIT
--------------
--------------
SET AUTOCOMMIT = 1
--------------
Appuyez sur une touche pour continuer... |
Partager