CODEWITHSUNDEEP
mysqlsqlsql-server-2008date
Ana
Ana

Reputation: 105

Max record including Null values as a max record

I have a query that looks into all the student program enrollments. As I am getting all my results I realize that many students program enrollments have multiple enrollments to the same program however; they have different end dates and start dates. I need to get all the program enrollments and those records with a maxim end date.I have tried a couple of queries however I do not get the results needed, instead, the null values get ignored and I only get the maximum records, but the null values get ignored and they shouldn't get ignored. There are some students that are enrolled into the same program but one record has an end date while the other doesn't I need to get the one with a null value in the end date and in other cases, both records have an end date, I should get the max end date in this case I am trying to get records like my results below. I have tried the query below but it seems to ignore null values 

Table #allprgms_enroll has all my program enrollments



    select t1.*  

   -- into #MaxPrgmEndDate
    from #allprgms_enroll t1

    where t1.endDate =
      (select MAX(t2.endDate)

       from #allprgms_enroll t2
       where t1.studentID = t2.studentID and 
             t1.programName = t2.programName

       ) 
       ORDER BY T1.StudentID



    Table #allprgms_enroll
 id         FirstName   last_name    DOB               program          Start Date           endDate
 11              Aaruinq     abc        01/01/1988    Science 162    2015-11-04 00:00:00.000    NULL
 11              Aaruinq     abc        01/01/1988    Science 162    2010-10-04 00:00:00.000    2015-08-26 00:00:00.000 
 12              Amber      DEF       02/02/1995      Math 143       2012-10-04 00:00:00.000  2012-10-04 00:00:00.000 
 12              Amber      DEF       02/02/1995      Math 143       2012-10-04 00:00:00.000  2016-10-04 00:00:00.000 
 12              Amber      DEF       02/02/1995      Economics 143  2012-10-04 00:00:00.000  2016-10-04 00:00:00.000 
 15             Rose        ghj        02/22/1995    Economics 146  2012-10-04 00:00:00.000  NULL

RESULTS Needed

  id    FirstName   last_name    DOB               program          Start Date           endDate
    11      Aaruinq    abc       01/01/1988      Science 162    2015-11-04 00:00:00.000 NULL
    12      Amber      DEF       02/02/1995      Math 143       2012-10-04 00:00:00.000  2016-10-04 00:00:00.000 
    12      Amber      DEF       02/02/1995      Economics 143  2012-10-04 00:00:00.000  2016-10-04 00:00:00.000 
    15      Rose       ghj        02/22/1995     Economics 146  2012-10-04 00:00:00.000  NULL

Upvotes: 0

Views: 60

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521409

This answer uses an approach involving a union. The first half of the union targets those people/program groups having a NULL end date. The second half of the union targets those people/program groups not having a NULL end date. And in this case, we retain the record having the max end date.

This question is a bit tricky, because your requirement involves an aggregation but where you actually want to retain a record with a NULL value. As you pointed out, the aggregate functions by default willl ignore NULL values.

SELECT id, FirstName, last_name, DOB, program,`Start Date`, endDate
FROM #allprgms_enroll
WHERE endDate IS NULL
UNION ALL
SELECT t1.id, t1.FirstName, t1.last_name, t1.DOB, t1.program,
    t1.`Start Date`, t1.endDate
FROM #allprgms_enroll t1
INNER JOIN
(
    SELECT id, program, MAX(endDate) AS maxEndDate
    FROM #allprgms_enroll
    GROUP BY id, program
    HAVING SUM(CASE WHEN endDate IS NULL THEN 1 ELSE 0 END) = 0
) t2
    ON t1.id = t2.id AND
       t1.program = t2.program AND
       t1.endDate = t2.maxEndDate
ORDER BY
    id, program;

Demo

Upvotes: 1

Related Questions