Mysql – Reserving dates with quantities, table schema wrong

MySQL

Okay I have a table like so.

ID   Productname  Quantity          Image       QuantityOut  LastDateReserved

1    productOne          5     'image.jpg'                 3       2013-03-15
2    productTwo          1     'product.jpg'               2       2013-03-02

I have a page that checks for the last part of an event date + 5 days buffer. Now here is where I get really confused. Say for instance I want to check out only 3 of productOne, how would I make that work with this table schema? Do I need to add more fields or tables? The way I have it now is that my LastDateReserved would be overwritten with a new date. Thus erasing the old date and not being accurate on when the earliest date actually is. What would be the proper table structure here?

If my question needs to be reworded, please let me know and I will change it.

Best Answer

Typically the way you would do this is using another reference table to hold the product ID with a date reserved. Something like this:

ProductID    DateReserved
1            2013-03-15
1            2013-02-15
2            2013-03-02

Then you could join this table to the table you currently have to get the x most recent. Your SQL could look something like this:

Select top 3 * from YourTable 
Inner Join ReferenceTable on YourTable.ID = ReferenceTable.ProductID
Order by DateReserved DESC

Related Solutions

Mysql – How to display all the dates between multiple two dates in a table

The easiest way is to have a calendar table, defined in the following way:

CREATE TABLE calendar
(
    a_day date PRIMARY KEY
) ;

... and filled with all the relevant dates (i.e.: all days from 1990-1-1 to 2100-12-31). For the sake of simplicity, we will fill it only with year 2013:

INSERT INTO 
     calendar (a_day)
VALUES
    ('2013-01-01'),
    ('2013-01-02'),
    ('2013-01-03'),
    ('2013-01-04'),
    ('2013-01-05'),
    -- everything up to
    ('2013-12-31') ;

At this point, you can just have a JOIN with the two tables; with the join condition not being an equality, but a range condition:

SELECT
     t.id, c.a_day
FROM
     t
     JOIN calendar c ON c.a_day BETWEEN t.start_date AND t.end_date 
ORDER BY
     t.id, c.a_day ;

... and get

id | a_day     
-: | :---------
 1 | 2013-01-14
 1 | 2013-01-15
 1 | 2013-01-16
 1 | 2013-01-17
 1 | 2013-01-18
 2 | 2013-02-01
 2 | 2013-02-02
 2 | 2013-02-03
 2 | 2013-02-04

You can see all the setup at dbfiddle here

How to Get Dates That Wrap Around New Year from Non-Datetime Columns in MySQL

You can use MAKEDATE(), it makes the day_of_year to a date.

Test:

mysql> SELECT * FROM cli_table;
+-------+--------------+-------------+-------+
| month | day_of_month | day_of_year | value |
+-------+--------------+-------------+-------+
|     1 |            1 |           1 | 23.00 |
|     1 |            2 |           2 | 22.95 |
|    12 |           31 |         365 | 23.12 |
+-------+--------------+-------------+-------+
3 rows in set (0.00 sec)

mysql> SELECT 
    -> MAKEDATE(YEAR(CURDATE()),day_of_year) AS Date,
    ->     value AS DayValue
    -> FROM test.cli_table;
+------------+----------+
| Date       | DayValue |
+------------+----------+
| 2015-01-01 |    23.00 |
| 2015-01-02 |    22.95 |
| 2015-12-31 |    23.12 |
+------------+----------+
3 rows in set (0.00 sec)

mysql>

You can try it in this Fiddle.

EDIT:

According of what you want, you can make it with pure SQL:

mysql> SELECT * FROM test.cli_table;
+-------+--------------+-------------+-------+
| month | day_of_month | day_of_year | value |
+-------+--------------+-------------+-------+
|     1 |            1 |           1 |  5.00 |
|     1 |            2 |           2 |  6.00 |
|     1 |            3 |           3 |  7.00 |
|    12 |           25 |         359 | 23.00 |
|    12 |           26 |         360 | 22.95 |
|    12 |           27 |         361 |  1.00 |
|    12 |           28 |         362 |  2.00 |
|    12 |           29 |         363 |  3.00 |
|    12 |           30 |         364 |  4.00 |
|    12 |           31 |         365 | 23.12 |
+-------+--------------+-------------+-------+
10 rows in set (0.00 sec)

mysql> SELECT 
    -> v.selected_date,
    -> v.dayyear,
    -> c.value AS DayValue,
    -> c.day_of_year 
    -> FROM test.cli_table AS c
    -> JOIN (SELECT 
    -> adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) AS selected_date,
    -> DAYOFYEAR(adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i)) AS dayyear
    -> FROM 
    -> (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
    -> (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
    -> (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
    -> (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
    -> (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) AS v
    -> ON (v.selected_date between '2015-12-25' AND '2016-01-03'
    -> AND v.dayyear=c.day_of_year);
+---------------+---------+----------+-------------+
| selected_date | dayyear | DayValue | day_of_year |
+---------------+---------+----------+-------------+
| 2015-12-25    |     359 |    23.00 |         359 |
| 2015-12-26    |     360 |    22.95 |         360 |
| 2015-12-27    |     361 |     1.00 |         361 |
| 2015-12-28    |     362 |     2.00 |         362 |
| 2015-12-29    |     363 |     3.00 |         363 |
| 2015-12-30    |     364 |     4.00 |         364 |
| 2015-12-31    |     365 |    23.12 |         365 |
| 2016-01-01    |       1 |     5.00 |           1 |
| 2016-01-02    |       2 |     6.00 |           2 |
| 2016-01-03    |       3 |     7.00 |           3 |
+---------------+---------+----------+-------------+
10 rows in set (0.00 sec)

mysql>

Query Meaning:

SELECT 
    v.selected_date,
    v.dayyear,
    c.value AS DayValue,
    c.day_of_year 
FROM test.cli_table AS c
--- > This JOIN is generating all the dates between the selected_dates ('2015-12-25' AND '2016-01-03')
JOIN (SELECT 
        adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) AS selected_date,
        DAYOFYEAR(adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i)) AS dayyear
      FROM 
     (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
     (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
     (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
     (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
     (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) AS v
--- > Here I'm matching every dayyear of the generated dates with the cli_table.day_of_year.
ON (v.selected_date between '2015-12-25' AND '2016-01-03'
    AND v.dayyear=c.day_of_year);