Runcorn Runcorn - 2 months ago 6
MySQL Question

Achieve data between particular date in Mysql

I have three tables named

User
,
Role
and
Balance_updates
.
User
table hold info about user,
Role
depicts type of user like Customer,Admin,Manager and
Balance_updates
store all transaction regarding balance i.e it store history about transaction related to balance.

Tables

User

+-----------------------+--------------+------+-----+-------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+--------------+------+-----+-------------------+-------+
| username | varchar(20) | NO | PRI | NULL | |
| password | varchar(32) | NO | | NULL | |
| email | varchar(50) | YES | | NULL | |
| role_id | int(11) | NO | MUL | NULL | |
| mobile_wallet_balance | double(20,2) | NO | | 0.00 | |
| merit_point | bigint(20) | YES | | NULL | |
| status | int(11) | NO | | NULL | |
| is_auto_btm_enabled | tinyint(1) | YES | | 0 | |
| created_at | datetime | YES | | NULL | |
| updated_at | timestamp | YES | | CURRENT_TIMESTAMP | |
| gender | varchar(20) | YES | | NULL | |
| validity | date | YES | | NULL | |
| status_desc | text | YES | | NULL | |
+-----------------------+--------------+------+-----+-------------------+-------+

Role

+-------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(25) | NO | UNI | NULL | |
| description | varchar(255) | YES | | NULL | |
| value | varchar(25) | NO | | NULL | |
+-------------+--------------+------+-----+---------+----------------+


Balance_updates

+------------+--------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+-------------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| username | varchar(15) | NO | | NULL | |
| role_id | int(11) | YES | | NULL | |
| amount | double(20,2) | YES | | NULL | |
| updated_at | timestamp | NO | | CURRENT_TIMESTAMP | |
+------------+--------------+------+-----+-------------------+----------------+


Data in Balance_updates

Objective

I want to generate all users and their respective balance in any given date;
For example if i want the balance statement for a given date
2012-12-28
it should generate latest balance from
Balance_updates
.

What I have tried

SELECT DISTINCT (
u.username
), r.value, u.amount AS `amount`
FROM Balance_updates u
INNER JOIN Role r ON u.role_id = r.id
WHERE u.amount > 0.0 && UNIX_TIMESTAMP( u.updated_at ) < UNIX_TIMESTAMP( '2013-1-3 23:59:59' )
ORDER BY r.value, UNIX_TIMESTAMP( u.updated_at ) DESC


RESULT and PROBLEM

As you expect it is returning all values from Balance_updates i.e every other User transaction recorded.

Question:

1.How can i achieve latest balance statement of a user in a particular date.If in that date user balance is not changed then show his balance in previous time last changed.

Any help will be appreciated;

Answer

Not tested, but I have the feeling this could work (and could be optimised).

SELECT DISTINCT u.username, r.value, u.amount AS `amount`
FROM Balance_updates u
INNER JOIN Role r ON u.role_id = r.id
WHERE u.updated_at = (
    SELECT MAX(inner_u.updated_at)
    FROM Balance_updates AS inner_u
    WHERE
        inner_u.username = u.username
        && amount > 0
        && UNIX_TIMESTAMP( inner_u.updated_at ) < UNIX_TIMESTAMP( '2013-1-3 23:59:59' )
)
ORDER BY r.value, UNIX_TIMESTAMP( u.updated_at ) DESC

Just one off-topic piece of advice:

  • add a surrogate key to table User (e.g. User.id); make it the primary key
  • replace column Balance_updates.username with (e.g.) Balance_updates.user_id, and store a reference to User.id (a foreign key).

or

  • change column Balance_updates.username to a VARCHAR(20) to match the type of User.username
Comments