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 |
|
sql_dan
Starting Member
43 Posts |
 Posted - 2010-05-11 : 10:38:32
|
Greetings,I am in a pickle through my own design which I am hoping has a simple yet elegant solution I am not aware of due to lack of knowledge of SQL!I have created a database to record all of the individual items used in a procedure in a bid to cost them and get both a price per procedure and over time get average costs and analyse the items used.To do so I have created 3 tables:1. ProcedureConsCode - To store the stock codes of the items used (Upto 60 items in table StkCode01 - StkCode60)2. ProcedureConsQty - To store the quantity of the items used (Upto 60 items in table StkQty01 - StkQty60)3. ProcedureConsCost - To store the cost of the item used (Qty * Stock Item Cost in store table) (Upto 60 items in table StkCost01 - StkCost60)When I input the items I have a number of stored procedures to update all of these tables at the point of input which all works great as well as assigning a unique number identifying the procedure.The problem I have is that I am trying to pull up a report in SSRS and to list the individual items used by the procedure.As the items are stored in columns I can write a script to pull out each of the items in StkCode1,StkCode2,StkCode3 etc but they are separate queries and I need them for the query in SSRS to be in the same query.Here is the code for a single item:SELECT c.stock_code,c.Stock_Name,pcq.StkQty01,(pcq.StkQty01 * c.Stock_CostPrice) AS CostFROM ProcedureConsCode pccINNER JOIN Consumerables c ON pcc.Stkcode01 = c.Stock_Code AND pcc.Status = @aopINNER JOIN ProcedureConsQty pcq ON pcc.Procedure_ID = pcq.Procedure_ID AND pcq.Status = @aopWHERE pcc.Procedure_ID = @pid AND pcc.Status = @aop AND pcq.StkQty01 <> '0'The code for the variables just isolate the specific procedure and status of the procedure.So to get the results I require I would need to duplicate the code above 60 times!Is there a way to get this information in a single query or would I be better doing this another way?Any help would be great!ThanksDan  If you cant sleep at night, its not the coffee its the bunk! |
|
|
sql_dan
Starting Member
43 Posts |
Posted - 2010-05-13 : 08:53:50
|
Hi Guys, After a couple of days going round a few issues I have solved this!!!  I created a temp table to dump everything in the format I required and queried the temp table! Sorted!Thanks for lookingDanIf you cant sleep at night, its not the coffee its the bunk! |
 |
|
|
|
|
|
Cant think of a good title for my problem![SOLVED]