InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 20:17:18 GMT20http://www.sqlservercentral.com/Forums/Topic1386812-391-1.aspx[quote][b]lex9120 (12/1/2012)[/b][hr]I'm not sure exactly what the result would look like because they're just asking it from me. However, I'd like to see what the guru's would write based on the information. Please if possible.[/quote]So I am guessing now that since you removed the contents of your post you don't want any help?Mon, 03 Dec 2012 07:22:20 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1386812-391-1.aspxI'm not sure exactly what the result would look like because they're just asking it from me. However, I'd like to see what the guru's would write based on the information. Please if possible.Sat, 01 Dec 2012 12:57:20 GMTlex9120http://www.sqlservercentral.com/Forums/Topic1386812-391-1.aspxerrrSat, 01 Dec 2012 12:55:07 GMTlex9120http://www.sqlservercentral.com/Forums/Topic1386812-391-1.aspx[quote][b]lex9120 (11/20/2012)[/b][hr]Also, by any chance does anyone have an idea on how to perform the following?I need to create a stored procedure named PartInventory that returns a grid (temp table). The stored procedure takes one parameter named @PartID int and returns a table in the above format for that PartID. This shows the ins (receipts and returns) and outs (shipments and spoilage) for the given part. The InventoryType values are "Receipt", "Shipment", "Return", and "Spoilage".And it also needs to returns these values in this order: • PartID int • InventoryType char(8) • InventoryDate datetime • Quantity int • TotalCost decimal • UnitPrice decimal [/quote]You did a fine job posting ddl and sample data. However, it is very unclear what you want from this query. Can you explain in detail and clearly what you want here? It would also help a lot if you could pick a PartID and post what the desired results would be.Tue, 20 Nov 2012 13:24:41 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1386812-391-1.aspxerrrTue, 20 Nov 2012 09:47:34 GMTlex9120http://www.sqlservercentral.com/Forums/Topic1386812-391-1.aspxSorry, This is work related and something that someone asked from me. Be sure that I'll be posting more stuff. :( Thank you all for the reply.Tue, 20 Nov 2012 05:10:07 GMTlex9120http://www.sqlservercentral.com/Forums/Topic1386812-391-1.aspxas i'm in a bit of a lul at the moment I thought I've have a crack at it, and its one of the most comprehensive set of DDL I've seen on SSC, even if it is a little overkill.One think I did notice is that the Extended cost is set as a Decimal(18,2) and I believe money has 4 Decimal places, so you might get truncation occuring. You also need to decide what to do if there is a rounding issue on the Unit cost calculation, eg 200.0000/3 is 66.6667, rounding up will give incorrect values when multiplied back out, rounding up 66.67*3 = 200.01 and if you round down its 199.98.The Only column I cant seem to get data for is the ExtendedPrice, but this is a bare bones stab at what you might be looking for.[code="sql"]CREATE PROCEDURE InsertSalesOrderPart @OrderNumber Int ,@PartNumber Int ,@Quantity Int ,@ExtendedCost MoneyAS Insert into SalesOrderPart (OrderNumber ,PartID ,Quantity ,UnitPrice ,ExtendedPrice ,UnitCost ,ExtendedCost ) Select @OrderNumber ,p.PartID ,@Quantity ,p.Price ,0 ExtendedPrice -- WHERE DOES THIS COME FROM ,@ExtendedCost/@Quantity UnitCost ,@ExtendedCost From Part p where P.PartID=@PartNumber[/code]I would suggest runing this first and check the data you get for a specific set of paramaters.[code="sql"]DECLARE @OrderNumber Int = <replace with orderno> ,@PartNumber Int = <replace with partId> ,@Quantity Int = <replace with Quantity> ,@ExtendedCost Money =<replace with cost>Select @OrderNumber ,p.PartID ,@Quantity ,p.Price ,0 ExtendedPrice --Where does this comefrom ,@ExtendedCost/@Quantity UnitCost ,@ExtendedCostFrom Part pwhere P.PartID=@PartNumber[/code]Tue, 20 Nov 2012 04:51:03 GMTJason-299789http://www.sqlservercentral.com/Forums/Topic1386812-391-1.aspxWaste of time putting all that on here. Start by writing the sql script that would perform this task and work from there.I may be worng but this sounds fairly straightforwardGraemeTue, 20 Nov 2012 04:33:29 GMTGraeme100http://www.sqlservercentral.com/Forums/Topic1386812-391-1.aspxIt's definitely one of the most complete examples of DDL I've seen on here - thanks.But could do with seeing how far you've got with the proc so far.Why the restriction on the result not being a integer, and what should happen in the case it is?ThanksTue, 20 Nov 2012 04:28:21 GMTGazarethhttp://www.sqlservercentral.com/Forums/Topic1386812-391-1.aspxSo whats your requirement then ..Sorry i havent able to retrieve anyting from your miles- long script :-P . you only need to post the stpred proc (which is creating prob or need assistence) hereTue, 20 Nov 2012 04:25:38 GMTBhuvneshhttp://www.sqlservercentral.com/Forums/Topic1386812-391-1.aspxErrTue, 20 Nov 2012 04:17:20 GMTlex9120