Dates return null and not null values mysql

Question

So i have a temporary table that I need to populate with the records that have to deal with enrollment history

I work with a dataset that looks like this:

sccphidsid    enroll_date disenroll_date    status
abc123x        2009-01-01  2010-31-12           0
abc123x        2011-01-01  null                 0
abc123x        2011-03-01  2012-01-01           0

So I need it to return all records with the same sccphidsid and return them including the null and not null values.

The conditions it has to meet are

Disenroll dates are not null
Disenroll dates are null and
records have multiple disenroll_dates

Current Solution:

select *, count(disenroll_date is null)
              from enrollment _test
              where Status = 0
              group by Sub_Client_Cd, Policy_Holder_ID, Suffix_ID
              having count(disenroll_date is null) > 1;

However this is returning too many records, is there a way to isolate this query down, maybe simpler, that can prevent this from returning too many records by constraints or parameters?

Thanks all

The conditions "Disenroll dates are not null" and "Disenroll dates are null" do not seem to be valid.. — user166390
– user166390, Commented Mar 9, 2012 at 20:32
I was referring to disenroll date is null and a case where the disenroll_date is not null with a valid date...not "not null". — Hituptony
– Hituptony, Commented Mar 14, 2012 at 19:31

Joe Stefanelli · Accepted Answer · 2012-03-09 20:36:39Z

1

SELECT Sub_Client_Cd, Policy_Holder_ID, Suffix_ID,
       SUM(CASE WHEN disenroll_date IS NULL THEN 1 ELSE 0 END) AS NullDateCount,
       SUM(CASE WHEN disenroll_date IS NOT NULL THEN 1 ELSE 0 END) AS NotNullDateCount
    FROM enrollment_test
    WHERE Status = 0
    GROUP BY Sub_Client_Cd, Policy_Holder_ID, Suffix_ID
    HAVING COUNT(*) > 1;

answered Mar 9, 2012 at 20:36

Joe Stefanelli

136k21 gold badges243 silver badges241 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

HelpNeeder · Accepted Answer · 2012-09-06 04:23:06Z

1

Actually this took care of it... Thanks anyways.

 select *, count(disenroll_date is null) from enrollment_test 
 where  Status = 0 
 and disenroll_date is null 
 group by Sub_Client_Cd, Policy_Holder_ID, Suffix_ID              
 having count(disenroll_date is null) > 1;

edited Sep 6, 2012 at 4:23

HelpNeeder

6,52026 gold badges95 silver badges160 bronze badges

answered Mar 14, 2012 at 19:29

Hituptony

2,8703 gold badges26 silver badges44 bronze badges

Collectives™ on Stack Overflow

Dates return null and not null values mysql

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related