Displaying Last Date of Training for a Month

In this article, we will explore how to modify an existing SQL query to display the last date of training for each month. We’ll dive into the specifics of grouping and aggregating data in SQL.

Background

The original SQL query provided is used to generate reports on training sessions by category and month. The query successfully groups data by month and calculates the total hours completed during that month. However, it doesn’t display the last date of training for each month.

To achieve this, we need to modify the query to extract the latest date from the Session_Start_Date column for each group (month).

Overview of SQL Aggregate Functions

SQL provides several aggregate functions to calculate and manipulate data at different levels. The most commonly used are:

• SUM: Returns the sum of a column.
• AVG: Returns the average value of a column.
• MAX and MIN: Return the maximum or minimum values in a column, respectively.

Using MAX to Extract Last Date

To extract the last date from a set of dates, we can use the MAX aggregate function. By grouping the data by month and applying the MAX function to the Session_Start_Date column, we effectively select the most recent date for each group.

However, in this case, using MAX directly may not be the best approach, as it doesn’t account for dates with time values (e.g., 2023-04-10 14:30). To get around this limitation, we can format the date column to remove any time components before applying the MAX function.

SQL Query Modification

Here’s an updated SQL query that incorporates these modifications:

SELECT 
    "t4"."Attendee_Name__Last__First_Middle_Suffix_" AS "Attendee_Name",
    DATE_TRUNC('MONTH', "t4"."datetime_months_Class_Sessions_Session_Start_Date") AS "Month",
    SUM("t4"."Hours") AS "Total_Hours",
    MAX(CAST(REPLACE("t4"."Session_Start_Date", ' ', '') AS TIME)) AS "Last_Session_Start_Date"
FROM 
    (SELECT 
         "t3"."Attendee_Name__Last__First_Middle_Suffix_",
         DATE_TRUNC('MONTH', "t2"."Session_Start_Date") AS "datetime_months_Class_Sessions_Session_Start_Date",
         "t0"."Hours",
         "t2"."Session_Start_Date"
     FROM 
         (SELECT 
              "_CLASSSESSIONID_PART1",
              "_CLASSSESSIONID_PART2",
              "Hours",
              "Credit Name" AS "Credit_Name"
          FROM 
              "PM_CLASSES"."Class_Sessions_Credits"
          WHERE 
              "Credit_Name" = 'FAR 139 CATEGORY 1 - AIRPORT FAMILIARIZATION') AS "t0"
     INNER JOIN 
         ((SELECT 
               "_CLASSSESSIONID_PART1",
               "_CLASSSESSIONID_PART2",
               "Session Start Date" AS "Session_Start_Date"
           FROM
               "PM_CLASSES"."Class_Sessions"
           WHERE 
               "Session_Start_Date" >= '2023-04-10 00:00:00'
               AND "Session_Start_Date" < '2024-04-04 00:00:00') AS "t2" 
     INNER JOIN 
         (SELECT 
              "_CLASSSESSIONID_PART1",
              "_CLASSSESSIONID_PART2",
              "Attendee Name (Last, First Middle Suffix)" AS "Attendee_Name__Last__First_Middle_Suffix_"
          FROM
              "PM_CLASSES"."Class_Sessions_Attendees") AS "t3" ON "t2"."_CLASSSESSIONID_PART1" = "t3"."_CLASSSESSIONID_PART1"
        AND "t2"."_CLASSSESSIONID_PART2" = "t3"."_CLASSSESSIONID_PART2"
      ) ON "t0"."_CLASSSESSIONID_PART1" = "t2"."_CLASSSESSIONID_PART1"
      AND "t0"."_CLASSSESSIONID_PART2" = "t2"."_CLASSSESSIONID_PART2"
GROUP BY 
    "t4"."Attendee_Name__Last__First_Middle_Suffix_", 
    "t4"."datetime_months_Class_Sessions_Session_Start_Date"
ORDER BY 
    "t4"."Attendee_Name__Last__First_Middle_Suffix_", 
    "t4"."datetime_months_Class_Sessions	Session_Start_Date";

In this modified query, we use DATE_TRUNC to remove any time components from the Session_Start_Date column. We then cast the resulting string back into a TIME type and apply the MAX function to extract the latest date value.

Example Use Case

Suppose you have a table named Class_Sessions_Credits with columns for Attendee_Name, Hours, and Credit_Name. You want to generate a report that shows the total hours completed by each attendee during training sessions in April 2023. Using this modified SQL query, you can easily extract the last date of training for each month.

Conclusion

In conclusion, extracting the last date from a set of dates requires careful consideration of data types and formatting. By using aggregate functions like MAX, we can effectively group and summarize data at different levels. This article demonstrated how to modify an existing SQL query to display the last date of training for each month.

Last modified on 2023-07-11