troubleshooting Question

T-SQL triple join between dates returns incorrect counts

Avatar of dneill8
dneill8Flag for United States of America asked on
Microsoft SQL ServerSQL
7 Comments2 Solutions492 ViewsLast Modified:
I'm trying to perform a triple join on:

our main table: eoc_episodes_of_care
a relation table: eoc_child_service_needs_relations
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
None                                0
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.

TIA
ASKER CERTIFIED SOLUTION
Ioannis Paraskevopoulos

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 2 Answers and 7 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros