Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
GDufresne
Starting Member
3 Posts |
 Posted - 2011-01-06 : 22:36:22
|
| Hi! I'm new to SQL so please excuse me. Here is my prolem.Say I have 2 tables.Table1 containing 5 columns.Customer#, Item#, DATE_SOLD, Quantity, Total (= Quantity * UnitPrice)Table2 (PriceList)Item#, UnitPrice, Date1, Date2The price of "Item" has changed over time, so it has many possible values depending on the date of purchase. Value Table1.Total is then calculated from the UnitPrice that was effective at DATE_SOLD, that is DATE_SOLD is between Date1 and Date2.What kind of relation should be established between those two tables?I just can't find.TIA,GDufresne |
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2011-01-06 : 23:59:39
|
| For me its one(table2) to many (table1). Now if you say how as the records of the same item# is duplicated in the Table2 as well. So its because of that every Item will have One "Effective" record at a time. Either by updating the same record every time when the price is changed or by inserting a new record for the same item and setting the Date2(let say its expiry date) with a value.Cheers! |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-01-08 : 01:26:50
|
| one question. what will be date2 value for latest applicable unitprice? will Date2 be NULL or will it be some future date?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
GDufresne
Starting Member
3 Posts |
Posted - 2011-01-09 : 00:22:40
|
| Date2 would be a future date say 2050/01/01 or null, I think it doen't matter.I know this would be a one to many relation. If we have a standard relation between ListPrice and Bill the relation would be something like "Bill join ListPrice on Bill.Item = ListPrice.Item#". The problem is that we should have "Table1 join Table2 on Table1.Date BETWEEN Table2.Date1 and Table2.Date2".How could that be implemented in SQL diagram?TIA GeorgesGDufresne |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-01-09 : 00:58:25
|
| it wont be one to many relationship as at any instant there would be only one record in Table2 for Item which falls within the date range------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
GDufresne
Starting Member
3 Posts |
Posted - 2011-01-09 : 10:21:53
|
| Hi!I have not been clear enough. That's my fault. So back to the beginning.Let's say I have two tables as shown below.ItemsTableItem#DescriptionDateINIDateENDPriceInvoicesTableCustomer#DateOfPurchaseItem#QuantityCostI want to keep trace of the price of each Item over time. So in the ItemsTable, I keep the price of each Item for every period. Suppose I want to know what was the price of Item Item# on December 2003, I search ItemsTable for Item# AND December 2003 between DateINI and DateEND.If I only had to search for Item#, I could establish a join like InvoicesTable join ItemsTable on Item#, but here, dates won't match exactly, it has to be IN BETWEEN. That is my question. Is ther any method by which I could join these two tables in a SQL Server Diagram?GDufresne |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-01-09 : 12:55:09
|
but didnt join using BETWEEN not work for you?SELECT *FROM InvoicesTable iINNER JOIN ItemsTable itON it.Item# = i.Item#AND i.DateOfPurchase BETWEEN it.DateINI AND it.DateEND ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|
|
Keys in between