Dynamo3209
Programmer
Hi,
I am using the following code to get the results from two queries and want to display the results in a horizontal format like
Howeve I get the results in the following format
In the horizontal format I can display the total orders by clientname and location, however I am stuck at how to display the total orders for each product by clientname and location as I have mentioned earlier.
Would greatly appreciate any help.
Thanks,
Dynamo.
I am using the following code to get the results from two queries and want to display the results in a horizontal format like
Code:
ClientName Loc TotOrd Product1 Product2 Product3
Client1 Loc1 6 2 3 1
client1 Loc2 5 1 2 2
client2 Loc1 4 1 1 2
..
..
..
Code:
<!--- Do the query --->
<cfquery name ="Order_Totals" datasource="ABC">
SELECT Clients.ClientID, Clients.ClientName,
Customers.ClientID, Customers.CustID,
Clients.Location, count(Clients.ClientName) As TotalOrders,
Orders.Status, OrderDetails.ProductID,
Products.ProdName
from
Clients, `Orders`, Customers, OrderDetails, Products
Where
Clients.ClientID = Customers.ClientID And
Customers.CustID = Orders.CustID AND
OrderDetails.OrderID = Orders.OrderID AND
OrderDetails.ProductID = Products.ProductID
group by
Clients.ClientName, Clients.Location
</cfquery>
<cfquery name ="Company_Totals" datasource="ABC">
SELECT Clients.ClientID, Clients.ClientName,
Customers.ClientID, Customers.CustID,
Clients.Location, count(Clients.ClientID) As TotalOrders,
Orders.Status, OrderDetails.ProductID,
Products.ProdName
from
Clients, `Orders`, Customers, OrderDetails, Products
Where
Clients.ClientID = Customers.ClientID And
Customers.CustID = Orders.CustID AND
OrderDetails.OrderID = Orders.OrderID AND
OrderDetails.ProductID = Products.ProductID
group by
Clients.ClientName, Clients.Location, Products.ProductID
</cfquery>
<cfset prevClient = "">
<cfset prevlocation = "">
<cfoutput query="Company_Totals">
<cfif prevClient is not ClientName>
<cfif prevlocation is not Location>
#ClientName# - #Location#- #ProdName# - #TotalOrders#
<br>
</cfif>
</cfif>
</cfoutput>
<cfquery name = "Products" datasource="ABC">
SELECT count(ProductID) as Count from Products
</cfquery>
<!--- Declare the array --->
<cfset Marray=arraynew(2)>
<!--- Populate the array row by row --->
<cfloop query="Order_Totals" >
<cfset Marray[CurrentRow][1]=TotalOrders>
</cfloop>
<html>
<head></head>
<body>
<table border=1>
<tr>
<td>Customer</td>
<td>Total Orders</td>
<!--- Now, create a loop to output the array contents --->
<cfset total_records=Order_Totals.recordcount>
</tr><tr>
<cfloop index="Counter" from=1 to="#Total_Records#">
<cfoutput>
<td>#MyArray[Counter][1]# - #MyArray[Counter][2]#</td>
<td>#MyArray[Counter][4]#</td>
</cfoutput>
</tr>
</cfloop>Howeve I get the results in the following format
Code:
this result is total orders by product by client by location
---------------------------------------------------------
Client - Location - ProdName - TotalOrders
Chrysler - Santa Ana- 1 - 2
Chrysler - Santa Ana- 2 - 1
Chrysler - Santa Ana- 4 - 1
Ford - Burlingame- 4 - 1
Ford - Burlingame- 6 - 1
Ford - Burlingame- 7LD - 1
Ford - Burlingame- 12 - 1
Triad - Loss- 1 - 3
Triad - Loss- 2 - 1
Triad - Loss- 4 - 5
Triad - Loss- 5 - 1
=======================================
This result is total orders by company and location
-----------------------------------------------------
Customer TotOrd Product1 Product2 Product3 Product4 Chrysler - Santa Ana 11
Ford - Burlingame 4
Triad - Loss 19
[COLOR=red]
I have to combine the two results above into one chart like
ClientName Loc TotOrd Prod1 Prod2 Prod3 Prod4
Triad - Loss 5 2 1 1 1
Ford Loc1 2 0 0 1 1
Chrysler Loc2 11 5 2 3 1
[/color]In the horizontal format I can display the total orders by clientname and location, however I am stuck at how to display the total orders for each product by clientname and location as I have mentioned earlier.
Would greatly appreciate any help.
Thanks,
Dynamo.
