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

,

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");

I feel like you’re overengineering this, IMO.

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.)

You have a random backtick at the end of this line.

I tried your answer but i get the error saying that there is a syntax error “near ') AS incompleted_tasks, SUM(CASE WHEN…”

Also just clarifying StartDate and EndDate are sql keywords right?

Sorry that was just a mess up in my editing in the post

I also tried getting startdate and enddate spearately just incase i was wrong about the keyword thing
like this,

$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');

And got the same error.

: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().

1 Like

i forgot the END to end the case statements. (did tell you i was spitballing :stuck_out_tongue:

SUM(CASE WHEN tasks.stats = 0 AND tasks.pf = 1 THEN 1 ELSE 0 END)

It gives the output now but its only reading one persons info :sweat_smile:. 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 :sweat_smile:

… 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.

1 Like

These are the table im drawing this stuff from

Employee table
eid | fname | lname

1 | lana | law
5 | carla | davis
6 | lana | doe

Tasks table

datepost | stats | pf | eemail

2022-08-31 | 0 | 1 | 6
2022-09-05 | 1 | 0 | 6
2022-09-05 | 1 | 0 | 6
2022-08-30 | 0 | 1 | 5
2022-09-04 | 0 | 0 | 5
2022-09-05 | 1 | 0 | 5
2022-09-01 | 0 | 0 | 5

This is the code I used to execute

$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’);

$query = $conn → query("SELECT employee.empfname, employee.emplname,

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");

  while($row = $query -> fetch_array())  
  { 
           
      $number = $row['incompleted_tasks'];   
      $number1 = $row['completed_tasks'];  
      $number2 = $row['late_tasks']; 
      
 $output .= '<tr>  
                          
                      <td>'.$row["empfname"] . $row["emplname"].'</td>
                     
                      <td>'.$number.'</td>
                      <td>'.$number1.'</td>
                      <td>'.$number2.'</td>
                      
             </tr>  
                      ';  
  }

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)

Im using xampp as the server and tcpdf to turn this thing into a downloadable report. Is there any chance that can mess things up?

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 :grin:

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

Explain?

Your logic you provided to me was

So incompleted is stats 0 and pf 1.

stats 1 and pf 0 is completed

stats 1 and pf 1 is late.

My query just replicates your logic…

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.