find entry based on date

[RemcoDeWit]

I've a database with product serial numbers and would like to be able to keep track of which product resides on which location.

[tt]+----+-------------+--------+-----------+------------+
| ID | Serialnumber| PartID | LocationID| InstDate |
+----+-------------+--------+-----------+------------+
| 6 | 456123 | 33 | 553 | 2006-01-25 |
| 5 | 456123 | 33 | 3 | 2006-01-20 |
| 4 | 456132 | 33 | 1 | 2006-01-21 |
| 3 | 123456 | 4 | 2 | 2006-01-17 |
| 2 | 123456 | 4 | 80 | 2006-01-18 |
| 1 | 123456 | 4 | 1 | 2006-01-21 |
+----+-------------+--------+-----------+------------+[/tt]

The installation date doesn't need to be enterd in chronical order.

I need an MySQL query which is able to return which Serialnumbers are currenly present on a specific location.

I've used the GROUP BY 'Serialnumber' command but this only returns the last added entry, Not with the last InstDate

With the example above the result should be for locationID 1
[tt]+----+-------------+--------+-----------+------------+
| ID | Serialnumber| PartID | LocationID| InstDate |
+----+-------------+--------+-----------+------------+
| 4 | 456132 | 33 | 1 | 2006-01-21 |
| 1 | 123456 | 4 | 1 | 2006-01-21 |
+----+-------------+--------+-----------+------------+[/tt]

Could anyone help me with the right query?

 

[r937]

let me see if i understand you correctly

this query --

WHERE locationID = 1

will return serial numbers 456132 and 123456, right?

and that's not good enough?

r937.com | rudy.ca

 

[RemcoDeWit]

When a product comes in the serial number is put into the system and a locationID is being added. When the product moves to another location the serial number is being added again. This time with the new locationID.

This way I'm able to see some kind of history where the product has been.

But what if I would like to know which serial numbers are currently available on a specific location.

If I would use a query with WHERE locationID = 80 for example I would get the result:
[tt]+----+-------------+--------+-----------+------------+
| ID | Serialnumber| PartID | LocationID| InstDate |
+----+-------------+--------+-----------+------------+
| 2 | 123456 | 4 | 80 | 2006-01-18 |
+----+-------------+--------+-----------+------------+[/tt]

Which would be incorrect because the product is moved to location 1 on the 21st.

 

[KarveR]

add a field to indicate that the product has been moved, on insert set it to '0' and on move set it to '1'

then just query for stuff thats not moved='1' (as its been moved and no longer there ...).

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[r937]

okay, sorry, you simply want the row with the latest date per Serialnumber, i should've seen that

select ID
     , Serialnumber
     , PartID
     , LocationID
     , InstDate   
  from yourtable as T
 where InstDate
     = ( select max(InstDate)
           from yourtable
          where Serialnumber  
              = T.Serialnumber )

r937.com | rudy.ca

 

[RemcoDeWit]

Thanks, This seems to be working.

Regards,

Remco