Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 242
  • Last Modified:

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
0
jiiins2
Asked:
jiiins2
  • 5
  • 4
1 Solution
 
momi_sabagCommented:
only for pricesource1? what about pricesource3?
if it's only for pricesource1 you can do this:

insert into myTable
select  t1.itemNo, t1.Date, 1 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
0
 
jiiins2Author Commented:
Hi momi, sorry for the late reply. This works great! Can I ask you one more thing? What would I need to change if I wanted PriceSource1'price to be overwritten by min(t1.price) if greater than it?

I mean:
if PS1price > min(t1.price) then PS1price = min(t1.price)

Thanks a lot!
J
0
 
momi_sabagCommented:
but we select 1 as the PriceSource
i don't understand the question
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
jiiins2Author Commented:
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
0
 
momi_sabagCommented:
can you post here the query you are currently using?
0
 
jiiins2Author Commented:
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

0
 
momi_sabagCommented:
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
0
 
jiiins2Author Commented:
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

0
 
momi_sabagCommented:
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
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now