November 20, 2012 at 4:17 am
Err
November 20, 2012 at 4:25 am
So whats your requirement then ..Sorry i havent able to retrieve anyting from your miles- long script 😛 . you only need to post the stpred proc (which is creating prob or need assistence) here
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
November 20, 2012 at 4:28 am
It'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?
Thanks
November 20, 2012 at 4:33 am
Waste 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 straightforward
Graeme
November 20, 2012 at 4:51 am
as 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.
CREATE PROCEDURE InsertSalesOrderPart
@OrderNumber Int
,@PartNumber Int
,@Quantity Int
,@ExtendedCost Money
AS
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
I would suggest runing this first and check the data you get for a specific set of paramaters.
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
,@ExtendedCost
From Part p
where
P.PartID=@PartNumber
_________________________________________________________________________
SSC Guide to Posting and Best Practices
November 20, 2012 at 5:10 am
Sorry,
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.
November 20, 2012 at 9:47 am
errr
November 20, 2012 at 1:24 pm
lex9120 (11/20/2012)
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
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.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 1, 2012 at 12:55 pm
errr
December 1, 2012 at 12:57 pm
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.
December 3, 2012 at 7:22 am
lex9120 (12/1/2012)
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.
So I am guessing now that since you removed the contents of your post you don't want any help?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply