Demo of getting records between two dates using two tables

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 Feb 2013

SELECT SUM(amount) FROM student_fee 
	WHERE MONTH(dt)=2 AND YEAR(dt)=2013

Ouput is

596

By using DATE_FORMAT

SELECT SUM(amount) FROM student_fee 
	WHERE DATE_FORMAT(dt,'%b-%Y')='Feb-2013';

OR

SELECT SUM(amount) FROM student_fee 
	WHERE DATE_FORMAT(dt,'%M-%Y')='February-2013';

Try to find out how much John Deo had paid in Year 2013

SELECT * FROM `student_fee`,student 
WHERE student_fee.id=student.id 
AND  dt BETWEEN '2013-01-01' and '2013-12-31'
AND student.name='John Deo'

Output

fee_id	id	dt	amount	id	name	class	mark	gender
1	1	2013-01-08	200	1	John Deo	Four	75	female
2	1	2013-01-10	100	1	John Deo	Four	75	female

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

← Subscribe to our YouTube Channel here

plus2net.com

Post your comments , suggestion , error , requirements etc here

Detail