I’ve inherited my father’s lawn care business and am developing the database to track our work. In a table named daily_log I create new entries for each person on property. Usually just me but not always. What I need to select out is a single entry for the most recent entry for each property. To help plan who needs me again when and can show them on the home page how close they are to the top of my rotation. So I may have 35 entries for on location, but I just need one… if more than one worker and it needs to be the most recent. Is there a logical way to filter this down in a MySQL query?
SELECT t.property_id
, t.foo
, t.bar
, t.entrydate
FROM ( SELECT property_id
, MAX(entrydate) AS latest
FROM daily_log
GROUP
BY property_id ) AS m
INNER
JOIN daily_log AS t
ON t.property_id = m.property_id
AND t.entrydate = m.latest
Thank you, I think I can get that to work!
if I just run it without CREATE VIEW… AS on the first line it seems fine, but as soon as I try to save a view to work with I get an error, #1349 - View’s SELECT contains a subquery in the FROM clause .
CREATE VIEW bf_latest_log AS
SELECT t.Item_ID, t.Property_ID, t.Employee_ID, t.Minutes, t.job_description, t.price, t.Done, t.Paid, t.Billed, t.deleted
FROM ( SELECT property_id
, MAX(Done) AS latest
FROM bf_daily_gol
GROUP
BY Property_ID ) AS m
INNER
JOIN bf_daily_gol AS t
ON t.Property_ID = m.Property_ID
AND t.Done = m.latest
That is a Mysql limitation. If you want to create a view you need to rewrite the select query. One way
create view bf_latest_log as
select t.Item_ID,
t.Property_ID,
t.Employee_ID,
t.Minutes,
t.job_description,
t.price,
t.Done,
t.Paid,
t.Billed,
t.deleted
from bf_daily_gol as t
where t.done =
(select max(Done)
from bf_daily_gol
where bf_daily_gol.Property_ID = t.Property_ID)
unfortunately, you can’t use that query in a view
however, you can work around it by defining the subquery as a view, then using that view in the main query, and then you can define the main query as a view
Well, between the two of you I’ve gotten what I asked for. But I overlooked a hole in my logic. Lawns that may have been done last year, but are not in my rotation anymore. Ironically the two at the top of my list are family members that show no activity this year. Our own, which I don’t need customers worrying when I’ll fit it in, and mother’s cousin who sold the house. Both are marked as soft deletes in a bf_properties table, but if I’m not careful, the delete column for properties table could interfere with delete column from log table. Can anyone show me how to add a filter for that? Please and thanks for all the help I’ve already gotten!
CREATE VIEW bf_latest_log AS
SELECT t.Item_ID,
t.Property_ID,
t.Employee_ID,
t.Minutes,
t.job_description,
t.price,
t.Done,
t.Paid,
t.Billed,
t.deleted
from bf_daily_gol AS t
WHERE t.done =
(SELECT MAX(Done)
FROM bf_daily_gol
WHERE bf_daily_gol.Property_ID = t.Property_ID)
I’ve inherited my father’s lawn care business and am developing the database to track our work. In a table named daily_log I create new entries for each person on property. Usually just me but not always. We select out a single entry for the most recent entry to each property, to help plan who needs me again when and can show them on the home page how close they are to the top of my rotation. So I may have 35 entries for one location, but they just see one… if more than one worker and it’s the most recent.
CREATE VIEW bf_latest_log AS
SELECT t.Item_ID,
t.Property_ID,
t.Employee_ID,
t.Minutes,
t.job_description,
t.price,
t.Done,
t.Paid,
t.Billed,
t.deleted
from bf_daily_gol AS t
WHERE t.done =
(SELECT MAX(Done)
FROM bf_daily_gol
WHERE bf_daily_gol.Property_ID = t.Property_ID)
But I overlooked a hole in my logic. Lawns that may have been done last year, but are not in my rotation anymore. Ironically the two at the top of my list are family members that show no activity this year. Our own, which I don’t need customers worrying when I’ll fit it in, and mother’s cousin who sold the house. Both are marked as soft deletes in a bf_properties table, but if I’m not careful, the delete column for properties table could interfere with delete column from log table. Can anyone show me how to add a filter for that?
I’ve considered basing the secondary view, off of this first view, but then I’m just overwriting the soft deletes, it’d probably work until I had to soft delete an entry from the work_log. Not sure if that’ll ever be an issue, but it still feels like the wrong way…
DROP VIEW IF EXISTS bf_z_log;
CREATE VIEW bf_z_log AS
SELECT bf_daily_gol.Item_ID,
bf_daily_gol.Property_ID,
bf_daily_gol.Employee_ID,
bf_daily_gol.Minutes,
bf_daily_gol.job_description,
bf_daily_gol.price,
bf_daily_gol.Done,
bf_daily_gol.Paid,
bf_daily_gol.Billed,
bf_properties.deleted
FROM bf_daily_gol
INNER JOIN bf_properties
ON bf_daily_gol.Property_ID=bf_properties.Property_ID
WHERE bf_properties.deleted = '0';
after further review, I’ve caught another hole in the logic… this one pertaining to the original premise of the question. If I have an additional employee on the same day then the property is appearing in the selection twice.
I’m fudging the logic a bit right now by adding the last line of
Employee_ID = 'John'
it work’s for proof of concept now, because I’m always on the job site myself so far, but if I ever hire someone else to do a yard with me, it should still filter down to a single entry per the most recent date irrespective of employee id. so right now I am creating the first view with
DROP VIEW IF EXISTS bf_z_log;
CREATE VIEW bf_z_log AS
SELECT bf_daily_gol.Item_ID,
bf_daily_gol.Property_ID,
bf_daily_gol.Employee_ID,
bf_daily_gol.Minutes,
bf_daily_gol.job_description,
bf_daily_gol.price,
bf_daily_gol.Done,
bf_daily_gol.Paid,
bf_daily_gol.Billed,
bf_properties.deleted
FROM bf_daily_gol
INNER JOIN bf_properties
ON bf_daily_gol.Property_ID=bf_properties.Property_ID
WHERE bf_properties.deleted = '0'
AND bf_daily_gol.Employee_ID = 'John';
and then filtering out the most recent entries with
DROP VIEW IF EXISTS bf_latest_log;
CREATE VIEW bf_latest_log AS
SELECT t.Item_ID,
t.Property_ID,
t.Employee_ID,
t.Minutes,
t.job_description,
t.price,
t.Done,
t.Paid,
t.Billed,
t.deleted
from bf_z_log AS t
WHERE t.done =
(SELECT MAX(Done)
FROM bf_z_log
WHERE bf_z_log.Property_ID = t.Property_ID);
Any help greatly appreciated.
so how do you want to choose which one to exclude from the results?
It shouldn’t matter… as long as there is only one per a property. Right now I’m maintaining 18. Say I’m logged on the tractor and an assistant is on the weedeater and blower at the same location on the same day, all of a sudden I have a 19th “duplicate”. If I fudge the date and say the weed eater was there the day before me, then I go back to a list of 18. Would the keyword UNIQUE help filter it down any?
unfortunately, sql does not support this criterion
WHERE employee_id = 'it doesn''t matter'
you’ll have to say how to pick the one you do want
not at all, sorry
Haha, I kept thinking about that phrase, “it doesn’t matter” while I was out mowing. I hope it didn’t come off as smart elec, especially since you’ve been so gracious on multiple occasions now. But it really doesn’t. As long as it gives me one and not both, I’d be content. And as long as I’m always on site, and for the near future it looks like I will be, it works for now. But I’m just trying to learn to do it right. It feels “hacked” right now… but I’m not sure what logical distinction to draw between them. My first thought is add another column for “mower” and just filter for when a “mower” was last present… that’s the most logical thing I can come up with for now… It usually comes to me in the middle of a field listening to someone else’s podcast. Haha, the country cowboy coder! I was already considering a ‘mow’ column to distinguish a ‘pruning’, so it could be something like ‘nature’ since description is taken, but it would describe the nature of the work and only filter down to the last time I was actually on site to mow.
give it some more thought, and when you’re ready, please give us complete column-by-column layouts of your tables by running the SHOW CREATE TABLE command for each table
I hadn’t used LIKE before, but I replaced
AND bf_daily_gol.Employee_ID = 'John';
with
AND bf_daily_gol.job_description LIKE '%mow%';
thanks again for all of your help! There is one anomaly, I’ll have to see if it’s shows up again as I create new entries. If you’ll look at PohlnerLandscaping.com you’ll notice that Pharr’s done on the 3rd, is listed below all of the one’s listed on the 5th. I’m reposting final code for reference.
DROP VIEW IF EXISTS bf_z_log;
CREATE VIEW bf_z_log AS
SELECT bf_daily_gol.Property_ID,
bf_daily_gol.job_description,
bf_daily_gol.Done,
bf_properties.deleted
FROM bf_daily_gol
INNER JOIN bf_properties
ON bf_daily_gol.Property_ID=bf_properties.Property_ID
WHERE bf_properties.deleted = '0'
AND bf_daily_gol.job_description LIKE '%mow%';
DROP VIEW IF EXISTS bf_latest_log;
CREATE VIEW bf_latest_log AS
SELECT t.Property_ID,
t.job_description,
t.Done
from bf_z_log AS t
WHERE t.done =
(SELECT MAX(Done)
FROM bf_z_log
WHERE bf_z_log.Property_ID = t.Property_ID);