Solved

Help with recursive SQL instruction

Posted on 2008-10-23
9
237 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

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

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

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

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

685 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