Fixed Point Arithmetic For Price Calculations
Feb 22, 2007
I've got a price in euro as a string, which I can easily cast to a numeric SSIS data type e.g. R4, R8, DECIMAL, NUMERIC. And I've got the dollar/euro exchange rate stored in an SSIS variable of type DOUBLE, set to 1.28 for testing purposes. I want to multiply the two values and return the (dollar) result, rounded (not truncated) to 2 decimal places, as a string.
Here are some experiments I did in an SSIS expression editor:
(DT_WSTR, 10) (1.28 * 31.10) evaluates to "39.8080"
(DT_WSTR, 10) (1.28 * (DT_R8) "31.10") evaluates to "39.808"
(DT_WSTR, 10) (1.28 * (DT_DECIMAL, 0) "31.10") evaluates to "39.68"
(DT_WSTR, 10) (1.28 * (DT_DECIMAL, 1) "31.10") evaluates to "39.808"
(DT_WSTR, 10) (1.28 * (DT_DECIMAL, 2) "31.10") evaluates to "39.8080"
(DT_WSTR, 10) (1.28 * (DT_DECIMAL, 3) "31.10") evaluates to "39.80800"
Of course, what I really want is "39.81", so I went on:
(DT_WSTR, 10) ((DT_DECIMAL, 0) (1.28 * (DT_R8) "31.10")) evaluates to "39"
(DT_WSTR, 10) ((DT_DECIMAL, 1) (1.28 * (DT_R8) "31.10")) evaluates to "39.8"
This looks promising! But:
(DT_WSTR, 10) ((DT_DECIMAL, 2) (1.28 * (DT_R8) "31.10")) evaluates to "39.8"
(DT_WSTR, 10) ((DT_DECIMAL, 3) (1.28 * (DT_R8) "31.10")) evaluates to "39.808"
Argh... How does one get a floating point value rounded to 2 decimal places???
View 6 Replies
ADVERTISEMENT
Dec 4, 2013
I am trying to understand why SQL Server gives me significantly lower precision than many of the other sources that have tried when using the POWER function. My environment is 2008 R2 SP2 (10.50.4000.0 X64, Standard edition) on Windows 2008 X64 SP1
DECLARE @x FLOAT(53) = 1.0004;
DECLARE @y FLOAT(53) = 1.0/12.0;
SELECT POWER(@x,@y)-1; -- Answer: 3.33270904724348E-05
GO
DECLARE @x FLOAT(24) = 1.0004;
DECLARE @y FLOAT(24) = 1.0/12.0;
[URL] ....
Answer: 3.33272237835747E-05
I also tried using Windows Calculator.Answer:3.3327223783495255846580902358195e-5
And an online "high-precision" calculator from [URL] ...
Answer: 3.332722378349525584658E-5
The best SQL results compare only to the fourth digit with other results. Everything else agrees to the 10th or 12th digit.
Is the precision of arithmetic calculations on SQL Server that bad, or am I doing something wrong here? Is there another more precise alternative?
I did the following. The results speak for themselves
DECLARE @x FLOAT(53) = 1.0004;
DECLARE @y FLOAT(53) = 1.0/12.0;
DECLARE @z FLOAT(53) = POWER(@x,@y)-1; -- Answer: 3.33270904724348E-05
SELECT POWER((1+@z),12); -- 1.00039999839968
-- using results from other sources (c#, windows calc, casio.com)
SELECT POWER((1+3.33272237835747E-05),12) -- 1.0004
SELECT POWER((1+3.3327223783495255846580902358195e-5),12)-- 1.0004
SELECT POWER((1+3.332722378349525584658E-5),12) -- 1.0004
It is not important what I am trying to do, but in case it will work, I am trying to calculate the monthly return for a 90-day T-bill given the compounded annual return. x is the annualized return.
View 7 Replies
View Related
Nov 9, 2001
Hello all,
I can't see any reason for this error, not having a high level understanding of maths I thought I'd post it and hope someone could share some light on it.
I yesterday got called by a client who said that a payment for £15 + VAT was being passed to their payment gateway as 17.62 when it should be 17.63. The VAT calculation is performed in a SQL Server 2000 stored procedure. In the end I tracked it down and it wasn't a propblem with my calculation.
The price was coming out as 17.63 fine. The stored procedure then had to return this price in pence (17.63 * 100 = 17.63). When I put in a print statement with this calculation it was correct but when I output the variable that the result was assigned to it was coming out as 1762.
The variable that the result was being put into was of real datatype.
I then wrote a udf to test this. Here is the function:
CREATE FUNCTION dbo.POUNDS_TO_PENCE
(
@POUNDVALUE real
)
RETURNS INTEGER
AS
BEGIN
RETURN @POUNDVALUE * 100
END
As you can see nothing very special.
If you run this runction and pass in 17.63 it will return 1762!!!
The bit I don't get is if I change the @POUNDSVALUE intput variable to type float it returns the correct amount.
I've also found that the same problem occurs when passing in £30 + VAT (35.25) + 1pence. So, 35.26 comes out as 3525 instead of 3526. This is the case if you keep doubling the number (and adding a few pence here and there).
Does anyone know why this is or is it a bug in the processor?
The SQL books online say the following about the float and real data types:
--------------------------------------------------------
float and real (T-SQL)
Approximate number data types for use with floating point numeric data. Floating point data is approximate; not all values in the data type range can be precisely represented.
Syntax
float[(n)]
Is a floating point number data from - 1.79E + 308 through 1.79E + 308. n is the number of bits used to store the mantissa of the float number in scientific notation and thus dictates the precision and storage size. n must be a value from 1 through 53.
n is Precision Storage size
1-24 7 digits 4 bytes
25-53 15 digits 8 bytes
The Microsoft® SQL Server™ float[(n)] data type conforms to the SQL-92 standard for all values of n from 1 to 53. The synonym for double precision is float(53).
real
Floating point number data from –3.40E + 38 through 3.40E + 38. Storage size is 4 bytes. In SQL Server, the synonym for real is float(24).
--------------------------------------------------------
Apart from the fact that it says 'Approximate number data types' I can't see any difference between the data type apart from the ranges.
Anyone any ideas?
Thanks
Tom Holder
View 2 Replies
View Related
Jul 20, 2005
Hi,I would like to know, if I need to do some floting point operations(mainly multiplication and division) on each roll of a table, should Iread the data out from the DB and do the calculation with a programminglanguage, say C#, or should I just use sql to do it on the sql server.An obvious advantage of doing it in the sql server is that you dontneed to transfer the data between the sql server and he applicationserver.But I am not sure if there are any other factors that will overridethis advantage: like the performance of doing lots of floting pointoperations in sql server.How is the performance of doing floting point operations in sql servercomparing to C# or other languages?Also are there any other factors that should be considered for thiscase andare there any other advantages to do this is sql server or in c#?ThanksBenny*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies
View Related
Jul 31, 2014
I'm reviewing the CAST function using Microsoft SQL server. I generally understand the functionality, but my confusion lies in the results of a particular script which casts a DateTime value to a fixed-point number.
It is as follows:
DECLARE @From DATETIME
DECLARE @To NUMERIC(10,5)
SET @From = '2009-10-11T11:00:00'
SET @To = cast(@From AS NUMERIC(10,5))
PRINT @To
This results in: 40095.45833
I am completely lost as to how 40095.45833 = 2009-10-11T11:00:00. I just do not understand the how that fixed point number equates to that source DateTime data.
View 2 Replies
View Related
Dec 12, 2014
I have to add the unit price on the order acknowledgement for products on our shelf.
Each product has different price breaks stored in a table called MaterialUnitCost.
I don't know how to pull the correct price based on the order quantity.
Let's say the customer orders 200 pieces,
I sell 1 pcs @ $20
50 pcs @$15
200 pcs @$10.
My order acknowledgement should pull a unit price of $10, but it pulls $20 instead, because in my select statement I have
select materialunitcost.unitcost.
I thought I should do a loop or use the row_number function, but I am new to SQL, and I never used any of these two.
View 1 Replies
View Related
Jun 19, 2000
I am developing basket and need a simple way to get price x quantity + total price.
I get back a recordset which has a varying number of items, each item has a price and quantity. Using
SELECT ((productprice)*(productquantity)) AS subtotal, product name, productid
I don't know how to add all subtotals to get a total, I have tried putting in SUM but but cant get it to work.
Any help would be well appreciated.
View 1 Replies
View Related
Oct 5, 2015
I have a table with 3 columns (ID Int , Name Varchar(25), Course Varchar(20))
My source data looks like below
ID     Name       Course
1Â Â Â Â Â Â Â AÂ Â Â Â Â Â Â Â Â Â Â Â Â Â Java
1Â Â Â Â Â Â Â AÂ Â Â Â Â Â Â Â Â Â Â Â Â Â C++
2Â Â Â Â Â Â Â BÂ Â Â Â Â Â Â Â Â Â Â Â Â Â Â Java
2Â Â Â Â Â Â Â BÂ Â Â Â Â Â Â Â Â Â Â Â Â Â SQL Server
2Â Â Â Â Â Â Â BÂ Â Â Â Â Â Â Â Â Â Â Â Â Â Â .Net
2Â Â Â Â Â Â Â BÂ Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â SAP
3Â Â Â Â Â Â Â CÂ Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Oracle
My Output should look like below...
ID     Name      Course(1)    Course(2)        Course(3)    Course(4) Â
1       A                Java           C++
2       B                Java           SQL Server .Net            SAP
3Â Â Â Â Â Â Â CÂ Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Oracle
Basically need t-sql to Convert non fixed rows to non fixed columns...
Rule: IF each ID and Name have more than 1 course then show it in new columns as course(1) course(2)..Course(n)
Create SQL:
Create table Sample (ID Int null , Name Varchar(25) null, Course Varchar(20) null)
Insert SQL:
INSERT Sample (ID, Name, Course)
         VALUES (1,'A','Java'),
                (1,'A','C++'),
                (2,'B','Java'),
                (2,'B','SQL Server'),
                (2,'B','.Net'),
                (2,'B','SAP'),
                (3,'C','Oracle')
View 12 Replies
View Related
Aug 13, 2013
My dataset looks like this:
IDBuyDotComPriceCompanyIDShadowOf
AB CIRCLE PRO199.99203
AB CIRCLE PRO-b2199.99203AB CIRCLE PRO
AB CIRCLE PRO-TB249.99344AB CIRCLE PRO
AB CIRCLE PRO-TB-S10344AB CIRCLE PRO
I need to update the price of an item where the CompanyID is 344 and the ShadowOf is not null. The value in ShadowOf is the same as the ID that I want to get the BuyDotComPrice for. It should be simple, but I keep getting errors.
I use Microsoft SQL 2008
View 1 Replies
View Related
Mar 26, 2008
Hello all,
First off, I appreciate the time that those of you reading and responding to this request are offering. My quesiton is a theoretical and hopefully simple one, and yet I have been unable to find an answer to it on other searches or sources.
Here's the situation. I am working with SQL Server 2005 on a Windows Server 2003 machine. I have a series of databases, all of which are in Full recovery mode, using a backup device for the full database backups and a separate device for the log backups. The full backups are run every four days during non-business hours. The log backups are run every half hour.
Last week, one of my coworkers found that some rarely-used data was unavailable, and wanted to restore a database to a point in time where the data was available. He told me that point in time was some time back in November.
To accomplish this, I restored the database (in a separate database, as to not overwrite my production database) using the Point in Time Recovery option. I selected November from the "To a point in time" window (I should note that this window is always grey, never white like most active windows, it seems), and the full database backup and the subsequent logs all became available in the "Select the backup sets to restore" window.
I then tried a bevy of different options from the "Options" screen. However, every restore succeeds (ie: it doesn't error out), but seems to be bringing the database back to a current point in time. It's never actually going back to the point in time I specify.
My questions are as follows:
a) Is it possible to do a point in time recovery to a point in time BEFORE the last full database backup?
b) If so, what options would you recommend I use? (ie: "Overwrite the existing database", restore with recovery, etc etc).
I again appreciate any and all advice I receive, and I look forward to hearing from anyone and everyone on this topic. Thank you.
Ryan
View 4 Replies
View Related
Nov 10, 2006
I'm running SQL Server 2005 on a Server 2003 machine serving both our home network as well as a remote site through a point-to-point T1. While file transfer speeds are up to par, the remote site's interaction with SQL Server (Point of sale system) is very slow. After testing I am certain that it has nothing to do with the actual physical machine in place neither is it an issue with the program itself since speeds are as they are supposed to be over the home network lan. It seems that there might be a packet size issue or something of the sort. Has anyone dealt with this before or have any thoughts?
Thanks,
Peter
View 7 Replies
View Related
Feb 21, 2008
Hello all, I have a question. I have two tables, one called Products which is comprised of various lookup values for IDs. The sceond table is a join table where I enter through a tool that I built another series of lookup IDs for Product, Size, Color, Quantity and Price. Each product in the join table can have multiple entries because there is not one price for each product. Instead, based on the product size, color there are price breaks at certain quantities - for example if someone orders one dozen widgets then the price will be one amount, while if they order two dozen they will get a different price.
Here is an example:
Product table
ProductID ProductName Description
1 Widget This is a widget description...
ProductPrice tbl
ProductID ColorID SizeID QuantityID Price
1 1 1 1 10.00
1 1 1 2 9.50
etc....
Quantity tbl
ID Description
1 12 - or one dozen
2 24
So what I want to do is get a complete list of products with the MAX price for each product returned. This will be displayed on a page where I will have the Product Name, Description, etc and the Price will state something like 'Starting at $10.00'
Here is what I have so far but I am not getting any results....
SELECT p.ProductID,
p.ProductName,
pc.ProductCategoryName,
p.ProductImageID,
SUBSTRING(p.Description, 1, 150) + '...' AS Description,
(SELECT DISTINCT MAX(Price)
FROM ProductColorSizeQuantityPrice
WHERE ProductId = 1) AS 'Price'
FROM Products p
INNER JOIN ProductColorSizeQuantityPrice pcs
ON pcs.ProductID = p.ProductID
INNER JOIN ProductCategory pc
ON pc.ProductCategoryID = p.ProductCategoryID
The area highlighted in blue is what I am trying to do logically but this is not getting any results. If I removed this section I get all the fields I want but I need to include this price. Any ideas?
View 6 Replies
View Related
Apr 3, 2008
I want to make stored Procedure that get sum total of the order and return the ship pricethis is my table:in my procedure I want to get the order subTotal and return the ship priceFor example if the subTotal is 60$ than return 300$etc,my problem is that if the "toPrice" = 0, than its mean "higher than"How can I Write this Code, here is my try:declare @subTotal intset @subTotal = 60SELECT [ShippingPolicyId] ,[Header] ,[fromPrice] ,[toPrice],[price] FROM [dbo].[xxx_ShippingPolicy]where @subTotal >=[fromPrice] and @subTotal <= [toPrice]--where @subTotal >=[fromPrice] and case when [toPrice] > 0 then @subTotal <= [toPrice] else 1=1
View 5 Replies
View Related
Dec 11, 2004
I plan on purchasing a new production server that will be running sql server and IIS under the dotnet framework/Windows Server 2003. It will be doing the following-
Running 6 or 7 customer service web applications that will operate as asp.net apps w/vb.net. These are database intensive applications but run a relatively light load with only 20 or so users accessing.
Running the 'customer service' portion of our web site that customers will use to access their account info, historical orders, order status, etc. These are also asp.net pages and are modestly database intesive. If I had to make a WAG, I would say that maybe 50 users on average to say 100 peak using these pages.
I plan on buying a single Xeon with say 2 GB of RAM, I'm not sure about the hard drives but something with suds.
So my question after that long tome is what's the tradeoff between RAM and extra processor. Given a fixed budget for the server, if I were to ask for additional funds I'm not sure if I should invest in additional processor or more RAM.
And also, I'm not sure about running the server for both internal and external applications. Obviously the internal apps could take a hit if things got busy externally. Is there any kind of best practice that advises against this.
Any ideas?
View 2 Replies
View Related
Oct 24, 2006
Hi I would like to import into DB prices in format 1,000.00I am using type money (mssql2004) but it doesnt let mi import this format.Any ides. thx rek
View 12 Replies
View Related
Jan 22, 2008
Hi, i was wondering what is the correct way to handle price fields in the database and then in c#. I read somewhere to use a decimal over a money field in the database but then in c# should i cast the data returned to a double (not sure if this would work) or to a decimal. My problem is that i thought when working in c# you should treat numbers with a decimal point as either a float or double? Why is there a decimal type in c#? Appreciate if someone could clear this up.
Thanks
View 3 Replies
View Related
Aug 15, 2004
cheack it out at http://www.itgalaxy.org/learnkey.html
and many more stuff
get each cd in 5 to 8 $
View 2 Replies
View Related
Dec 5, 2007
Hello chapsI have a theta join on two tables, a tiddler and a bigun. The tiddler is there to recode the values of a field in my main table. It consists of mutually exclusive ranges corrollated to a new code. I want to keep the data in a table for flexibility however I would like the performance of a case statement. I suspect that SQL Server would be a lot better at this if it knew that the ranges are mutually exclusive. Although I know how to set up a check constraint to enforce this rule SQL Server still will not be able to use this information. The optimiser estimates three rows returned for the clustered index scan - actual rows is 699609 - which equals (number of rows in little table) * (number of rows in big table).BTW - I've been chucking unique constraints at the tiddly table just to see if I can affect execution - I can't.Question:Can I make this more efficient while maintining flexibility or is this just the price of a theta join? I would prefer to avoid dynamic sql please (in reality the full query is pretty complex).Ta!USE tempdbgoSET NOCOUNT ON ------------------------------ SET STUFF UP ---------------------------------IF NOT EXISTS (SELECT NULL FROM sys.schemas WHERE name = N'welovepoots') BEGIN EXEC ('CREATE SCHEMA welovepoots')ENDGOIF EXISTS (SELECT NULL FROM sys.tables WHERE name = N'little_table' AND SCHEMA_NAME(schema_id) = 'welovepoots') BEGIN DROP TABLE welovepoots.little_tableEND IF EXISTS (SELECT NULL FROM sys.tables WHERE name = N'big_table' AND SCHEMA_NAME(schema_id) = 'welovepoots') BEGIN DROP TABLE welovepoots.big_tableENDCREATE TABLE welovepoots.little_table ( recode_this_lower DECIMAL(9, 3) NOT NULL , recode_this_upper DECIMAL(9, 3) NOT NULL , recode_this_recode TINYINT NOT NULL , CONSTRAINT pk_little_table PRIMARY KEY NONCLUSTERED (recode_this_lower, recode_this_upper) WITH (FILLFACTOR = 100) , CONSTRAINT ix_little_table_u_nc UNIQUE NONCLUSTERED (recode_this_recode) WITH (FILLFACTOR = 100) , CONSTRAINT ix_little_table_u_c UNIQUE CLUSTERED (recode_this_lower, recode_this_upper, recode_this_recode) WITH (FILLFACTOR = 100) ) GOINSERT INTO welovepoots.little_table (recode_this_lower, recode_this_upper, recode_this_recode)SELECT 30, 999999.999, 0UNION ALLSELECT 16, 29.999, 2UNION ALLSELECT 1, 15.999, 1CREATE TABLE welovepoots.big_table ( my_id INT NOT NULL , recode_this DECIMAL(9, 3) , CONSTRAINT pk_big_table PRIMARY KEY CLUSTERED (my_id) WITH (FILLFACTOR = 100) )GOINSERT INTO welovepoots.big_table (my_id, recode_this)SELECT a.number * b.number , CASE WHEN MIN(a.number % 50.5) = 40.5 THEN NULL ELSE MIN(a.number % 50.5) ENDFROM dbo.numbers AS aCROSS JOIN dbo.numbers AS bWHERE a.number <= 650 AND b.number BETWEEN 651 AND 1300GROUP BY a.number * b.number------------------------------ END SET STUFF UP ---------------------------------SET NOCOUNT OFFSET STATISTICS IO ONSET STATISTICS TIME ONSET STATISTICS PROFILE ON--Inefficient but flexible SELECT recode_this , recode_this_recode FROM welovepoots.big_table AS bt LEFT OUTER JOIN welovepoots.little_table AS lt ON bt.recode_this BETWEEN lt.recode_this_lower AND lt.recode_this_upper--Efficient but inflexible SELECT recode_this , recode_this_recode = CASE WHEN recode_this BETWEEN 30 AND 999999.999 THEN 0 WHEN recode_this BETWEEN 16 AND 29.999 THEN 2 WHEN recode_this BETWEEN 1 AND 15.999 THEN 1 END FROM welovepoots.big_table AS btSET STATISTICS IO OFFSET STATISTICS TIME OFFSET STATISTICS PROFILE OFFSET NOCOUNT ON------------------------------ CLEAN UP ---------------------------------IF NOT EXISTS (SELECT NULL FROM sys.schemas WHERE name = N'welovepoots') BEGIN EXEC ('CREATE SCHEMA welovepoots')ENDGOIF EXISTS (SELECT NULL FROM sys.tables WHERE name = N'little_table' AND SCHEMA_NAME(schema_id) = 'welovepoots') BEGIN DROP TABLE welovepoots.little_tableEND IF EXISTS (SELECT NULL FROM sys.tables WHERE name = N'big_table' AND SCHEMA_NAME(schema_id) = 'welovepoots') BEGIN DROP TABLE welovepoots.big_tableEND------------------------------ END CLEAN UP ---------------------------------SET NOCOUNT OFF
View 14 Replies
View Related
May 25, 2004
bp server with the lowest price
Our company is a proffesional deticated bullet proof server and general server provider in china. If you're looking for a reliable and long term cooperation bp server provider, it deservers you to read our company file to know more about us.
we have the lowest price for bp server.
http://www.serverinchina.com/bpserverservice/index.htm
ICQ:226745581 MSN:dqkj#hotmail.com
ICQ:329211498 MSN:jiemie11#hotmail.com
Email: serverinchina#yahoo.com.cn
Website: www.mailinchina.com www.serverinchina.com
View 5 Replies
View Related
Feb 11, 2007
(Spam Removed)
View 1 Replies
View Related
Apr 23, 2007
Hi all,
I am trying to write a sp that searches 2 joined tables - ActiveArticle & ActiveArticlePrice for any products that exist in the update tables - UpdateArticle & UpdateArticlePrice. They contain the products listed for ALL suppliers so I first have to join both sets of tables based on the Supplier and then search the Active tables for any articles that have the same ArticleNumber, LotSize but different Prices. And I am getting all muddled up on how to do this, this is what I have so far, of course it isn't working:
ALTER PROCEDURE sp_GetPriceListUpdates
@SupplierGUID uniqueidentifier
AS
SELECT 'B' AS FLAG, 'New Price' AS TEXT, * FROM UpdateArticle UA, UpdateArticlePrice UAP
WHERE EXISTS (SELECT * FROM UpdateArticle UA, UpdateArticlePrice UAP
WHERE UA.SupplierArticleGUID=UAP.SupplierArticleGUID AND UA.SupplierGUID=@SupplierGUID
AND UA.UnitPrimeCost NOT IN
(Select * WHERE UA.SupplierArticleNumber AND UAP.LotSize IN (SELECT SupplierArticleNumber AND LotSize FROM ActiveArticle AA, ActiveArticlePrice AAP WHERE AA.SupplierArticleGUID=AAP.SupplierArticleGUID)
)
I would really appreciate any info on how I should be going about this problem!
Thanks,
PP
View 3 Replies
View Related
Nov 13, 2006
Does anyone have any ideas on how I can superscript a price field in a RS 2000 Report? I need the cents to be superscripted and underlined. I didn't want to use two text fields if possible. Is it possible do do something like this <sup>$</sup>99<u><sup>99</u></sup> ?
View 1 Replies
View Related
Feb 20, 2008
Hello all, I have an interesting question about calculating a price in my database for a quantity entered. I have a join table that I store ProductID, SizeID, Quantity and Price. The price for a particular product changes based most often on the quantity ordered. For example, if you order one unit of a widget the price is 10.00, however if you order one dozen units of the same widget the price drop to 9.75 and so on.
Here is a sample of my table....columns are in the order I specified above.
1, 1, 1, 10.00
1, 2, 1, 10.00
1, 3, 1, 10.00
1, 1, 6, 9.90
1, 2, 6, 9.90
1, 3, 6, 9.90
1, 1, 12, 9.75
1, 2, 12, 9.75
1, 3, 12, 9.75
1, 1, 24, 9.50
1, 2, 24, 9.50
1, 3, 24, 9.50
1, 3, 36, 9.30
1, 2, 36, 9.30
1, 1, 36, 9.30
So depending on if my widget is available in certain sizes, the second column, then each product has an price for the size id and quantity at which the price break occurs.
I use this stored procedure to return the price or price range based on the input parameters entered.
CREATE PROCEDURE dbo.sp_GetPrice
@ProductID INT,
@QuantityID INT = NULL,
@SizeID INT = NULL
AS
BEGIN
SET NOCOUNT ON
IF @QuantityID IS NOT NULL AND @SizeID IS NULL -- WE ARE L0OKING FOR A SPECIFIC PRICE BUT DO NOT HAVE A SIZE SPECIFIED
SELECT DISTINCT Price AS 'Price'
FROM join_ProductSizeQuantityPrice
WHERE ProductID = @ProductID
AND Quantity = @QuantityID
IF @QuantityID IS NOT NULL AND @SizeID IS NOT NULL -- WE WANT THE EXACT PRICE FOR THE SIZE AND QUANTITY SPECIFIED
SELECT Price AS 'Price'
FROM join_ProductSizeQuantityPrice
WHERE ProductID = @ProductID
AND SizeID = @SizeID
AND Quantity = @QuantityID
IF @SizeID IS NULL AND @QuantityID IS NULL
SELECT MIN(Price) AS 'Price Range' -- WE ARE LOOKING FOR A PRICE RANGE
FROM join_ProductSizeQuantityPrice
WHERE ProductID = @ProductID
UNION ALL
SELECT MAX(Price)
FROM join_ProductSizeQuantityPrice
WHERE ProductId = @ProductID
END
GO
So everything works great, however, when a user orders an quantity amount like 7, 13, 26 - which is not part of the table - I want to give them the discount available to them for the price break appropriate.
For example if a customer orders 16 widgets of size 2 the price break threshold they have crossed is one dozen, however they have not yet reached the next one which is two dozen. Therefore I want to offer the price associated with one dozen widgets of size id which is: $9.75. Once I have this a simple calculation of this price * 16 units would give me a total but my question is, how do I elegantly design this quantity / right price per unit calculation?
-Brian
View 9 Replies
View Related
Nov 21, 2003
SQL 2000 pricing and licensing is quite confusing, and even more pain including the upgrading pricing from sql6.5/sql7.0.
I got very clear Windows2000 pricing and upgrading from one Microsoft web address. But just could not find a ONE Microsoft web sit explicitely describe the $ pricing for each of SQL2000 edition (per processor vs. CAL..., including upgrading from SQL 6.5, SQL 7.0). May be I missed that site?
Thanks
David
View 2 Replies
View Related
Mar 13, 2006
I have a little problem that I just haven't been able to solve. I don't think it is very difficult but I can't seem to make it work. Here's the scenario:
I have a database with the following values:
Model Make Price
DA1100 GTN$88.00
DA1100 GTN $100.00
DA1000 GBN$110.00
DA668 GTN$100.00
DA880 GTN$200.00
In this case DA1100 is listed twice with 2 different prices. I only want to display the one with the lowest price. So the result I want is:
Model Make Price
DA1100 GTN$88.00
DA1000 GBN$110.00
DA668 GTN$100.00
DA880 GTN$200.00
View 1 Replies
View Related
Apr 3, 2006
Hello,
I have the follwoing problem:
I`d like to generate a new table out of the follwing one:
ID1 PRICE DATE ID2 S
1C8GYN2M21U13090810229.002005-11-07 00:00:00.0001358161
1FTDX07W0VKB74105-1500.002005-04-15 00:00:00.0001264960
1FTDX07W0VKB7410514950.002005-04-14 00:00:00.0001264961
1G6KY549X1U27448621301.052006-01-17 00:00:00.0001384811
Result:
ID1 PRICE DATE ID2
1C8GYN2M21U13090810229.002005-11-07 00:00:00.000135816
1FTDX07W0VKB74105 13450.00 2005-04-14 00:00:00.000 126496
1G6KY549X1U27448621301.052006-01-17 00:00:00.000138481
If there is an ID with a S 1 and 0 then sum up the price and take the date where s=1.
Write the result into another table.
Can anybody help me ?
Do I have to implement this by using cursors ?
Thx.
Dajm
View 12 Replies
View Related
Oct 24, 2013
I have a table with multiple products from different suppliers. there will duplicate products in my db as Supplier A may stock the same product as Supplier B. I have a business rule that only one unique product can be online at one time and as such I want the cheapest product from the 2 Suppliers to be displayed where there is a match.
I have been trying a Group By clause and also a partition by but my problem is returning the primary key for the winning row (minimum price).
here is an example
CREATE TABLE #TempTable(
productcode nvarchar(50),
productname varchar(50),
productmanufacturer varchar(50),
saleprice money,
tyrewidth varchar(5),
[Code] ....
How can I return the productcode in the above query for the row with the cheapest price? If I try MIN(productcode) or MAX(productcode) it will not always return the product code associated to the min price.
View 5 Replies
View Related
Jun 1, 2006
should I set to "0" as a default value on a quantity and a price fieldor set to "null"?if a user enter nothing on the quantity or price field on a webbrowser, should I treat it as null or "0"? I am so confused about thisconcept. please advise me. thank you.
View 5 Replies
View Related
Dec 19, 2006
Pricing is a little confusing at my work. My works database from the point of sale has multiple places for price to be. Right now the tables in question are in a SQL database. I am trying to create a user defined function so I can get a list of prices for specific items and for the customer logged into the web page. The tables look like this:Inventory: ItemID, ItemCompany, Description, ListPrice, PublicPriceContractID: ContractNum, ItemID, ItemCompany, MinQty, ContractPriceCustomer: CustomerNumber, CustomerDepartment, Contract1 – Contract4ContractNum ‘99’ is a global contract to all customers. I was able to get the price for this generic contract price, but not for contracts customer may have. Here is what I have so far (with an example item already being pulled up). I am at a loss on how to get the customers contract price with the same function. SELECT Inventory.ItemNumber, Inventory.Company, Inventory.Description, Contracts.Quantity AS MinContractQty, COALESCE (Contracts.ContractPrice, Inventory.WhlCatalogPrice) AS Price, Inventory.WhlCatalogPrice AS ListPriceFROM Inventory LEFT OUTER JOIN
Contracts ON Inventory.ItemNumber = Contracts.ItemNumber AND
Inventory.Company = Contracts.Company AND '99' = Contracts.ContractNumberWHERE (Inventory.ItemNumber = N'444') AND (Inventory.Company = N'035')
View 8 Replies
View Related
Sep 11, 2007
Hi,We are discussing possible implementation for sql2005 database(s). This database will serve one web portal. Part of data will get into it by hand, and part will be replicated from internal system.Some of us are for creating two separate databases, since there are two separate datasources. One, automatic, will change very little over time and requires almost no maintenance. Other datasource will be manual input. Tables and procedures related to this part will change over time.Some of us are for creating single database, since it will serve one web site. More important this group is concerned about performance issues since almost every select will require join between tables that would be stored in two separate databases. Do these issues exist? Can you share some insights, comments, links about this?
View 2 Replies
View Related
Sep 29, 2015
I need to find the last purchase price for each product. Â If I run the following code, I correctly get 1 result for each productID and the last purchase order number.
SELECT pod.article as ProductID,max(pod.order_ID) as LastOrderfrom apodetail podgroup by pod.articleorder by pod.article
Now I need to add in the price for that product on that orderID. Â I've tried the following self join query, tried it without the join, and tried adding DISTINCT, but they all return more than 1 row per ProductID.
SELECT pod.article as ProductID,max(pod.order_ID) as LastOrder,pod2.rev_price as UnitPricefrom apodetail podjoinapodetail pod2on (pod2.order_ID = pod.order_id)group by pod.article,pod2.rev_priceorder by pod.article
How can I get it to simply add the price to the first query?
View 10 Replies
View Related
May 1, 2000
I have a field of type numeric(5) in a SQL 7.0table that I'm trying to assign the value -100. I get an 8115 error. Does anybody know where I can find out what the possible values I can put into this field.
View 3 Replies
View Related
Feb 6, 2001
Dear All,
Despite multiple, unsuccessful attempts, I am unable to populate a table with converted int data using the following select syntax:
select name, convert(size*8000)/1024000 as Size into 'TableName' from sysfiles
order by name
results in the following:
Server: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type int.
The statement has been terminated.
What change/code is necessary for a successful select?
Regards, John
TIA
View 1 Replies
View Related
