Solved

Help with recursive SQL instruction

Posted on 2008-10-23
9
236 Views
Last Modified: 2012-05-05
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
Comment
Question by:jiiins2
  • 5
  • 4
9 Comments
 
LVL 37

Accepted Solution

by:
momi_sabag earned 500 total points
ID: 22784352
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
 

Author Comment

by:jiiins2
ID: 22828936
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
 
LVL 37

Expert Comment

by:momi_sabag
ID: 22829031
but we select 1 as the PriceSource
i don't understand the question
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

Author Comment

by:jiiins2
ID: 22837955
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
 
LVL 37

Expert Comment

by:momi_sabag
ID: 22838774
can you post here the query you are currently using?
0
 

Author Comment

by:jiiins2
ID: 22838885
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
 
LVL 37

Expert Comment

by:momi_sabag
ID: 22838897
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
 

Author Comment

by:jiiins2
ID: 22848883
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
 
LVL 37

Expert Comment

by:momi_sabag
ID: 22856073
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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question