How to handle NULL in a SQL subquery?

Question

I'm pulling my hair out over such a simple thing...

I'm recording the number of days a member attends a gym club. By default, I assume the member attends every day. When they are sick, I record the dates and total number of days absent in a table (ie DateFrom, DateEnd, TotalDays). The total days absent is the difference between DateFrom and DateEnd.

Now sometimes I don't know when the member is coming back to gym. Just that they have stopped attending on a certain day. Hence the DateEnd and TotalDays are unknown. So the total number days are calculated by taking the difference between DateFrom and today's date.

Table: InactiveOnProgram
Columns: PersonId, DateFrom, DateEnd, TotalDays

Data:

1,01/01/2012,05/01/2012,5 
1,05/01/2012,08/01/2012,3
2,01/02/2012,05/02/2012,5 
2,05/02/2012,08/02/2012,3
2,20/02/2012,null,null

My below query works fine for personId=2. The total days absent is 8+2=10 days (2 days being 20/02/2012 till 22/02/2012 = today ). But for personId=1, it returns null , instead of 8 days!

sql:

(SELECT   
    case (  isnull(sum(TotalDays), 0) ) 
        when 0 then 0
        else CAST(SUM(TotalDays)  as DECIMAL(20,2))
    end 
FROM InactiveOnProgram  
)    
+   
(SELECT 
    case (  isnull( DateFrom, 0) ) 
        when null then 0
        when 0 then 0
        else CAST(datediff(day,DateFrom, getdate()) as DECIMAL(20,2))
    end  
FROM  InactiveOnProgram   
WHERE (TotalDays is null or TotalDays =0) 
AND  DateTo is null 
)

Any idea what I'm missing here?! As far as I can guess the second part of sql returns null and because of this it ignores the first part!

Any help is much appreciated.

Thanks

Damien_The_Unbeliever · Accepted Answer · 2012-02-22 13:34:02Z

2

You can write it as a single query:

declare @InactiveOnProgram table
(PersonId int, DateFrom datetime, DateEnd datetime, TotalDays int)

insert into @InactiveOnProgram (PersonId , DateFrom , DateEnd , TotalDays)
select 1,'20120101','20120105',5 union all
select 1,'20120105','20120108',3 union all
select 2,'20120201','20120205',5 union all
select 2,'20120205','20120208',3 union all
select 2,'20120220',null,null

select PersonId,SUM(COALESCE(TotalDays,DATEDIFF(day,DateFrom,CURRENT_TIMESTAMP)))
from @InactiveOnProgram group by PersonId

I'm not really happy with the storing of TotalDays, but given your data set, it seems necessary, since apparently, from 1st - 5th = 5 days, but from 5th - 8th = 3 days.

answered Feb 22, 2012 at 13:34

Damien_The_Unbeliever

241k28 gold badges358 silver badges470 bronze badges

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

1 Comment

user1225750 Over a year ago

Yes Damien Your are Unbeliever!! it worked now! your query worked fine. what a relieve!! I would like to thank ALL of your hard work and help, really appreciated all of you. Thanks Damien for this solution!

Arvo · Accepted Answer · 2012-02-22 12:41:21Z

1

Do you only guess that second part returns null or do you know that? Because as far as I can see, first part is returning something undefined.

You need to use SUM() and ISNULL() in different order, like:

select cast(sum(isnull(TotalDays, 0)) as decimal(20,2)) as totdays

And in second case you can use next:

datediff(day, isnull(DateFrom, getdate()), getdate())

This way you can eliminate null values before calculation/conversion.

answered Feb 22, 2012 at 12:41

Arvo

10.6k1 gold badge34 silver badges36 bronze badges

3 Comments

user1225750 Over a year ago

thanks, the sum of both query fails because second part is not getting anything when there is no DateFrom in the table

Arvo Over a year ago

Ah, you had + sign between the queries, I missed that. Then you need to wrap both queries into isnull((select...), 0) themselves.

user1225750 Over a year ago

Your answer was very very close to solve the problem, Thanks a lot.

freggel · Accepted Answer · 2012-02-22 13:34:43Z

0

Maybe this is your solution:

select personid, sum( closed + unclosed) 
from (
    SELECT personid
    , CAST(SUM(isnull(nullif(TotalDays,0),0)) as DECIMAL(20,2)) as closed
    , case when min(isnull(nullif(DateFrom,0),0))=0 OR (SUM(isnull(nullif(TotalDays,0),0)) >0 AND min(isnull(nullif(dateend,0),0)) >0)  then 0 else min(CAST(datediff(day,DateFrom, getdate()) as DECIMAL(20,2)))  end  as unclosed
    FROM test 
    group by personid
    --WITH ROLLUP 
) as test
group by personid
WITH ROLLUP

answered Feb 22, 2012 at 13:34

freggel

5522 gold badges6 silver badges13 bronze badges

1 Comment

user1225750 Over a year ago

It looked like very promising but when tried and it returned NULL

Bohemian · Accepted Answer · 2012-02-22 21:47:00Z

0

The problem is basically that in SQL a null term in a calculation results in a null.

Make sure null is not possible in your results.

BTW, your logic is waaaaay too complicated - simplify it

edited Feb 22, 2012 at 21:47

answered Feb 22, 2012 at 12:48

Bohemian♦

427k103 gold badges603 silver badges750 bronze badges

1 Comment

user1225750 Over a year ago

How can I avoid the null? i still want keep getting in a format that is: row1: 1, 8 row2: 2,10 (person id and total inactive days)

Collectives™ on Stack Overflow

How to handle NULL in a SQL subquery?

4 Answers 4

1 Comment

3 Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

3 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related