I have a query that I made that will accept a variable to give the results I
need. The only problem is, I have to specify the CONTAINER_NO and
COMPANY_NAME each time and I need to do an aggregate function in here
somewhere that will take the field "OUT" and subtract it from "IN", then
multiply it by "RATE" and group the results by Company_Name,
Container_Number.
How do I make it so that the output gives distinct records with COMPANY_NAME
and CONTAINER_NO along with the SUM of IN minus OUT * RATE and list each
grouped recordset with the product of the IN-OUT*RATE calculation.
OUTPUT LIKE THIS:
FIELDS>>> COMPANY_NAME CONTAINER_NO TOTAL RATE
----------------------------------------------------------------------------
------
VALUES>>> Joe's Company FWDE44563798764 $200.00
VALUES>>> Joe's Company DREE44563798764 $400.00
VALUES>>> Fred's Company BGFE44563798764 $300.00
VALUES>>> Jan's Company F555544563798764 $600.00
VALUES>>> Jan's Company 543DE44563798764 $800.00
VALUES>>> Bert's Company vfdDE44563798764 $1000.00
For Instance, Joe's company has many company_name records each with with
different Container_No fields. Each different Container_No record has IN and
OUT fields that pertain to that container.
I need to calculate each Container_No record's IN minus the OUT and multiply
it times RATE which is set at a dollar amount per company_name,
container_no.
I need a list that will show all of the companies, with their containers
listed with the dollar abount that is to be charged.
This is what I have so far, but it is killing me to figure out how to get
the extended functionality with the calculations and grouping.:::
<cfquery name="getCompanyData"
datasource="LOADDATA">
SELECT SUM(RATE_CALC.RATE) AS RATETOTAL
FROM RATE_CALC
WHERE COMPANY_NAME='#COMPANY_NAME#' AND CONTAINER_NO='#CONTAINER_NO#'
</cfquery>
<CFOUTPUT query="GETCOMPANYDATA">
#COMPANY_NAME#, #CONTAINER_NO#, #DollarFormat(RATETOTAL)#
</CFOUTPUT>
This gives me the Comoany Names all grouped together by container_no with a
total of the RATE field, but I need it to calculate IN-OUT*RATE and SUM all
of the individual containers and list them per record.
I hope this makes sense. I tried to be as explanatory as possible.
Thanks for any insight you can give.
MKP
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Discover CFTicket - The leading ColdFusion Help Desk and Trouble
Ticket application
http://www.houseoffusion.com/banners/view.cfm?bannerid=48
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:193229
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
