Search code examples
postgresqlpgadmin

How to get sum based on the very complex data using group by function based on latest date data only

As a level manager two, I want to get the counting of the employees based on their Division Name with respective to their assigned supervisor. Attached a screenshot of the data for more clarification of this description.

table : tbl_name
+-------+-----------+--------------+---------------+--------------+-------------------+-------------------+----------------+------------+---------------+
|    id |    name   | Level_1_MNGR |  Level_2_MNGR | Level_3_MNGR | FORM_NAME         | ASSIGN_DATE       | LAST_DATE      | ACK_STATUS | DIVISION_NAME |
+-------+-----------+--------------+---------------+--------------+-------------------+-------------------+----------------+------------+---------------+
|    1  |    EMP_1  | L1M_NAME_A   | L2M__A        | L3M__A       | Form_nov_2023     | NOV 1st, 2023     | NOV 30th, 2023 | Y          | DIV_A         |
|    2  |    EMP_2  | L1M_NAME_A   | L2M__A        | L3M__A       | Form_nov_2023     | NOV 1st, 2023     | NOV 30th, 2023 | N          | DIV_B         |
|    3  |    EMP_3  | L1M_NAME_A   | L2M__B        | L3M__A       | Form_nov_2023     | NOV 1st, 2023     | NOV 30th, 2023 | Y          | DIV_A         |
|    4  |    EMP_4  | L1M_NAME_B   | L2M__A        | L3M__A       | Form_nov_2023     | NOV 1st, 2023     | NOV 30th, 2023 | Y          | DIV_A         |
|    5  |    EMP_5  | L1M_NAME_B   | L2M__B        | L3M__A       | Form_nov_2023     | NOV 1st, 2023     | NOV 30th, 2023 | N          | DIV_B         |
|    6  |    EMP_6  | L1M_NAME_B   | L2M__A        | L3M__A       | Form_nov_2023     | NOV 1st, 2023     | NOV 30th, 2023 | N          | DIV_A         |
|    7  |    EMP_7  | L1M_NAME_B   | L2M__A        | L3M__A       | Form_nov_2023     | NOV 1st, 2023     | NOV 30th, 2023 | Y          | DIV_A         |   
|    8  |    EMP_8  | L1M_NAME_C   | L2M__B        | L3M__A       | Form_nov_2023     | NOV 1st, 2023     | NOV 30th, 2023 | Y          | DIV_B         |
|    9  |    EMP_9  | L1M_NAME_C   | L2M__B        | L3M__A       | Form_01_dec_2023  | DEC 1st, 2023     | FEB 1st, 2024  | N          | DIV_B         |
|    10 |    EMP_10 | L1M_NAME_C   | L2M__B        | L3M__A       | Form_01_dec_2023  | DEC 1st, 2023     | FEB 1st, 2024  | N          | DIV_B         |
|    11 |    EMP_11 | L1M_NAME_A   | L2M__A        | L3M__A       | Form_01_dec_2023  | DEC 1st, 2023     | FEB 1st, 2024  | Y          | DIV_A         |
|    12 |    EMP_12 | L1M_NAME_A   | L2M__A        | L3M__A       | Form_01_dec_2023  | DEC 1st, 2023     | FEB 1st, 2024  | N          | DIV_B         | 
|    13 |    EMP_13 | L1M_NAME_A   | L2M__B        | L3M__A       | Form_01_dec_2023  | DEC 1st, 2023     | FEB 1st, 2024  | N          | DIV_A         |
|    14 |    EMP_14 | L1M_NAME_B   | L2M__A        | L3M__A       | Form_01_dec_2023  | DEC 1st, 2023     | FEB 1st, 2024  | Y          | DIV_A         |
|    15 |    EMP_15 | L1M_NAME_B   | L2M__A        | L3M__A       | Form_01_jan_2024  | DEC 1st, 2023     | FEB 1st, 2024  | N          | DIV_B         |
|    16 |    EMP_16 | L1M_NAME_B   | L2M__A        | L3M__A       | Form_01_jan_2024  | JAN 1st, 2024     | FEB 1st, 2024  | N          | DIV_A         |
|    17 |    EMP_17 | L1M_NAME_B   | L2M__A        | L3M__A       | Form_15_jan_2024  | JAN 15th, 2024    | FEB 1st, 2024  | Y          | DIV_A         |
|    18 |    EMP_18 | L1M_NAME_C   | L2M__B        | L3M__A       | Form_15_jan_2024  | JAN 15th, 2024    | FEB 1st, 2024  | Y          | DIV_B         |
|    19 |    EMP_19 | L1M_NAME_C   | L2M__B        | L3M__A       | Form_15_jan_2024  | JAN 15th, 2024    | FEB 1st, 2024  | N          | DIV_C         | 
|    20 |    EMP_20 | L1M_NAME_C   | L2M__B        | L3M__A       | Form_15_jan_2024  | JAN 15th, 2024    | FEB 1st, 2024  | N          | DIV_B         |
+-------+-----------+--------------+---------------+--------------+-------------------+-------------------+-------------------+------------+------------+

Below condition I need to set to get the desired output.

  • Get records of the only latest LAST_DATE
  • Fetch a specific level manager two records. like WHRE Level 2 MNGR = 'L2M__B'
  • Count only 'N' status records from 'ACK_STATUS'
  • Counting should be based on the group of the division name.

To do so I have created the query as below, but it is not giving me the actual result.

SELECT MAX("ASSIGN_DATE") AS assignDate, 
MAX("LAST_DATE") AS lastDate, 
MAX("Level_1_MNGR") AS lvl_one_mngr_name,
COUNT("DIVISION_NAME" ILIKE '%DIV_A%') as DIV_A,
COUNT("DIVISION_NAME" ILIKE '%DIV_B%') as DIV_B,
COUNT("DIVISION_NAME" ILIKE '%DIV_C%') as DIV_C
FROM tbl_name
INNER JOIN (SELECT MAX("LAST_DATE") AS lastDate FROM tbl_name) subq 
ON "tbl_name"."LAST_DATE" = subq.lastDate
WHERE "ACK_STATUS" = "N"
AND "Level_2_MNGR" = 'L2M__B'
GROUP BY "Level_1_MNGR"

Result which I am looking is as below.

"levelTwoManager": "L2M__B" desired data
+-------+-----------+--------------+---------------+--------------+-----+
| lvl_one_mngr_name |  ASSIGN_DATE | LAST_DATE  | div_a | div_b | div_c |
+-------------------+--------------+------------+-------+-------+-------+
| L1M_NAME_C        | 2023-12-01   | 2024-02-01 |   0   |   2   |   0   |
| L1M_NAME_C        | 2024-01-15   | 2024-02-01 |   0   |   1   |   1   |
| L1M_NAME_A        | 2023-12-01   | 2024-02-01 |   1   |   0   |   0   |
+-------------------+--------------+------------+-------+-------+-------+
Solution

  • Retrieve the most recent LAST_DATE for each Level_1_MNGR, join it with your table, and then group the results by Level_1_MNGR, assign_date, and last_date after filtering out undesirable records.

    SELECT t.Level_1_MNGR,
  assign_date, 
  last_date, 
  COUNT(CASE WHEN DIVISION_NAME ILIKE '%DIV_A%' THEN 1 END) as DIV_A,
  COUNT(CASE WHEN DIVISION_NAME ILIKE '%DIV_B%' THEN 1 END) as DIV_B,
  COUNT(CASE WHEN DIVISION_NAME ILIKE '%DIV_C%' THEN 1 END) as DIV_C
FROM tbl_name t
INNER JOIN (
  SELECT Level_1_MNGR, MAX(LAST_DATE) AS lastDate
  FROM tbl_name
  GROUP BY Level_1_MNGR
) s 
ON t.LAST_DATE = s.lastDate 
   AND t.Level_1_MNGR = s.Level_1_MNGR
WHERE Level_2_MNGR = 'L2M__B' AND ACK_STATUS = 'N'
GROUP BY t.Level_1_MNGR, assign_date, last_date;

    Results :

    level_1_mngr    assign_date last_date   div_a   div_b   div_c
L1M_NAME_A      2023-12-01  2024-02-01  1       0       0
L1M_NAME_C      2023-12-01  2024-02-01  0       2       0
L1M_NAME_C      2024-01-15  2024-02-01  0       1       1

    Demo here