· 6 years ago · Mar 16, 2019, 06:32 PM
1mysql> desc depot;
2+-------+----------+------+-----+---------+-------+
3| Field | Type | Null | Key | Default | Extra |
4+-------+----------+------+-----+---------+-------+
5| recd | date | YES | | NULL | |
6| id | int(11) | YES | | NULL | |
7+-------+----------+------+-----+---------+-------+
8
9mysql> select * from depot;
10+---------------------+------+
11| recd | id |
12+---------------------+------+
13| 2012-07-09 | 33 |
14| 2012-07-11 | 32 |
15| 2012-07-15 | 32 |
16+---------------------+------+
173 rows in set (0.00 sec)
18
19+------------+------+
20| recd | id |
21+------------+------+
22 2012-07-01 0
23 2012-07-02 0
24 2012-07-03 0
25 2012-07-04 0
26 2012-07-05 0
27 2012-07-06 0
28 2012-07-07 0
29 2012-07-08 0
30 2012-07-09 33
31 2012-07-10 0
32 2012-07-11 32
33 2012-07-12 0
34 2012-07-13 0
35 2012-07-14 0
36 2012-07-15 32
37 2012-07-16 0
38 2012-07-17 0
39 2012-07-18 0
40 2012-07-19 0
41 2012-07-20 0
42 2012-07-21 0
43 2012-07-22 0
44 2012-07-23 0
45 2012-07-24 0
46 2012-07-25 0
47 2012-07-26 0
48 2012-07-27 0
49 2012-07-28 0
50 2012-07-29 0
51 2012-07-30 0
52 2012-07-31 0
53
54select c.cal_date, coalesce(d.id, 0) id
55from calendar c
56left join depot d on d.recd = c.cal_date
57where c.cal_date between '2012-07-01' and '2012-07-31'
58order by c.cal_date
59
60create table calendar (
61 cal_date date primary key
62);
63
64insert into calendar values
65('2012-07-01'),
66('2012-07-02'),
67...
68('2012-07-31');
69
70BASE TABLE
71
72 CREATE TABLE `deopt` (
73 `recd` datetime DEFAULT NULL,
74 `id` int(11) DEFAULT NULL
75) ENGINE=InnoDB;
76Seed records to the base table
77
78insert into deopt values ('2012-07-09 23:08:54',22);
79insert into deopt values ('2012-07-11 23:08:54',22);
80insert into deopt values ('2012-07-11 23:08:54',2222);
81insert into deopt values ('2012-07-12 23:08:54',22);
82insert into deopt values ('2012-07-14 23:08:54',245);
83Create a table for dates of a month
84
85CREATE TABLE seq_dates
86(
87 sdate DATETIME NOT NULL,
88
89);
90Create a Stored Procedure to create records for a called month
91
92delimiter //
93DROP PROCEDURE IF EXISTS sp_init_dates;
94
95CREATE PROCEDURE sp_init_dates (IN p_fdate DATETIME, IN p_tdate DATETIME)
96BEGIN
97DECLARE v_thedate DATETIME;
98TRUNCATE TABLE seq_dates;
99SET v_thedate = p_fdate;
100WHILE (v_thedate <= p_tdate) DO
101 INSERT INTO seq_dates (sdate)
102 VALUES (v_thedate);
103 SET v_thedate = DATE_ADD(v_thedate, INTERVAL 1 DAY);
104END WHILE;
105END;
106
107delimiter ;
108Call the procedure for July month with starting and ending values to be seeded to seq_dates table.
109
110call sp_init_dates ('2012-07-01','2012-07-31');
111RESULT QUERY - To fetch records of all dates in a month and its corresponding ids keeping 0 inplace of null for ids.
112
113select date(seq_dates.sdate),coalesce (deopt.id,0) from seq_dates LEFT JOIN deopt ON date(deopt.recd)=date(seq_dates.sdate);
114
115
116
117+-----------------------+-----------------------+
118| date(seq_dates.sdate) | coalesce (deopt.id,0) |
119+-----------------------+-----------------------+
120| 2012-07-01 | 0 |
121| 2012-07-02 | 0 |
122| 2012-07-03 | 0 |
123| 2012-07-04 | 0 |
124| 2012-07-05 | 0 |
125| 2012-07-06 | 0 |
126| 2012-07-07 | 0 |
127| 2012-07-08 | 0 |
128| 2012-07-09 | 22 |
129| 2012-07-09 | 22 |
130| 2012-07-10 | 0 |
131| 2012-07-11 | 22 |
132| 2012-07-11 | 2222 |
133| 2012-07-11 | 22 |
134| 2012-07-11 | 2222 |
135| 2012-07-12 | 22 |
136| 2012-07-13 | 0 |
137| 2012-07-14 | 245 |
138| 2012-07-15 | 0 |
139| 2012-07-16 | 0 |
140| 2012-07-17 | 0 |
141| 2012-07-18 | 0 |
142| 2012-07-19 | 0 |
143| 2012-07-20 | 0 |
144| 2012-07-21 | 0 |
145| 2012-07-22 | 0 |
146| 2012-07-23 | 0 |
147| 2012-07-24 | 0 |
148| 2012-07-25 | 0 |
149| 2012-07-26 | 0 |
150| 2012-07-27 | 0 |
151| 2012-07-28 | 0 |
152| 2012-07-29 | 0 |
153| 2012-07-30 | 0 |
154| 2012-07-31 | 0 |
155+-----------------------+-----------------------+
15635 rows in set (0.00 sec)