Automatically selecting date range

[chipjohns]

HI. I need to spend more time here so that when I visit I am not always asking
questions. Anyway, here is my question fo today.

I have a database that I use for customer and client information. The table
has a date field that I use to pull out the records that I need to work on for
a specific day. I can pul out records for any given date by using a form that I
can plug the date into. there are 2 things that i would like to do. the first
is to show the number of clients/records that I have for each of the next five
days on my admin home page, as follows:

Contacts for next five days:
Today - 12
10/19/05 - 11
10/20/05 - 10
10/21/05 - 15
10/22/05 - 7

I cannot determine how to use 5 seperate record sets that pull the records
out. For the first specific day I assume to use a querry like this:

<cfquery datasource="xxxx" name="qGetToday">
SELECT *
FROM ClientList
WHERE ContactDate='#Form.date#'
</cfquery>

to display use: <cfoutput>#qGetToday.RecordCount#</strong></cfoutput>

Don't know how to get the next 4 days worth ??? Or is there a simple/better
way to accomplish this?

The second thing I want to do is build a page that displays the records for
each of the 5 days..
EXAMPLE:


Contact Today
-----------------------------------------
Record 1
Record 2

Contact on 10/19/05
-----------------------------------------
Record 1
Record 2

etc..

Any help would be appriciated... Thanks, Chip :beer;:D

[Dan Bracuk]

If your database has date functions like dateadd, use them. Otherwise, use
cold fusion to create a list of dates (or something like that) and use that in
your query.

To display your data, use the group attribute of the cfoutput tag.

[mxstu]

chipjohns,

So you want to display the totals on one page and the detail on a separate page? I assume there won't always be contacts for every date?

[chipjohns]

mxstu

True , there most likely will be times when there will be days with no
records...


Dan

I'm not familiar with the group function for cfoutput. Off I go to learn more.
Thanks...

Oh yes, I am using Access

[mxstu]

Will the totals and detail records be on a single page or two separate pages?

[chipjohns]

two seperate pages!

[mxstu]

>I cannot determine how to use 5 seperate record sets that pull the records out.
You could do this in a single query, by getting the count(*) of records by
contactdate, and then grabbing the TOP 5 records, ordered by contact date
(ascending)

How you output the information depends on what you want to display. Assuming
you want to display the next (5) calendar dates, whether or not there are any
contacts, one method is to loop through the query results and display the
number of contacts if any exist. Otherwise, display zero. Note, there may be
more efficient methods, but this should demonstrate the idea.





<!--- Assumes "ContactDate" is a date/time field that contains a date ONLY (no
time) --->
<!--- Assumes "form.date" is a valid date string in mm/dd/yyyy format --->

<cfquery name="getData" datasource="#yourDSN#">
SELECT TOP 5 ContactDate, count(*) AS TotalContacts
FROM ClientList
WHERE ContactDate >= #createODBCDate(form.date)#
GROUP BY ContactDate
ORDER BY ContactDate ASC
</cfquery>

<cfset loopDate = createODBCDate(form.date)>
<cfloop from="1" to="5" index="j">
<!--- reset default --->
<cfset NumberOfContacts = 0>
<!--- find number of contacts for current date --->
<cfloop query="getData">
<cfif dateDiff("d", loopDate, contactDate) eq 0>
<cfset NumberOfContacts = TotalContacts>
<cfbreak>
</cfif>
</cfloop>
<cfoutput>Contact Date: #DateFormat(loopDate, "mm/dd/yyy")# -
#NumberOfContacts#</cfoutput><br>
<!--- increment loop date --->
<cfset loopDate = dateAdd("d", 1, loopDate)>
</cfloop>

[Dan Bracuk]

Since you are displaying stuff on two pages, you don't have to use cfoutput to
group the data. You can do it with two queries.

On the first page, your query will select date, count(date) as appointments.
When you are displaying the results, wrap the date with an anchor tag and send
that date as a url variable.

Then on the 2nd page, use that variable to select the appointments for that
date.

Originally posted by: chipjohns
mxstu

True , there most likely will be times when there will be days with no
records...


Dan

I'm not familiar with the group function for cfoutput. Off I go to learn more.
Thanks...

Oh yes, I am using Access

[chipjohns]

Thanks Dan and Stu ,

I'm going to try and understand this and see if I can make it work..
Thanks,