0

everyone. Some one who can help me to get the latest transaction details of employees based on selected transaction type. Below is the sample tables.

employees Table:

employee_id employee_name
1 John Doe
2 Jane Doe
3 Teri Dactyl
4 Allie Grater

transactions Table:

transaction_id employee_id transaction_date transaction_type remarks
1 1 2021-03-28 Add test 1
2 3 2022-09-07 Add test 2
3 2 2019-08-01 Add test 3
4 4 2023-06-05 Read test 4
5 4 2023-05-12 Add test 5
6 2 2020-02-01 Read test 6
7 3 2022-11-15 Update test 7
8 1 2020-06-14 Add test 8
9 1 2020-01-14 Update test 9
10 2 2023-12-31 Delete test 10

Then now, I want to get list of employees and their latest transaction details where transaction_type is either [Add, Update, Read]

Below is my desired output:

Transaction ID Employee Name Transaction Date Transaction Type Remarks
4 Allie Grater 2023-06-05 Read test 4
7 Teri Dactyl 2022-11-15 Update test 7
1 John Doe 2021-03-28 Add test 1

As you can see, Jane Doe is not in the list, because her last transaction type is Delete which is not in the selected transaction type [Add, Update, Read]

Below is my Code:

<?php

$transaction_types = ['Add', 'Update', 'Read'];

$output = '';

$query = "SELECT * FROM transactions 
          INNER JOIN employees ON employees.employee_id = transactions.employee_id 
          WHERE transactions.transaction_type IN ('".implode("', '", $transaction_types)."') 
          GROUP BY transactions.employee_id 
          ORDER BY DATE(transactions.transaction_date) DESC
          ";
$stmt = $connect->prepare($query);
if ($stmt->execute()) {
   if ($stmt->rowCount() > 0) {
      $output .= '<table>
                    <thead>
                       <tr>
                          <th>Transaction ID</th>
                          <th>Employee Name</th>
                          <th>Transaction Date</th>
                          <th>Transaction Type</th>
                          <th>Remarks</th>
                       </tr>
                    </thead>
                    <tbody>';
      $result = $stmt->fetchAll();
      foreach ($result as $row) {
         $output .= '<tr>
                        <td>'.$row->transaction_id.'</td>
                        <td>'.$row->employee_name.'</td>
                        <td>'.$row->transaction_date.'</td>
                        <td>'.$row->transaction_type.'</td>
                        <td>'.$row->remarks.'</td>
                     </tr>';
      }
      $output .= '</tbody></table>';
   }
}

echo $output;

?>
Mr.Jepoyyy
  • 17
  • 6
  • You code should generate the error: _"Fatal error: Uncaught Error: Undefined constant 'Add'"_, did you notice that? See: [How do I get PHP errors to display?](https://stackoverflow.com/questions/1053424/how-do-i-get-php-errors-to-display) The array should probably be: `['Add', 'Update', 'Read']`. – KIKO Software Jun 05 '23 at 14:52
  • @KIKOSoftware thanks already updated but my major concern is the sql statement on how to get my desired output. – Mr.Jepoyyy Jun 06 '23 at 02:58

1 Answers1

1

you first have to remove the users with Delete from your results set and then use the row_number() window function with CTE to select the max dates

WITH cte
AS (
    SELECT t.transaction_id AS transaction_id
        ,t.transaction_date AS transaction_date
        ,row_number() OVER (
            PARTITION BY e.employee_name ORDER BY t.transaction_date DESC
            ) AS rn
        ,e.employee_name AS employee_name
        ,t.transaction_type AS transaction_type
        ,t.remarks AS remarks
    FROM employees e
    JOIN transactions t ON t.employee_id = e.employee_id
    WHERE e.employee_name NOT IN (
            SELECT e.employee_name
            FROM employees e
            JOIN transactions t ON t.employee_id = e.employee_id
            WHERE transaction_type = 'Delete'
            )
    )
SELECT cte.transaction_id
    ,cte.transaction_date
    ,cte.employee_name
    ,cte.transaction_type
    ,cte.remarks
FROM cte
WHERE rn = 1;

demo here

zhiguang
  • 345
  • 1
  • 7