Query Question Please HELP

[flooker]

I have a Table with huge volume of records . table structuer is some thing like
this

country : region : destrict : destination: Activity date : Trailor : T type

what i want eventaully is to create a Text file using perl automatically which
will contain info based on Acticity Date : Destination: Trailor
So for all the trailors which Went to Houston destination on todays date will
make One file and then All the trailor which Went to Dalls Destination on lets
say yesterday will make another File .

Writing Data to File is next Part but what will be the shape of Query ?

[philh]

You could use the GROUP attribute of the CFOUTPUT tag to slice and dice the
query results.

I assume that you need one file per destination. As a bare framework of what
to do:

(Order your CFQUERY by destination, then date descending if you want to show
latest activity first)
<cfquery name="myquery" datasource="sql">

</cfquery>
<CFOUTPUT query="myquery" GROUP="destination">
<Cfset outputtext = "">
<cfset outputtext=myquery.columnlist>
<cfset outputtext = outputtext & chr(13)& chr(10)>
<CFOUTPUT>
<Cfloop index="filefield" list="#myquery.columnlist#">
<cfset outputtext= outputtext&#evaluate(filefield)#&",">
</cfloop>

<Cfset outputtext=outputtext&chr(13)& chr(10)>
</CFOUTPUT>
<CFFILE action="write" file="\\mymachine\c$\#destination#.txt"
output=#outputtext#>
</CFOUTPUT>

HTH,

[flooker]

Phil sorry for very late reply.

Here is the Snippet of Data :

ID--Destinatio -- Sort -- Date -- Bags/Trailor --
smalls/trailor -- Trailor Num -- Total_bags_per_trailor -- Zip_code
1 --Dallas -- M -- 20060202 -- 10 --
21 -- 1111 -- 34
-- 4023
2--Dallas -- M -- 20060202 -- 10 --
54 -- 1111 -- 34
-- 4023
3--Dallas -- M -- 20060202 -- 14 --
21 -- AAAA -- 24
-- 1111
4--Dallas -- M -- 20040202 -- 22 --
21 -- 1111 -- 34
-- 4023
5--Houst -- M -- 20030202 -- 9 --
31 -- KKKK -- 11
-- 3456
6--Houst -- M -- 20060202 -- 10 -- 21
-- 4567 -- 34
-- 6755
7--Houst -- M -- 20060202 -- 10 -- 21
-- 4567 -- 26
-- 1232
8--Moon -- P -- 20040105 -- 99 -- 22
-- 4567 -- 26
-- 1232

so ID 1 and 2 will create one Text File based on Same destination - Sort -
Date - Trailor Num
ID 3 will create another text file trailor number is different in it from ID 1
& 2
ID 4 will now create another file because Date is different

So bottom line is for one records having same destination sort date trailor
number there will be one file . and if any one of the criteria is changed there
will be another file .

Help will be really appreciated

[philh]

In that case, keep track of the values of your critical data elements, and break out of the loop/reset the values when one of them changes.

[philh]

Besides, your reply outlines business rules which differ from your original
request:

"all the trailors which Went to Houston destination on todays date will make
One file and then All the trailor which Went to Dallas Destination on lets say
yesterday will make another File ."

There's nothing in there about a new file for a different trailer number.

That said, I think that unequivocal definition of business rules should
precede any coding whatsoever. If you can't fully and cogently explain what it
is that you need, it's impossible to make a system that fulfills the
requirements.