Im trying to draw out the month and year values from the database column datepost to compare it to the current month and date for a report of sorts. Its meant to check records with the previous month. I tried a few times with code and checked syntax through websites but it still says that there is a syntax error near the EXTRACT area. Could someone please help me figure out what im missing?
$now = new DateTime();
$previousmonth = $now -> modify('first day of previous month');
$month = $previousmonth -> format('m');
$year = date('Y');
$Date = $year.$month;`
$query = $conn -> query("SELECT employee.empfname, employee.emplname,
COALESCE( (
SELECT COUNT( tasks.tid, EXTRACT (YEAR_MONTH from tasks.datepost) as yearmonth )
FROM tasks
WHERE tasks.eemail = employee.eid And tasks.yearmonth = $Date
AND tasks.stats = 0
AND tasks.pf = 1
), 0 ) AS incompleted_tasks, COALESCE((
SELECT COUNT( tasks.tid, EXTRACT (YEAR_MONTH from tasks.datepost) as yearmonth )
FROM tasks
WHERE tasks.eemail = employee.eid And tasks.yearmonth = $Date
AND tasks.stats = 1
AND tasks.pf = 0
),0) AS completed_tasks, COALESCE((
SELECT COUNT( tasks.tid, EXTRACT (YEAR_MONTH from tasks.datepost) as yearmonth )
FROM tasks
WHERE tasks.eemail = employee.eid And tasks.yearmonth = $Date
AND tasks.stats = 1
AND tasks.pf = 1
),0) AS late_tasks
FROM employee
WHERE 1");
Dates can be compared directly. You get the first date of last month, the last date of last month, and find all of your tasks where the date is BETWEEN last month and this month.
(Spitballing, untested/optimized)
SELECT employee.empfname,
employee.emplname,
SUM(CASE WHEN tasks.stats = 0 AND tasks.pf = 1 THEN 1 ELSE 0) AS incompleted_tasks,
SUM(CASE WHEN tasks.stats = 1 AND tasks.pf = 0 THEN 1 ELSE 0) AS completed_tasks,
SUM(CASE WHEN tasks.stats = 1 AND tasks.pf = 1 THEN 1 ELSE 0) AS late_tasks
FROM employee
LEFT JOIN tasks ON tasks.eemail = employee.eid
WHERE tasks.datepost BETWEEN :StartDate AND :EndDate
GROUP BY tasks.eemail
(Dare I summon the SQL gods to correct my work? Maybe.)
:StartDate and :EndDate in this example are PDO parameter placeholders, as youād normally be executing this as a Prepared Statement - youāll pass in those dates as parameters in your execute() call or bindParam().
It gives the output now but its only reading one persons info . Im supposed to get a table that looks like thisā¦
emp name | missed not submitted | submitted on time | missed and submitted
lana law | 0 | 0 | 0
carla dav | 0| 1 | 0
lana doe | 0 | 2 | 0
But instead i get this only one persons results, like thisā¦
emp name | missed not submitted | submitted on time | missed and submitted
lanadoe | 2 |0 |0
So it looks like the values for incompleted_tasks and completed_tasks have swapped since even if it only called one persons id it should show like
emp name | missed not submitted | submitted on time | missed and submitted
lanadoe | 0 |2 |0
sorry abt the table style, tried to get stuff directly under each column but it doesnt register more than 1 space
ā¦ do you have some data we could see to run this against? I canāt immediately see why it would do that unless you skipped the GROUP BY at the endā¦
At the end this is not a valid SQL statement because you select a column which is not in the group by statement. With MySQL 8 in standard settings you will get an error.
$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ā);
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 $StartDate AND $EndDate
GROUP BY tasks.eemail");
When I use the :StartDate method you used for the dates I got this error to check for the right syntax to use near ā:StartDate AND :EndDate GROUP BY tasks.eemailā so I used the other method to call the dates and got that swapped values result
code works fine for meā¦ Thallius is correct that in MySQL 8 my group by is bad, and would instead have to be GROUP BY employee.empfname, employee.emplname, but i definitely get 2 return rows from the query. (I only get 2 because employee 1 didnt do anything during the reporting period)
I would doubt itā¦ but you could try turning off the download part and just look at the HTML output from PHP.
Your report for this period should be that Lana Doe had 2 completed tasks, and Carla Davis had 1. You didnt actually have any late or incompleted tasks in the data you provided me.
The report is supposed to ideally show 0 wherever there isnt that condition. Its kinda supposed to be like a month end report thingy that shows a persons progress or lack of.
il try this and hopefully see if i can find an error. Thanks for you help
SELECT employee.empfname, employee.emplname, IfNull(T.incompleted_tasks,0),IfNull(T.completed_tasks,0),IfNull(T.late_tasks,0) 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;
(tho you probably want to clean up those column names, heh)
I also tried your logic by just seeing if the datepost was between start date and end date and that prints everybodies stuff but still mixes the values for incomplete tasks and complete tasks
nah nah by logic i jst meant the way you compared the dates, my method was to extract month and year
For lana doeās missed not submitted,(incompleted_tasks) also ($number) which is stats = 0 and pf = 1, should be 0 and her tasks submitted on time, (completed_tasks) also ($numer1) which is stats = 1 and pf = 0 should be 2. But in the table it shows hers the other way around. Meaning missed not submitted shows as 2 and completed tasks shows as 0.
. Im supposed to get a table that looks like thisā¦