SQL product new and default price

[AmrMohammed]

Greetings,

I have a table which contains product information called Products contains fields: ProductID, ProductName, and another table called UnitsInStores contains fields: StoreID, ProductID, UnitsNumber, UnitPrice

My first question is: Is it better design to put the UnitPrice field within the Products table or to put it within UnitsInStores table (As is happening already)
//-------------------------------------------------------------------------------------------------------------------
I have a table called CustomerNewPrices contains fields: ClientID, NewPrice, IssueDate in which I store product's new price for customers which given to the customer in a certain date

My question is: I wanna make a SQL Statement that when I issue a new invoice according to the invoice issue date and a specific customer too, before it returns me the unit price of the product it checks if there is a new price for that customer with a new price issue date equal to or greater than the invoice issue date. and if there are many price update for that customer for example that customer had a new price at 20/04/2013 and another new price at 25/04/2013 if I made the check as IssueDate >= @InvoiceIssueDate and @InvoiceIssueDate was 28/04/2013 it doesn't return me anything until the date within parameter was only equal to the dates in the table, however, I put the condition >=

[pritaeas]

My first question is: Is it better design to put the UnitPrice field within the Products table or to put it within UnitsInStores table (As is happening already)

This depends on your requirements. If a product can have a different price, it makes sense to NOT put it in the products table.

Your second question is missing something, your sentence is unfinished. Do show your query.

[AmrMohammed]

The product have a default initial price, however, for some customer(s) this price at a certain day changed for these specific cusotmer(s) so there is another table in the database for this "customer(s)-product(s) new price(s)" so is it a better design to put the default initial price in the product information table or to put it in "store(s)-product(s)" table?

SELECT NewPrice FROM CustomerNewPrice WHERE CustomerID=@CustomerID AND ProductID=@ProductID AND IssueDate>=@IssueDate

but that statement doesn't work properly as I said. If there were 2 new price: 100 LE given at 20/04/2013 & another 150 LE give at 25/04/2013 and I execute the above statement with @IssueDate = 28/04/2004 it doesn't return any data, however, if I changed the @IssueDate to 20/04/2013 or 25/04/2013 it returns me the new price related to that date.

I need to make a statement that makes that: according to the invoice issue date, a specific customer, and a specific product it returns me the (last) price given to that customer in a date equal to or greater than the invoice issue date and if there is no new price found for that customer it returns the default initial price for that product. Hope I explained my need well

[Lethugs]

There are a lot of problems (for me) in dealing with dates, try to check the datatypes you used in your column, or convert each date to make sure they have the same format:

(CONVERT(Datetime, @IssueDate, 101))

Hope that helps

[pritaeas]

If you are using date columns, make sure your query uses 'yyyy-mm-dd' format.

[AmrMohammed]

"Lethugs, pritaeas" thanks for help. The syntax of the statement was wrong from the beginning :(. I had the change the condition "IssueDate >= @IssueDate" with "@IssueDate >= IssueDate" as I want prices equal to or less than a specified day so the new syntax should be

SELECT NewPrice FROM CustomerNewPrice WHERE CustomerID=@CustomerID AND ProductID=@ProductID AND @IssueDate >= IssueDate

The second problem I had assuming that table CustomerNewPrice including 2 records and IssueDate column including the value 2013-04-20 on first record and the value 2013-04-25 on the second record and assuming the @IssueDate parameter having the value 2013-04-28 it return me 2 prices. I need to return only the last price not all prices, meant the price in the second record where IssueDate having the value 2013-04-25