Steve:
The query simply pulls the records. I then compare the date of the record against the
previous record read. I am doing this within the CFOUTPUT. I'd love to do it within
another query, as in querying the first query, since I do not know what's up until I
get the next record. I've done this a million times, just not within this environment.
I am looking for records 1,2 4,5, and 6 in this case. I could create a table and
populate it with the results of this query and then query that table for the results
but just thought of that. I thought I could query the query, checking the number of
entries for a single customer before I got to the CFOUTPUT. Instead I chose the array
method. I still have the same dilemma though.
Here's some code. Thanks for your help.
John.
<! --Get data by data range
<CFQUERY name="GetData" datasource="abc" dbtype="ODBC" >
SELECT TOP 100 PERCENT Name AS CUST, Who, ADDRESS, CITY, TestType, Type, Date1, Date2,
Date3, Date_Date1, Date_Date2, Date_Date3
FROM source.TableA
WHERE Type = 'ABC' and (date_DATE3 >= CONVERT(DATETIME,'#StartAtDate# 00:00:00', 102))
AND (date_DATE3 <= CONVERT(DATETIME, '#EndAtDate# 00:00:00',102)) AND (ADDRESS =44 OR
ADDRESS=57 )
order by CUST,date_Date3
</cfquery>
<cfset counter=1 >
<cfset i=0 >
<cfset CUST_id =0>
<!- Create a 2D array to store the data -->
<cfset thisarray = ArrayNew(2) >
<cfset thiscount = ArrayNew(1) >
<cfloop Query="GetData">
<cfif old_CUST NEQ CUST >
<cfset old_CUST = CUST >
<cfset Previous_Date_Date3 = GetData.Date_Date3 >
</cfif>
<cfset s1=#DateFormat(Previous_Date_Date3, "mm/dd/yyyy")# >
<cfset s2=#DateFormat(GetData.Date_Date3, "mm/dd/yyyy")# >
<cfif Previous_Date_Date3 NEQ '' and Date_Date3 NEQ '' >
<cfset Difference=datediff("d",s1,s2)>
</cfif>
<cfif #Difference# LTE #Variance# >
<cfset thisarray[CurrentRow][1] = CurrentRow >
<cfset thisarray[CurrentRow][2] = GetData.CUST[CurrentRow] >
<cfset thisarray[CurrentRow][3] = GetData.ADDRESS[CurrentRow] >
<cfset thisarray[CurrentRow][4] = GetData.CITY[CurrentRow] >
<cfset thisarray[CurrentRow][5] = GetData.TestType[CurrentRow] >
<cfset thisarray[CurrentRow][6] = GetData.Date1[CurrentRow] >
<cfset thisarray[CurrentRow][7] = GetData.Date2[CurrentRow] >
<cfset thisarray[CurrentRow][8] = GetData.Date3[CurrentRow] >
<cfset thisarray[CurrentRow][9] = GetData.Who[CurrentRow] >
<cfset thisarray[CurrentRow][10] = #Difference# >
<cfset Previous_Date_Date3 = Date_Date3 >
</cfif>
</cfloop>
<CFOUTPUT>
<center>
<b><font face="Arial, Helvetica, sans-serif" size="5"
color="##0000FF">Heading</font></b>
</center>
</CFOUTPUT>
<cfoutput>
Output here - headings and then loop through the array
>>> [EMAIL PROTECTED] 07/16/02 03:09PM >>>
You should post your query. This sounds like it can be done in your query.
Re your example.
Row Customer Date Days O/S
1 ABC Jan 1 0
2 ABC Jan 8 7
3 DEF Jan 1 0
4 GHI Jan 2 0
5 GHI Jan 3 1
6 GHI Jan 17 14
Do you need to display only row 1 and 4 or rows 1,2,4,5,6?
if the first you could do a select that counts the customer occurence and
if GT 1 display
if the second you could do a sum on days o/s grouped by customer and if its
days o/s = 0 reject the record.
Anyway I'm not 100% sure of the goal
-steve
"John Spencer"
<John.Spencer@ene. To: [EMAIL PROTECTED]
gov.on.ca> cc:
Sent by: Subject: [CFTALKTor] Query within a
query?
owner-cfug@cfugtor
onto.org
07/16/02 02:15 PM
Please respond to
CFTALK
Hello Folks:
I am new to CF so please bear with me if you will.
I am trying to determine how I can check on the number of occurences of a
certain 'identifier', say 'customer code', that are contained in a query
that I run. If there is only one occurrence then I do not wish to output
that code. I do not know if there will only be one though until the 'code'
changes, since I am doing some calculations on data contained in the record
and then deciding if I would like to output it or not.
At present I have stored everything in an array. I would now like to buzz
through this array and build another, storing the count for each 'code'
used.
Customer Date Days O/S
ABC Jan 1 0
ABC Jan 8 7
DEF Jan 1 0
GHI Jan 2 0
GHI Jan 3 1
GHI Jan 17 14
In this example, I need to show the '0' records for ABC and GHI but not
the one for DEF so a simple 'if days = 0' will not work. I need to know how
many records have been processed for each customer.
Does this make any sense? I can then read the 2nd array and if the record
count.
Any ideas?
Thanks for your time.
John.
-
You are subscribed to the CFUGToronto CFTALK ListSRV.
This message has been posted by: "John Spencer"
<[EMAIL PROTECTED]>
To Unsubscribe, Please Visit and Login to http://www.CFUGToronto.org/
Manager: Kevin Towes ([EMAIL PROTECTED])
http://www.CFUGToronto.org/
This System has been donated by Infopreneur, Inc.
(http://www.infopreneur.net)
-
You are subscribed to the CFUGToronto CFTALK ListSRV.
This message has been posted by: [EMAIL PROTECTED]
To Unsubscribe, Please Visit and Login to http://www.CFUGToronto.org/
Manager: Kevin Towes ([EMAIL PROTECTED]) http://www.CFUGToronto.org/
This System has been donated by Infopreneur, Inc.
(http://www.infopreneur.net)
-
You are subscribed to the CFUGToronto CFTALK ListSRV.
This message has been posted by: "John Spencer" <[EMAIL PROTECTED]>
To Unsubscribe, Please Visit and Login to http://www.CFUGToronto.org/
Manager: Kevin Towes ([EMAIL PROTECTED]) http://www.CFUGToronto.org/
This System has been donated by Infopreneur, Inc.
(http://www.infopreneur.net)
