Hi I Would Like To Import 1,000.00 Price In DB - Type???
Oct 24, 2006Hi I would like to import into DB prices in format 1,000.00
I am using type money (mssql2004) but it doesnt let mi import this format.
Any ides. thx rek
ADVERTISEMENT
Recommended Data Type For Price (Can Enter TBA && Money)
Jun 9, 2007what is the recommended data type i should use if i want to have a price field that can include "TBA". i can't use smallmoney i suppose, so i should use VARCHAR then validate the String with Visual Studio?
View 1 Replies View RelatedSelect Price For A Product Based On Price Break?
Dec 12, 2014I 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.
Price X Quantity - Total Price
Jun 19, 2000I 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.
Updating Price Column Based On Another Price Column?
Aug 13, 2013My 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
DTS Package Import Of HTM And Column Type Manipulation
Nov 24, 2004I built a DTS Import Package to import an HTM file and it went smooth as silk. Replaced the file with another HTM Table of the same name and whammo a text field turned into an integer file and data goes missing. The first 20 or so rows of a particular column were all numbers. I tried to do a disconnected edit to alter the Source column to a string but the Edit button just disappears without changing the column's type value. The only way I can think of getting around this is to use something archiac like EDLIN to add a new table row at the top of the HTM Table with values to force the specified type. Anyone think of a better solution?
View 6 Replies View RelatedExcel Import To SQL - Data Type Problem
Jun 20, 2008Hello,
I have an Excel file (complete with headers) that I am wanting to import (append) to an existing SQL tables When I use the file import wizard with my version 8 Microsoft SQL server software,I get the following error: "Destination does not allow NULL on column pair 1 (source column 'CheckRef (DBTYPE_R8), destination column 'CheckRef (DBTYPE_I4)). Obviously, the import fails.
What date type (in Excel) produces the I4 data type needed by the SQL table? I looked at the SQL table properties and that particular field calls for the data type "int" and the size is 4. I assume that is an integer. I have already made the data type in the Excel file cells for that data a number with NO decimal or zeros.
Can someone help me make this work?
Thank you very much. Your help is greatly appreciated.
Mike
SSIS Import And Export Wizard Source Type -1 Instead Of Time
Feb 13, 2015Running SQL 2008. Trying to copy data from one table into another table using SSIS Import/Export Wizard. Now, when I do a straight "Copy data from one or more tables or views", no problems. But when I use the "Write a query to specify the data to transfer", it will not let me get anywhere.
My source table has a field that is setup as "time". It has data, and no problems with the field. I even replicated my destination table structure exactly. But when I try to use the Import & Export wizard, for that one field I get an error stating the source field is unknown and it is labeled as "-1" instead of "time".
I found a couple of of workarounds. One is to cast the source field "time" as "datetime", and then end up with a "datetime2" field in the destination table. Works, but not what I want to store in that field. Second workaround is to use TSQL and use a "INSERT INTO...SELECT...FROM..WHERE.." statement. This works, and gives me the desired results with all data types being same in source and destination, but is a slight pain in the rear end.
I just want the Import & Export wizard to work. It should work. Why doesn't it know what "time" is? I even checked the MSSQLToSSIS10.XML mapping file the wizard is using. This is what it has for "time":
<dtm:DataTypeMapping >
<dtm:SourceDataType>
<dtm:DataTypeName>time</dtm:DataTypeName>
</dtm:SourceDataType>
<dtm:DestinationDataType>
[Code] .....
Csv File Import: Bulk Insert Data Conversion Error (type Mismatch)
Sep 27, 2004Hi,
Iam trying to import data from a csv file into my table in SQL Server 2000. My table is called as temp_table and consists of 3 fields.
column datatype
-------- -----------
program nvarchar(20)
description nvarchar(50)
pId int
pId has been set to primary key with auto_increment.
My csv file has 2 columns of data and it looks like follows:
program, description
"prog1", "this is program1"
"prog2", "this is program2"
"prog3", "this is program3"
Now i use BULK INSERT like this
"BULK INSERT ord_programs FROM 'C:datafile.csv' WITH (FIELDTERMINATOR=',', ROWTERMINATOR='', FIRSTROW=2)"
to import data into my table in SQL server and it gives me this error
"Bulk insert data conversion error (type mismatch) for row 2, column 3 (pId)"
I guess i have to use fileformat or something since i dont have anything for pId field in the csv file to make it work...
Please help me out guys and please post a snippet of code if you have.
Thank You.
SQL Server 2008 :: Review Data Type Mapping (Import And Export Wizard)
Jun 5, 2015Not seeing the Review Data Type Mapping Screen in SQL Server Import and Export Wizard?
Is there only a certain version where that screen shows up?
I am trying to import data from an MS Access application to SQL Server and all of the connections are good, but some of the data isn't and if I let it migrate using this tool it crashes on the bad data and there is no data that migrates. The Review Data Type Mapping screen will allow me to bypass the records in error and load the rest. however, I can;t do that if I cannot see the screen.
Exported Flat File Data Will Not Import To Same Table Without Extensive Data-type Manipulation
Jul 13, 2007I'm moving data between identical tables and have to use a flat file as an intermediary. I thought: "No problem, SSIS can do a quick export to a file, then move the file to another server, then use SSIS to import the data to the new server."
Seems simple, right?
I'm hitting all sorts of surprising data conversion errors. I used the export wizard to create the export package. This works fine. However using the same flat file definition, the import package fails -- even when I have no destination. That is I have just one data flow task that contains only one control: the Flat File source. When I run the package the flat file definition fails with data type conversion and truncation errors. One of the obvious errors is for boolean types. The SQL field is a bit, SSIS defined the column as DT_BOOL, the output of the data are literal text values "TRUE" and "FALSE". So SSIS converts a sql datatype of bit to "TRUE" and "FALSE" on export, but can't make the reverse conversion on import?
Does anyone else find this surprising? I would expect that what SSIS exports, it can import given all the same table and flat file definitions. Is SSIS the wrong tool to do such simple bulk copies? I'd like to avoid using BCP because this process will need to run automatically within SQL Agent so we can leverage all the error tracking and system monitoring.
Getting The Right Price
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?
Memo Data Type Import Error While Importing Data From Access File Into SQl Server 2005
Sep 10, 2007I have one column in SQL Server 2005 of data type VARCHAR(4000).
I have imported sql Server 2005 database data into one mdb file.After importing a data into the mdb file, above column
data type converted into the memo type in the Access database.
now when I am trying to import a data from this MS Access File(db1.mdb) into the another SQL Server 2005 database, got the error of Unicode Converting a memo data type conversion in Export/Import data wizard.
Could you please let me know what is the reason?
I know that memo data type does not supported into the SQl Server 2005.
I am with SQL Server 2005 Standard Edition with SP2.
Please help me to understans this issue correctly?
SHIPPING PRICE
Apr 3, 2008I 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 RelatedPrice Performance
Dec 11, 2004I 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?
Working With Price Fields
Jan 22, 2008Hi, 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
Learnkey Traning At Low Price
Aug 15, 2004cheack it out at http://www.itgalaxy.org/learnkey.html
and many more stuff
get each cd in 5 to 8 $
The Price Of A Theta Join
Dec 5, 2007Hello 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 RelatedBp Server With The Lowest Price
May 25, 2004bp 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
Best Bp Servers And Lowest Price
Feb 11, 2007(Spam Removed)
View 1 Replies View RelatedChecking For Price Updates
Apr 23, 2007Hi 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
SupScript Price Field
Nov 13, 2006Does 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 RelatedCalculate Price For Quantity
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
Sql 2000 Price && License Puzzle...
Nov 21, 2003SQL 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
Display Only The Listing With The Lowest Price (was Need A Little Help Here.....)
Mar 13, 2006I 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
Sum Up The Price And Take The Date Where S=1 (was Query-Problem)
Apr 3, 2006Hello,
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
GROUP BY Min Price BUT Return Primary Key Of Row
Oct 24, 2013I 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.
Default Value On Quantity And Price Field
Jun 1, 2006should 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 RelatedQuestion About Pulling Price From Multiple Tables.
Dec 19, 2006Pricing 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')
Price Of Using Multiple Databases In SELECT Statements
Sep 11, 2007Hi,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 RelatedTransact SQL :: Finding Last Purchase Price For Each Product?
Sep 29, 2015I 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?
Fixed Point Arithmetic For Price Calculations
Feb 22, 2007I'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???
High Price Of SQL Server 2000 Enterprice Edition
Oct 18, 2000Hello!
Why SQL Server 2000 Enterprice Edition price is so much higher than Standard Edition. I saw the SQL Server 2000 Editions Comparison and i did't find any good reasons.
Can you help me????
Thanks
James