If I understand this correctly, I think you can get what you want by doing a
UNION on all the days within your date range minus the days returned in your
original query.
Since it seems like you are using Coldfusion, it's easier to accomplish than
writing it straight SQL.
SELECT DATE, etc FROM TABLE
UNION
(CFLOOP to generate All dates within your date range)
SELECT DATE from SQL where date between your date range
and DATE NOT IN
repeat your main query here to remove duplicate dates
(SELECT DATE, etc FROM TABLE )
Hope it helps or opens up an idea!
-----Original Message-----
From: Torrent Girl [mailto:[email protected]]
Sent: Friday, March 18, 2011 9:44 AM
To: sql
Subject: Re: Date Range with Null Records
> On 3/18/2011 6:09 AM, Torrent Girl wrote:
> > I really just need to access all of the dates in the submitted date
> range. Is there a way to do this?
>
> Are there records in your database for all dates? Or do you want your
>
> SQL to make up phantom records in the result set for dates that have
> no
data?
I want SQL to make up phantom records in the result set for dates that have
no data?
I think I amy have a soluttion tho.
<cfloop from="#form.startDate#" to="#form.endDate#" index="i">
<cfoutput>#dateformat(i, "yyyy-mm-dd")#<br /></cfoutput>
</cfloop>
I really just needed to access the date in the date range to be used in
subsequent queries if the date wasn't included in the first query.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
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:3408
Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/sql/unsubscribe.cfm
