Databases
StackOverflow2013
Postsdbo
PODates and timespans with SQL
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PODates and timespans with SQL
    text
    copied!<p>Given the following table and data.</p> <pre><code> create table prices (productKey int ,PriceType char(10) ,BeginDate date ,EndDate date ,price decimal(18,2)) insert into prices(productKey, PriceType,BeginDate,EndDate, price) values (1,'LIST','1-1-2010','1-15-2010',10), (1,'LIST','1-16-2010','10-15-2010',20), (1,'DISCOUNT','1-10-2010','1-15-2010',-5), (2,'LIST','2-1-2010','10-15-2010',30), (2,'LIST','10-16-2010','1-1-9999',35), (2,'DISCOUNT','2-10-2010','10-25-2010',-10), (2,'LIST','1-1-2010','1-15-2010',10), (3,'DISCOUNT','1-12-2010','1-1-9999',-5), (3,'LIST','1-16-2010','1-1-9999',10) </code></pre> <p>I need to insert records into that same table that calculates the actual price (list-discount) for each time period.</p> <p>e.g. for product 1, I should have the following "ACTUAL" records</p> <pre><code>Begin End Price 1-1-2010 1-9-2010 10 1-10-2010 1-15-2010 5 1-16-2010 10-15-2010 20 </code></pre> <p>I kind of have it figured out for anything where a discount starts within a list price span, but I'm at a loss for anything else.</p> <p>Thanks for the help</p> <p><strong>EDIT</strong></p> <p>There can be multiple discounts per ProductKey, but the discount periods won't overlap. So you could have one for 2010, and another one for 2012, but not 2 for 2010.</p> <p>Also, if someone can come up with a better title, please do so. My poor brain is completely challenged at this point.</p> <p><strong>EDIT2</strong></p> <p>It's SQL server 2008R2. I'd love a beautiful set based answer (or someone that gives me a start in that direction), but will be just as happy with a cursor solution that works.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload