I'm trying to perform a triple join on:
our main table: eoc_episodes_of_care
a relation table: eoc_child_service_needs_re
the related table: eoc_child_service_needs
The query needs to return counts where values are null.
/* Counts by Child Service Need - zeros included */
/* the following tested fine with a zero value category */
select substring(csn.service_need,1,35) as 'Child Service Need',
isnull(count(csnr.eoc_rec_id),0) as "Count"
from eoc_child_service_needs csn
left join (eoc_child_service_needs_relations csnr
right join (select eoc_rec_id
from eoc_episodes_of_care e
where live='1') as t1
on csnr.eoc_rec_id = t1.eoc_rec_id)
on csn.child_service_needs_rec_id = csnr.child_service_needs_rec_id
group by csn.service_need, csn.sortorder
order by csn.sortorder
The above returns:
Child Service Need Count
Cognitive Limitations 55
Learning Disability 97
Medical Problems 32
Behavioral Health Difficulty 175
Other Developmental Disability 53
Data not collected 33
Warning: Null value is eliminated by an aggregate or other SET operation.
(7 row(s) affected)
What I need to do first is find out why I'm getting that warning and get a correct count on records with a NULL value.
Second, I need to be able to do the above with a date restriction on the eoc_episodes_of_care - something to the effect of:
where ( (live='1') and (eoc_start_date between '10/1/2012' and '4/1/2013' ) )
I've been attempting that inserting the date restriction after the "live='1'" line but have been getting wrong counts for that as well.