OK..not a total noob, but I really need help with a fairly complex (for me anyway) thing I'm trying to accomplish:
I need to create a "report" based on data in a MS SQL 2000 dataset that can be run. What I'm trying to get is a summary report of all transactions in a given date range broken down by "Department " (there are seven in my case). The data should be the Qty of transactions for each department and the $ total of all transaction in the department over the time period. I'll be doing a little math with these after the fact to get Average $ of each transaction for each department and what % of each contributes to the total of 100%, so any extra "bonus" advice for these if they make sense to incorporate would be great too!.
Not sure exactly what someone needs to be able to help here, so I'll throw out everything I can think of and let the suggestions/questions come.
There are (I think) several tables involved to get/do this. First, the "Department s" table (to get Department Names and the field within that stores the names is "Name".
Next, we need to look in the "[Transaction]" (t) table to find all transactions in a timeframe (this is where dates/times are stored) and in order to really drill into the details of the transaction, we'll add "TransactionEnt ry" (te) as an additional table. These two join on t.TransactionNu mber = te.TransactionN umber
Now in order to Join Department to TransactionEntr y, we have to use the common table "Item" (i) where i.id = te.itemid, and finally join "Department " (d) to "Item" (i) where i.Departmentid = d.id
The resulting SQL statement (if someone can help me make it happen) will be called from ColdFusion MX 7 pages on a Windows 2003 server platform, not that that probably makes a difference...
(What I currently have that's not doing it is this:)
Ideas? Thanks in advance to anyone that helps/attempts!
I need to create a "report" based on data in a MS SQL 2000 dataset that can be run. What I'm trying to get is a summary report of all transactions in a given date range broken down by "Department " (there are seven in my case). The data should be the Qty of transactions for each department and the $ total of all transaction in the department over the time period. I'll be doing a little math with these after the fact to get Average $ of each transaction for each department and what % of each contributes to the total of 100%, so any extra "bonus" advice for these if they make sense to incorporate would be great too!.
Not sure exactly what someone needs to be able to help here, so I'll throw out everything I can think of and let the suggestions/questions come.
There are (I think) several tables involved to get/do this. First, the "Department s" table (to get Department Names and the field within that stores the names is "Name".
Next, we need to look in the "[Transaction]" (t) table to find all transactions in a timeframe (this is where dates/times are stored) and in order to really drill into the details of the transaction, we'll add "TransactionEnt ry" (te) as an additional table. These two join on t.TransactionNu mber = te.TransactionN umber
Now in order to Join Department to TransactionEntr y, we have to use the common table "Item" (i) where i.id = te.itemid, and finally join "Department " (d) to "Item" (i) where i.Departmentid = d.id
The resulting SQL statement (if someone can help me make it happen) will be called from ColdFusion MX 7 pages on a Windows 2003 server platform, not that that probably makes a difference...
(What I currently have that's not doing it is this:)
Code:
SELECT d.Name AS dName, SUM(te.Quantity) AS nQuantity, te.Price AS nPrice FROM TransactionEntry te INNER JOIN [Transaction] t ON te.TransactionNumber = t.TransactionNumber LEFT JOIN Item i ON te.ItemID = i.id LEFT JOIN Department d ON i.DepartmentID = d.id WHERE te.StoreID IN ( 101 ) AND t.Time <= <cfqueryparam cfsqltype="cf_sql_timestamp" value="#CreateODBCDate(FORM.dTo)#" /> AND t.Time >= <cfqueryparam cfsqltype="cf_sql_timestamp" value="#CreateODBCDate(FORM.dFrom)#" /> GROUP BY d.Name, i.id, te.Price ORDER BY d.Name ASC