What works is to JOIN with a table that has all the dates for your range in
it, it will just display NULLs for the days that you don’t have any matching
records.
If you do not have such a table in your database, create a temp table on the
fly...
This should work in SQL Server (notice the ## for cfquery)
<cfquery datasource="#datasoruce#" name="TEST">
CREATE TABLE ##tc(calendar_date smalldatetime)
DECLARE @dummy smalldatetime
SET @dummy='1/1/2004'
WHILE @dummy < '3/1/2004'
BEGIN
INSERT ##tc VALUES (@dummy)
SET @dummy=DATEADD(day,1,@dummy)
END
SELECT ##tc.calendar_date, e.ID, e.event_name
FROM ##tc LEFT JOIN events e ON ##tc.calendar_date = e.event_date
DROP TABLE ##tc
</cfquery>
Hope that helps,
Stefan
Dr. Stefan Salzbrunn
Geschäftsführer
--------------------------------------------------------
InterLake - The Web Enabler
Friedrichshafen München Berlin New York
--------------------------------------------------------
InterLake Informationssysteme GmbH
Postfach 2269
D-88012 Friedrichshafen
Tel 0800 555 1010 (Intl.+49 89 53886562)
Fax 0800 555 1011 (Intl.+49 89 53886573)
E-Mail [EMAIL PROTECTED]
www.interlake.net www.moremx.com www.shadomx.com
--------------------------------------------------------
> -----Original Message-----
> From: C. Hatton Humphrey [mailto:[EMAIL PROTECTED]
> Sent: Thursday, January 29, 2004 11:33
> To: CF-Talk
> Subject: Calendar query?
>
> Does anyone know of a way to create a query with a range of
> dates and any
> matching records that might match in a database in one query?
> For example,
> if I'm looking at a table that has the following:
>
> ID | Date | Event
> ---+------------+-------------------
> 1 | 01/01/2004 | New Years Day
> 2 | 02/14/2004 | Valentine's Day
> 3 | 01/26/2004 | Mom's Birthday
> 4 | 02/04/2004 | Doctor's Appointment
>
> Say I wanted to show all of the days between 01/15/2004 and
> 02/15/2004 along
> with any matching events in the database. Is there a way to
> do this with
> one query? So far the only two ways that I can see doing it
> otherwise is to
> loop through the date range and perform individual queries...
> Which IMO is a
> little inefficient.
>
> Any ideas?
>
> Thanks!
> Hatton
>
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.572 / Virus Database: 362 - Release Date: 1/27/2004
>
>
>
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
