Basic MySQL Trick: Keep tracking the user’s working place based on the time-range

Hey guys, it’s been a long time no post since I’ve been on my blog. Just a quick update for everyone dropping by on my blog and keep wanted for your reply on my blog. =)

It’s been 3 months since I’ve got a nice job. A lot of new friends and it means, A LOT OF EXPERIENCES you’ve got! There are so many things that you’ll never get while you are sitting on the uni. You’ll get a bunch of new experiences and chances to boost your skills to solve the real-world problem that can’t just solve by implementing your theory you’ve got from uni. So far, it was really nice to broaden up our horizon about all things.

A simple study-case that recently I got while I am working at my current job is:
“You have to record every transaction on the company. The problem raised is that you will have to keep track the user’s movement of working place, it means, person A on a range of time is working on Department A, and on another time is working on Department B. Let’s get into a real sample: George is starts to work on Department A on July 10, 2012. He makes a transaction of TRX0001. On August 15, 2012, George is move to Department B, and makes another transaction. On the history, you have to show that on TRX0001, George is to be shown works at Department A. But after August 15, 2012, George is to be shown works at Department B”.

How to solve that problem? Let’s have the table on mysql.

And take an example to fill the table:

What you are going to show is a table like:

It should be done by running below query:

[php]

SELECT th.TrxID, th.Date, th.UserID, muh.UserName, Dept.DeptName FROM trxheader th
INNER JOIN mstuserheader muh ON (th.UserID = muh.UserID)
INNER JOIN mstuserdetail mud ON (th.UserID = mud.UserID)
INNER JOIN ( SELECT md.DeptID, md.DeptName FROM mstdept md) AS Dept ON Dept.DeptID = mud.DeptID
WHERE mud.Date = ( SELECT MAX(mud.Date) FROM mstuserdetail mud WHERE mud.UserID = th.UserID AND mud.Date <= th.Date)

[/php]

Explanation of the code:

Let’s break down the code we have:

This part of code is to show the first part of the table (shown on the image above as numbered 1):
SELECT th.TrxID, th.Date, th.UserID, muh.UserName, Dept.DeptName FROM trxheader th

And we join it with the mstuserheader table (shown on the image as numbered 2):
INNER JOIN mstuserheader muh ON (th.UserID = muh.UserID)

And also join this table (as numbered 3):
INNER JOIN mstuserdetail mud ON (th.UserID = mud.UserID)

And the last table to show the department (as numbered 4):
INNER JOIN ( SELECT md.DeptID, md.DeptName FROM mstdept md) AS Dept ON Dept.DeptID = mud.DeptID

The most important code to filter the date based on the transaction date is (between two gray lines):
WHERE mud.Date = ( SELECT MAX(mud.Date) FROM mstuserdetail mud WHERE mud.UserID = th.UserID AND mud.Date <= th.Date)

Hope it helps and inspires you. If you have any ideas or suggestions about the code (have another better ways to make it), pls let me know by sharing your comment below. I’ll be really gladful if you would like to. =)

Stay reading my blog and, cheers... :)