Acummulating a total using two files

[movertom]

I have two files that I am working with. One file is a lsit of all our customer orders that contains basic data that has been estimated. The second file I have is sorted by order number (the same key as file one) and contains multiple records for each order that is created once the actual order has been billed. And in the event that credits or additions to that same order take place, the number of records for each order number will increase in that second file.

What I want to do is to move through a recordset that is based on file one. As each record of that recordset is read I want a specific field (amount due) to be subject to logic whereby file 2 is checked to see if an order number exists, and if it does, a loop is performed accumulating a total (amount due) until such time as all records for that order number have been read. I will then <cfoutput> the data records for recordset one from file one with either the estimated or actual amount due.

On our AS400 I know the programmers can set a lower limit to get right to the beginning of the data that is sought. I would be very interested in any ColdFusion technique that is recommended that can accomplish the same thing. I am also thinking that <cfbreak> could be utilized within a loop to quickly exit the sorted list once the order number changes.

I will be creating a list of two thousand records, so getting this program to run as quickly/efficiently as possible is important. Else the program will not be used by our staff. I would be very appreciative of recommendations on the best methods to accomplish what I have outlined. Thank you

[Dan_Bracuk]

Use cfhttp to read the two files and use the name attribute to make them query objects. Then use Query of Queries to get your answers.

[NVL-Tom]

I have arrived at a partial solution based on your (very appreciated) advice that I use a query of query. The following is the last of 4 recordsets that I used to get my data. However, because in the WHERE clause I ask for matching B/L (order) numbers, I do not get a full data listing. (Yet I need the mutual primary key for each file). What I need are all of the records from Recordset2 and only those from Recordset3 that have a matching B/L#. I do not want to lose records just because the order has not yet been billed. What else do I need to implement? Thanks!

<cfquery name="Recordset4" dbtype="query">
SELECT Recordset3.RBLNO as BL, ((Recordset3.RCUTR - 100) / 100) as CUT,(Recordset2.DTAMTsum * -1) as LH, (Recordset2.DTAMTsum * ((Recordset3.RCUTR - 100) / 100)) as NET
FROM Recordset3, Recordset2
WHERE Recordset3.RBLNO = Recordset2.DTBLNO
</cfquery>

[Dan_Bracuk]

Do more than one Q of Q. Be imaginative with valuelists and union queries.

[NVL-Tom]

I did get the code to work using a Union. However, the results are not what I sought. What I seek is to use a query of queries on two queries that will pull data from each into a single recordset that is limited in its record count to the corresponding record count of the registration table. The field taken from the second query I would like as part of the resulting recordset. I do not desire a second record for the same item containing that field that is created in my successful UNION.

I recreated the scenario in MS Access and accomplished exactly what I want with the following SQL:

SELECT REGS.*, [DATA-Grouped-Qry].SumOfLH
FROM REGS LEFT JOIN [DATA-Grouped-Qry] ON REGS.RBLNO = [DATA-Grouped-Qry].BL;

Can you recommend the best method to address this in ColdFusion. Thanks so much.

[Dan_Bracuk]

Something like this

select fields, 0 as thesum
from yourqueries
where whatever
and somefield not in (a valuelist )
union
select fields, sum(something) as thesum
from yourqueries
where whatever
and somefield in (the same valuelist )
group by fields