How to extract month and year values from database column and compare it to current month and date

,

Which it is.

Which it also is.

(EDIT: Changed the query’s spacing so you can see the query’s not doing anything sneaky.)

Oh my bog you have it beautifully. But for some reason when i try on mine those values are switch and carlas 1 doesnt show either :sob:

I tried your other one and it said incompleted tasks, completed tasks and late tasks was undefined :smiling_face_with_tear:

Yeah, in my last query, i’ve changed the final column names to “incomplete”, “complete”, and “late”, so you’d have to change your PHP code to look for those instead.

waaaaaait in your other one u dont have the IfNull(T.incomplete_tasks,0)as incomplete part. Thats why.

1 Like

The image above uses this: (for ease of copy-pasting)

SELECT employee.empfname, employee.emplname, 
       IfNull(T.incompleted_tasks,0) AS incomplete,
       IfNull(T.completed_tasks,0) AS complete,
       IfNull(T.late_tasks,0) AS late 
FROM   employee
LEFT   JOIN (
  SELECT employee.eid,
         SUM(CASE WHEN tasks.stats = 0 AND tasks.pf = 1 THEN 1 ELSE 0 END) AS incompleted_tasks,
         SUM(CASE WHEN tasks.stats = 1 AND tasks.pf = 0 THEN 1 ELSE 0 END) AS completed_tasks,
         SUM(CASE WHEN tasks.stats = 1 AND tasks.pf = 1 THEN 1 ELSE 0 END) AS late_tasks
  FROM   employee
  LEFT   JOIN tasks ON tasks.eemail = employee.eid
  WHERE  tasks.datepost BETWEEN "2022-09-01" AND "2022-09-30"
  GROUP  BY employee.eid 
) AS T 
ON employee.eid = T.eid;

You’ll need to replace the date strings, obviously, but i’m running this query in a fiddle, rather than in PHP, so i needed to put actual strings in there for testing.

This works well when i use the direct dates but the moment i add the ones to check for previous dates it goes back to that mess up. Is this wrong?

$StDate = new DateTime("first day of last month");
$EnDate = new DateTime("last day of last month");

 $StartDate = $StDate->format('Y-m-d');
 $EndDate = $EnDate->format('Y-m-d');

I call it in like this
WHERE tasks.datepost BETWEEN $StartDate AND $EndDate GROUP BY employee.eid

If you echo out $StartDate and $EndDate , do they look correct? and if so, are you absolutely sure your data is as you say it is in post 13?

This is what it echos 2022-09-012022-09-30 and the dates in my system shows like this 2022-09-01. So that should be right i think

oh wait. You’re sticking these in without quotes because you’re not doing parameter passing…

Try this as a temporary fix, and spend some time when you can looking at Prepared Statements. They’ll be a lot of help to you in the future.

WHERE tasks.datepost BETWEEN \"$StartDate\" AND \"$EndDate\" GROUP BY employee.eid
1 Like

IT FINNNAALLLLYYYY WORKKED. Maaaaaaan i was on this jazz for weeks. THANK YOU

1 Like

Only because it works didn’t mean it is correct. The query only works because of a bug in the old MySQL version and it will give you a random empfname which might lead to errors hard to find in the future. Also this query will not work in any other SQL dialect.

And, we are here to help people getting better. So we should not teach them wrong things

instead of

    GROUP BY tasks.eemail

i would write

    GROUP BY employee.empfname
           , employee.emplname

because (1) sometimes eemail might be NULL, and (2) i insist on my GROUP BY and SELECT clauses having at least a nodding acquantance with each other

2 Likes

Apologies for possibly sounding dumb here but what do u mean by nod?

He’s saying that even if you ignore the bug/feature/whatever in older MySQL versions that allow grouping by fields you’re not selecting, they should be related to one another. Which is perfectly fair and true, but i’ll be honest and say my group-by was the least of my concerns with that query when i wrote it :stuck_out_tongue:

1 Like

expertly summarized, thanks, m, that is indeed what i had in mind

to @cheeseburgers123 – “nod” means game recognize game

cheeseburgers, cheeseburgers

1 Like

Thats a peculiar nod but okay. waow :joy:

Ive never heard the word cheeseburger said so many times in 1 sentence. It repeats in my head now… Thanks. A lot.

1 Like

Ah kk

If you group by their last name and first name would that mean that if there were two people with the same first name and last name the results in the table would be wonky?
Also eemail is actually the foreign key in the tasks table so it holds the users ID not their email. I wasnt sane when i named my columns :sweat_smile:

well, sure it would

Yes. If there are 2 John Smiths, they will get lumped together if you group by John Smith.

If this is a concern for you, group by email and select MAX(fname), MAX(lname) or something (apply a group function that wont change the result of the group; a group gathered by email should have all the same fname and lname, so the MAX of that set of values will just be the fname and lname).

1 Like