jiiins2
asked on
Help with recursive SQL instruction
Hi experts,
On a SQLExpress db I have a table with the following structure:
ItemNo Date PriceSource Price
In a few words, we get prices for our items from different sources. The problem is that not all sources provide prices everyday and I need to fill the gap on one of them. What I need to do, is to copy the lowest price at any given date to PriceSource 1 if and only if PrisceSource 1 is empty.
Example: on day 31/09/2008 I get the following prices:
ItemNo, Date, PriceSource, Price
55, 31/09/2008, 2, 202.50
55, 31/09/2008, 4, 206.25
55, 31/09/2008, 5, 201.95
I have no price from PriceSource 1. Therefore I need to insert a record as follows:
55, 31/09/2008, 1, 201.95 (the lowest price above)
The code should do this for all records (dates) in the table.
Is it possible to achieve the above with a routine I could execute with SQLCMD?
Thanks a lot!
J
On a SQLExpress db I have a table with the following structure:
ItemNo Date PriceSource Price
In a few words, we get prices for our items from different sources. The problem is that not all sources provide prices everyday and I need to fill the gap on one of them. What I need to do, is to copy the lowest price at any given date to PriceSource 1 if and only if PrisceSource 1 is empty.
Example: on day 31/09/2008 I get the following prices:
ItemNo, Date, PriceSource, Price
55, 31/09/2008, 2, 202.50
55, 31/09/2008, 4, 206.25
55, 31/09/2008, 5, 201.95
I have no price from PriceSource 1. Therefore I need to insert a record as follows:
55, 31/09/2008, 1, 201.95 (the lowest price above)
The code should do this for all records (dates) in the table.
Is it possible to achieve the above with a routine I could execute with SQLCMD?
Thanks a lot!
J
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
but we select 1 as the PriceSource
i don't understand the question
i don't understand the question
ASKER
You are right, sorry. Please try to imagine that PriceSource1 is not a real price source but more of a collection of prices.
In your script if there is no record for PS1 at a specific date, a new record will be created with PS1's price = min(t1.price). Perfect.
What I'm asking is if it's possible to do the "same" if the record exists, updating the record (PS1 price) if that price is higher than min(t1.price). This way, I could use PS1 to get always the lowest price.
New example: on day 31/09/2008 I get the following prices:
ItemNo, Date, PriceSource, Price
55, 31/09/2008, 2, 202.50
55, 31/09/2008, 4, 206.25
55, 31/09/2008, 5, 201.95
55, 31/09/2008, 1, 203.95
I have a price from PriceSource 1, but it's not the lowest. Therefore I need to update the record as follows:
55, 31/09/2008, 1, 201.95 (the lowest price above)
Does it make sense? Sorry for my lack of explanatroy skills...
Thanks a lot
J
In your script if there is no record for PS1 at a specific date, a new record will be created with PS1's price = min(t1.price). Perfect.
What I'm asking is if it's possible to do the "same" if the record exists, updating the record (PS1 price) if that price is higher than min(t1.price). This way, I could use PS1 to get always the lowest price.
New example: on day 31/09/2008 I get the following prices:
ItemNo, Date, PriceSource, Price
55, 31/09/2008, 2, 202.50
55, 31/09/2008, 4, 206.25
55, 31/09/2008, 5, 201.95
55, 31/09/2008, 1, 203.95
I have a price from PriceSource 1, but it's not the lowest. Therefore I need to update the record as follows:
55, 31/09/2008, 1, 201.95 (the lowest price above)
Does it make sense? Sorry for my lack of explanatroy skills...
Thanks a lot
J
can you post here the query you are currently using?
ASKER
unfortunately I don't have any access to the query... it is hard-coded in an application we use. That's why I need to tamper with the db!
Thanks,
J
Thanks,
J
try
insert into myTable
select t1.itemNo, t1.Date, case when 1 < min(t1.price) then 1 else min(t1.price) end as PriceSource, min(t1.price)
from myTable t1
left outer join myTable t2 on t1.itemNo = t2.itemNo and t1.Date = t2.Date and t2.PriceSource=1
where t2.itemNo is null
group by t1.date, t1.itemno
insert into myTable
select t1.itemNo, t1.Date, case when 1 < min(t1.price) then 1 else min(t1.price) end as PriceSource, min(t1.price)
from myTable t1
left outer join myTable t2 on t1.itemNo = t2.itemNo and t1.Date = t2.Date and t2.PriceSource=1
where t2.itemNo is null
group by t1.date, t1.itemno
ASKER
Hi momi,
this unfortunately doesn't seem to be working. The query result tells me that 806 rows were affected, but I can't see which... the records I tested didn't seem to change and PS1's price remained what it was even if PS2's price was lower.
Any ideas?
Many thanks,
J
this unfortunately doesn't seem to be working. The query result tells me that 806 rows were affected, but I can't see which... the records I tested didn't seem to change and PS1's price remained what it was even if PS2's price was lower.
Any ideas?
Many thanks,
J
look that in the query i check min(t1.price) against 1, so it makes sense that most of the records will remain with 1 as the PriceSource
ASKER
I mean:
if PS1price > min(t1.price) then PS1price = min(t1.price)
Thanks a lot!
J