Avatar of dneill8
dneill8
Flag 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
Microsoft SQL ServerSQL

Avatar of undefined
Last Comment
PortletPaul

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Ioannis Paraskevopoulos

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
PortletPaul

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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.
dneill8

ASKER
Apparently only one response can get 'best solution' - how elitist.  Thank you both.
PortletPaul

"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
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
dneill8

ASKER
Thanks Paul.  I'm glad I double-checked with the exchange.
PortletPaul

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