Demo of getting records between two dates

We will use our student table and link this table to fee payment table to test various quires to collect records between two date ranges. Here are our two tables with some sample data.

fee_id	id	dt	amount
1	1	2013-01-08	200
2	1	2013-01-10	100
3	2	2013-01-24	120
4	3	2013-02-12	211
5	2	2013-02-07	150
6	3	2013-02-06	135
7	4	2013-02-14	100

id	name	class	mark
1	John Deo	Four	75
2	Max Ruin	Three	85
3	Arnold	Three	55
4	Krish Star	Four	60
5	John Mike	Four	60
6	Alex John	Four	55

Here is our query to display records between two date ranges

SELECT * FROM `student_fee` WHERE dt BETWEEN '2013-01-09' and '2013-01-30'

We will get output displaying two records

fee_id	id	dt	amount
2	1	2013-01-10	100
3	2	2013-01-24	120

Now Linking two tables we will display the records

SELECT * FROM `student_fee`,student WHERE student_fee.id=student.id and  dt BETWEEN '2013-01-09' and '2013-01-30'

The output is here

fee_id	id	dt	amount	id	name	class	mark
2	1	2013-01-10	100	1	John Deo	Four	75
3	2	2013-01-24	120	2	Max Ruin	Three	85

Try to find out total fee collected in Jan 2013
Try to find out how much John Deo had paid in Feb 2013

Download sql dump of student table
Download sql dump of studnet_fee table

This article is written by plus2net.com team.