selecting too much data. If you need the total time (or time per
client), you will probably need two queries. The problem is that I have
no idea which column belongs to which table.
Get all the records you need
<CFQUERY NAME="sumtest" DATASOURCE="esitest">
SELECT tcd.ClientName, acctstatus, dailiesdate, postagesentto,
noofcopies, postagesite, confhrsused, confhrsfrom, confhrsto, confroom,
postagecost, misc, miscdesc, faxin, faxout
FROM tblclientdailies tcd, tblclients tc
where tc.clientname = tcd.clientname
and tc.acctstatus = 'A'
and dailiesdate BETWEEN #Date1# AND #Date2#
and tcd.clientname like '%marsh%'
order by dailiesdate
</CFQUERY>
Get the number of minutes:
<CFQUERY NAME="sumtest" DATASOURCE="qMinutes">
SELECT SUM(DATEDIFF(minute, confhrsfrom, confhrsto)) AS
total_no_of_minute
FROM tblclientdailies tcd, tblclients tc
where tc.clientname = tcd.clientname
and tc.acctstatus = 'A'
and dailiesdate BETWEEN #Date1# AND #Date2#
and tcd.clientname like '%marsh%'
</CFQUERY>
#qMinutes.total_no_of_minute# will give you the total for all selected
records
OR get the number of minutes per client
<CFQUERY NAME="sumtest" DATASOURCE="qMinutes">
SELECT SUM(DATEDIFF(minute, confhrsfrom, confhrsto)) AS
total_no_of_minute, t.clientname
FROM tblclientdailies tcd, tblclients tc
where tc.clientname = tcd.clientname
and tc.acctstatus = 'A'
and dailiesdate BETWEEN #Date1# AND #Date2#
and tcd.clientname like '%marsh%'
GROUP BY t.clientname
</CFQUERY>
To facilitate the output of this one, I usually convert it into a
structure:
<cfset stMinutes = StructNew()>
<cfloop query="qMinutes">
<cfset stMinutes[qMinutes.clientname] = qMinutes.total_no_of_minute>
</cfloop>
Later in the output of your first query, you can accees the minutes for
a client like:
#stMinutes[esitest.clientname]#
A few additional remarks:
1. it's not a very good idea to use a clientname as primary key (and
foregn key). If you are using access, create a PK clientID as an
autonumber.
2. use <cfqueryparam> everywhere for values in queries !!!!
3. use <cfqueryparam> everywhere
4. use <cfqueryparam> everywhere
5. ....
HTH,
Pascal
> -----Original Message-----
> From: hammerin hankster [mailto:[EMAIL PROTECTED]
> Sent: donderdag 3 juni 2004 19:49
> To: CF-Talk
> Subject: SUM DateDiff Data
>
> This is a follow-up from a previous post I made. Thanks to
> Pascal for helping me earlier!
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
