?
Solved

Help with recursive SQL instruction

Posted on 2008-10-23
9
Medium Priority
?
240 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 37

Accepted Solution

by:
momi_sabag earned 2000 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

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.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

770 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