The question you have to ask yourself is "what is a record?"
Right now, the query is basing a record on the fact that the WHERE condition is
true
which would be there is a reportdate between date1 and date2 for
traxuserid=xxxxx
If you are not pulling date 10/10/2010, then chances are the traxuserid does not
match xxxxx exactly OR the data that is in reportdate is not a datetype data, so
the date comparison isn't working unless you convert the data to date format.
George
> To: sql@houseoffusion.com
> Subject: Re: Date Range with Null Records
> Date: Fri, 18 Mar 2011 08:35:48 -0400
> From: moniqueb...@gmail.com
>
>
> >> I want to bring back a record for the date that does not have any records.
> >
> >If you re-read that, it sounds like you want to magically invent data
> >out of thin air? :P
> >
> >
> >Do you mean that, in addition to currently returned data, you also
> >want to include records that have a null/unknown date value? (So
> >they're not in the specified range, but they're not outside either.)
> >
> >If so, you just need to do an OR inside parentheses:
> >
> >SELECT <columns>
> >FROM dailyActivity
> >WHERE traxUserID = XXXXXX
> >AND (
> > reportDate BETWEEN '2010-10-09' AND '2010-10-12'
> > OR
> > reportDate IS NULL
> > )
>
> Well there are no records for 10/10/2010 but I need to include that date in
> the results with no records. Because it is not returned by the query, it is
> not displayed in the output. Since it is within the range, can I somehow
> include it in the query results?
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive: http://www.houseoffusion.com/groups/sql/message.cfm/messageid:3404
Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/sql/unsubscribe.cfm
