Correctly Dispalying Data [modified]
-
Hi Guys, I've been trying for quite sometime to accomplish this task but now I am at my wits end so I turn to you "the professionals" for assistance. My problem is as follows: I have a method which executes a stored procedure with two select queries which return two tables of data which is rans accross the several databses to view individual store performace. This data is then available to the application in a data set. The first table (table[0]) returns the transactions details (i.e. ProductID, ProductName, Cost, Sales date) while the second table(table [1]) returns a summary of each stores sales activity. What I am hoping to is display the "Details"(Table[0]) the below those records display the summary of the stores activity. However, this query is ran over several databse (one per store) thus when the dataset is returned information for all the stores are in there hence it looks something like this:
Table[0]
StoreID StoreName Product TotalSold TotalIncome
0015 Joe's Store SomeBeer 1000 $10,0000
0016 Mike's Store FoodItem 500 $2500
0017 Mary's Store Clothing 500 $2500and so on...
Table[1]
StoreID ProductName Unitcost UnitSold Total
0015 SomeBeer1 $10.00 50 500.00
0015 SomeBeer2 $8.00 50 400.00
0015 SomeBeer3 $10.00 50 500.00
0015 SomeBeer4 $10.00 50 500.00
0016 SomeBeer1 $10.00 50 500.00
0016 SomeBeer2 $8.00 50 400.00
0016 SomeBeer3 $10.00 50 500.00
0016 SomeBeer4 $10.00 50 500.00
0017 SomeBeer1 $10.00 50 500.00
0017 SomeBeer2 $8.00 50 400.00
0017 SomeBeer3 $10.00 50 500.00
0017 SomeBeer4 $10.00 50 500.00
and so on....What I need is: Store 15
StoreID ProductName Unitcost UnitSold Total
0015 SomeBeer1 $10.00 50 500.00
0015 SomeBeer2 $8.00 50 400.00
0015 SomeBeer3 $10.00 50 500.00
0015 SomeBeer4 $10.00 50 500.00StoreID StoreName Product TotalSold TotalIncome
0015 Joe's Store SomeBeer 1000 $10,0000
Store 16
StoreID ProductName Unitcost UnitSold Total
0016 SomeBeer1 $10.00 50 500.00
0016 SomeBeer2 $8.00 50 400.00
0016 SomeBeer3 $10.00 -
Hi Guys, I've been trying for quite sometime to accomplish this task but now I am at my wits end so I turn to you "the professionals" for assistance. My problem is as follows: I have a method which executes a stored procedure with two select queries which return two tables of data which is rans accross the several databses to view individual store performace. This data is then available to the application in a data set. The first table (table[0]) returns the transactions details (i.e. ProductID, ProductName, Cost, Sales date) while the second table(table [1]) returns a summary of each stores sales activity. What I am hoping to is display the "Details"(Table[0]) the below those records display the summary of the stores activity. However, this query is ran over several databse (one per store) thus when the dataset is returned information for all the stores are in there hence it looks something like this:
Table[0]
StoreID StoreName Product TotalSold TotalIncome
0015 Joe's Store SomeBeer 1000 $10,0000
0016 Mike's Store FoodItem 500 $2500
0017 Mary's Store Clothing 500 $2500and so on...
Table[1]
StoreID ProductName Unitcost UnitSold Total
0015 SomeBeer1 $10.00 50 500.00
0015 SomeBeer2 $8.00 50 400.00
0015 SomeBeer3 $10.00 50 500.00
0015 SomeBeer4 $10.00 50 500.00
0016 SomeBeer1 $10.00 50 500.00
0016 SomeBeer2 $8.00 50 400.00
0016 SomeBeer3 $10.00 50 500.00
0016 SomeBeer4 $10.00 50 500.00
0017 SomeBeer1 $10.00 50 500.00
0017 SomeBeer2 $8.00 50 400.00
0017 SomeBeer3 $10.00 50 500.00
0017 SomeBeer4 $10.00 50 500.00
and so on....What I need is: Store 15
StoreID ProductName Unitcost UnitSold Total
0015 SomeBeer1 $10.00 50 500.00
0015 SomeBeer2 $8.00 50 400.00
0015 SomeBeer3 $10.00 50 500.00
0015 SomeBeer4 $10.00 50 500.00StoreID StoreName Product TotalSold TotalIncome
0015 Joe's Store SomeBeer 1000 $10,0000
Store 16
StoreID ProductName Unitcost UnitSold Total
0016 SomeBeer1 $10.00 50 500.00
0016 SomeBeer2 $8.00 50 400.00
0016 SomeBeer3 $10.00You can return the data in 1 query by using the ROLLUP[^] command in your SELECT statement. ROLLUP is used to for producing summary information for a set of rows. In your case, you can use it to produce subtotals for each set of sales records grouped by store.
Paul Marfleet "No, his mind is not for rent To any God or government" Tom Sawyer - Rush
-
You can return the data in 1 query by using the ROLLUP[^] command in your SELECT statement. ROLLUP is used to for producing summary information for a set of rows. In your case, you can use it to produce subtotals for each set of sales records grouped by store.
Paul Marfleet "No, his mind is not for rent To any God or government" Tom Sawyer - Rush
ThanksPmar, however, I have used "Rollup" in the SP itself thus the calculation. The result is table[1]. However table[0] counts the transcation based on a different criteria. I got all the information I need from the DB, however it can not be done in single query. All I need is to display the dat from the dataset.
Skan If you knew it would not compile why didn't you tell me?!?!?!