Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2003-04-23 : 07:46:18
|
| Mark writes "Hello,I am a dba that is learning as I'm going, but I guess that never ends. I am currently creating a commission reporting structure based on a SQL Server 2000 database. There are two tables that I am attempting to report on, something that I thought sounded simple enough, but now I am asking for any assistance.The first table (Table1) is the OrderDetails table that contains all of the detail line records for all Sales Orders. The second table (Table2) is a commission control table, where each row specifies the requirements that need to be met for the commission calculation to take place.For example, Table1 would consist of hundreds of thousands of records looking like this...SalesOrderSalesOrderLineStockCodeetc...Table2 would consist of several records that look like this...ContractIDStockCode1StockCode2StockCode3CommissionPctetc...StockCode1, StockCode2 and StockCode3 are all of the stock codes that need to be sold on a single sales order in order to qualify for the commission calculation. So, multiple records in Table1 need to be parsed and compared to Table2, and if all the stock codes from the control table are found on the same SalesOrder, then the commission is applicable.I'm not sure on the best to handle this, whether it's through an alteration to the table structure or the implementation of a cursor, but any help that you could lend would be greatly appreciated.Sincerely,Mark Rosmus" |
|
|
ValterBorges
Master Smack Fu Yak Hacker
1429 Posts |
Posted - 2003-04-23 : 08:21:24
|
| I think I understand your needs but just so we don't go on wild goose hunt how about giving us some sample data and sample results to compare against. |
 |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-04-23 : 08:59:34
|
| First, you might need to normalize Table2, which you can just do dynamically with a VIEW w/o altering your table:Select ContractID, CodeNumber, CASE CodeNumber WHEN 1 THEN StockCode1 WHEN 2 THEN StockCode2 WHEN 3 THEN StockCode3 END as StockCodeFROMTable2CROSS JOIN(Select 1 as CodeNumber UNION select 2 as CodeNumber UNION select 3 as CodeNumber) B(note: normally I would have a table of 3 CodeNumbers somewhere in my database if I ever need to store data in this format, and do a cross join with that table.)After that ... I am not sure how to relate Table1 to Table2 -- there appears to be no common field. For example, does ContractID live anywhere in Table1?- JeffEdited by - jsmith8858 on 04/23/2003 09:07:43 |
|
|
|
|
|
|
|
|
Comparing Records