Chloe Chloe - 3 months ago 8
SQL Question

Price History Log query in SQL SERVER

I have three tables, one distinctly contains the items to be sold. one that contains the name of the supplier of the item. one that contains the transactions which houses the amount item was purchased namely, the invoices.

I want to have a History Log of the each item by date but i am having trouble since when i do it, all the data are included, what i want are the distinct prices by date which is as displayed below.

What I have.

tbl_Items
--------
item_ID int
itemName varchar(500)
supplier_ID int

tbl_Suppliers
-------------
sup_ID int
sup_Name varchar(500)

tbl_Invoices
------------
inv_Num int
inv_Date date
item_ID int
sup_ID int
inv_amount int


as you can see, the item amount is in the invoices because the prices changes depending on whom we sell to, so there is no need to make the prices fix. as stated, i want to create Price History Log which should look like this.

inv.inv_amount | inv.inv_Date | item.itemName | s.sup_Name |
----------------------------------------------------------------
200 | 07-01-2016 | Ballpen | Panda |
190 | 07-16-2016 | Ballpen | Panda |
250 | 07-16-2016 | Ballpen | Panda |
400 | 07-16-2016 | Notebook | Aspire |
420 | 07-21-2016 | Notebook | Aspire |
----------------------------------------------------------------


i used the table prefix for reference on where the data would came from as what i use on for the code that i have. the code that i have outputs something like this.

inv.inv_amount | inv.inv_Date | item.itemName | s.sup_Name |
----------------------------------------------------------------
200 | 07-01-2016 | Ballpen | Panda |
200 | 07-01-2016 | Ballpen | Panda |
200 | 07-01-2016 | Ballpen | Panda |
190 | 07-16-2016 | Ballpen | Panda |
250 | 07-16-2016 | Ballpen | Panda |
400 | 07-16-2016 | Notebook | Aspire |
420 | 07-21-2016 | Notebook | Aspire |
----------------------------------------------------------------


it has redundant data, i just need the distinct prices, the date is just a reference to know when the price decreased or increased. Hope i have provided enough info.

Answer

If you wanted to get the items with different price ,use the below script.

with cte_1
as (
SELECT i.inv_amount,i.inv_Date,t.itemName, s.sup_Name,ROW_NUMBER()OVER(PARTITION BY i.inv_amount,t.itemName ORDER BY t.itemName,i.inv_Date) as Rno
FROM tbl_Items t
JOIN tbl_Suppliers s
 on t.supplier_ID =s.supplier_ID 
 JOIN tbl_Invoices i
   on t.item_ID =i.item_ID  AND s.sup_ID =i.sup_ID 
 )

SELECT inv_amount,inv_Date,itemName,sup_Name
FROM cte_1
WHERE Rno=1