Solved

Help with recursive SQL instruction

Posted on 2008-10-23
9
233 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
 

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

867 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now