How to select a MAX record based on multiple results from a unique ID in MySQL


Problem :

I run a report in mysql which returns all active members and their associated plan selections. If a member is terminated, this is displayed on the report as well. The issue I am facing, however is when a member simply decides to 'change' plans. Our system effective 'terminates' the original plan and starts fresh with the new one while keeping the original enrollment information for the member.

Sample report data may look like the following:

MemberID | Last    | First | Plan Code |  Original Effective | Change Date   |   Term Date
--------------------------------------------------------------------------------------------
12345    | Smith   | John  |     A     |   05-01-2011        |               |   06-01-2011
12345    | Smith   | John  |     B     |   06-01-2011        |               |      

In the example above, a member had plan code A from May 1, 2011 to June 1, 2011. On June 1, 2011, the member changed his coverage to Plan B (and is still active since no term or change data).

Ultimately, I need my report to generate the following instead of the 2 line items above:

MemberID | Last    | First | Plan Code |  Original Effective | Change Date   |   Term Date
--------------------------------------------------------------------------------------------
12345    | Smith   | John  |     B     |   05-01-2011        |  06-01-2011   | 

which shows his original plan effective date, the date of the change, and leaves the termination field blank.

I know I can get into a single row by using Group By the Member ID and I can even add the change date into the appropriate field with a (IF COUNT() > 1) statement but I am not able to figure out how to show the correct plan code (C) or how to leave the term date blank keeping the original effective date.

Any ideas?



Solution :

Although I hate columns with embedded spaces and having to tick them, this should do it for you. The PreQuery will detect how many policy entries there are, preserve the first and last too, grouped by member. Once THAT is done, it can re-join the the plan enrollment on the same member but matching ONLY for the last "Original Effective" date for the person... That will give you the correct Plan Code. Additionally, if the person was terminated, it's date would be filled in for you. If still employed, it will be blank on that record.

select STRAIGHT_JOIN
      pe2.MemberID,
      pe2.Last,
      pe2.First,
      pe2.`Plan Code`
      PreQuery.PlanStarted `Original Effective`,
      case when PreQuery.PlanEntries > 1 then PreQuery.LastChange end `Change Date`,
      pe2.`Term Date`
   from
      ( select 
              pe.MemberID,
              count(*) as PlanEntries,
              min( `pe`.`Original Effective` ) PlanStarted,
              max( `pe`.`Original Effective`) LastChange
           from
              PlanEnrollment pe
           group by
              pe.MemberID ) PreQuery

     join PlanEnrollment pe2
        on PreQuery.MemberID = pe2.MemberID
        AND PreQuery.LastChange = pe2.`Original Effective`

Mysql Tutorials

Mysql Howto..

How can I delete values from input fields after submitting

How can I get emacs sql-mode to use the mysql config file (.my.cnf)?

How to query huge MySQL databases?

PHP How to save data from array to mysql using laravel 5

How to store php blog post in mysql database with images and other html text format? [closed]

How to get Data Between Two Dates in mysql?

How to populate check boxes from Mysql database?

How to convert .db file to .sql file

Inserting substring in string at specific index counting from end. How to in mysql?

How storing database info outside webroot of php applications will improve security

how to retrieve image from mysql database using java servlet and show it in HTML img tag? [closed]

How to order by maximum of two column which can be null in MySQL?

How to use LIKE for mysql search with JOIN and ORDER BY the count of most rows/votes in the vote table?

How to reduce the execution time in stored procedure using mysql

How to use the LIKE statement correctly in a MySql SELECT statement?

Two date columns, one date. How can I get the data?

How to update data in database without removing the old data?

How to compare 3 tables and merge their columns in mysql

How to insert reactive input values from a shiny app into a MySQL database?

How to use prepared statements in Zend Framework

How to get in between dates from table using MYSQL

How to use syntax of mysql such as ifnull(max(Id),0) in Hibernate

How to update Sphinx Realtime Index with field = old_value + new_value?

AJAX / jQuery / PHP / MySQL - how to update a database

mysql - How to determine the length of a field which stores a mutable string?

Where (how) to store categories (sort of) tree in my database?

How to execute MySQL query with range of years in one field

How to select columns with same set of values in mysql?

How to optimize query if table contain 10000 entries using MySQL?

How can I form MySQL query algorithm to add columns to another table?