Link to home
Start Free TrialLog in
Avatar of dneill8
dneill8Flag for United States of America

asked on

T-SQL triple join between dates returns incorrect counts

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

Open in new window


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)

Open in new window


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' ) )
...

Open in new window


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
Avatar of Ioannis Paraskevopoulos
Ioannis Paraskevopoulos
Flag of Greece image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of dneill8

ASKER

I am progressing, with your help.  I corrected my code to Gianises:
count(isnull(csnr.eoc_rec_id,0)) as "Count"

I could have sworn I'd seen some documentation that said the dates in between
operated using midnight of the day delimited.  This probably is consistent
with both of your views and I got mixed up on the end date - what I should have
written is:
where ( (live='1') and (eoc_start_date between '10/1/2012' and '3/31/2013' ) )

Open in new window

because I'm shooting for a fiscal quarter.
So, I can correct it as above or using the more fully qualified '<' and '<=' operators.

This has been very informative and, since the above mistakes were repeated in many queries, I am very indebted to both of you.  Thanks for the fast answers.
Avatar of dneill8

ASKER

Apparently only one response can get 'best solution' - how elitist.  Thank you both.
"between ... and" is actually performed as ( >= lowvalue  and <= highvalue ) & there is no special use of midnight, "between ... and" does not exist purely for date comparisons either

the most rock solid method of choosing a date range is a combination of >= with < 
(i.e. only one equals sign) but you 'move' the high value up one unit so that less than is appropriate.

because I come from a society that uses dd/mm/yyyy I'm also sensitive to the ambiguous nature of date sequences here, so I honestly didn't know what dates you intended, and due to this ambiguity always recommend the yyyy-mm-dd sequence which is totally unambiguous, so:

where ( (e.live='1') and (eoc_start_date >= '2012-10-01' and eoc_start_date < '2013-04-01' ) )

if I knew which table eoc_start_date was from I would also use that alias :)

finally, if you should ever come across documentation that suggests 'between ... and' uses midnight please be very wary of that documentation :)

cheers,
Paul
Avatar of dneill8

ASKER

Thanks Paul.  I'm glad I double-checked with the exchange.
LOL. I don't want to confuse anyone, but thinking about midnight....

Midnight is 24:00:00, and 24:00:00 is also 00:00:00 (they are the exact same point in time)
DB2 for example allows one to use 24:00:00, but most dbms's do not.

What really happens is when just providing just a date (e.g. 2013-04-01) it is treated as "the beginning of 2013-04-01", but some people think between ... and will automagically understand/translate the second date to be "the end of" that day. It does not, which is easily proven, and that is why we must "push" 2013-03-31 up one day

i.e.
and eoc_start_date between between '10/1/2012' and '3/31/2013'

could be translated in words to:
(1) and eoc_start_date from the beginning of '10/1/2012' up to and including the beginning of  '3/31/2013'

in truth what we need is:

(2) and eoc_start_date from the beginning of '10/1/2012' up to but excluding the beginning of  '4/01/2013'

(2 in sql is) and eoc_start_date >= '2013-10-01' and eoc_start_date < '2013-04-01'

I'll stop now, hope I haven't confused the issue