November 28, 2012 at 7:20 pm
Ok, here's an easy one for some of you. I want to read through a group of items and determine the most recent price increase for an item some time in the past, but I don't know when it occurred. For example, I have a table "FRUIT" containing food items, price & date:
[font="Courier New"]item price date
Apple $1.00 Jan 2012
Apple $1.00 Feb 2012
Apple $1.50 Mar 2012
Apple $1.50 Apr 2012
Apple $1.50 May 2012
Apple $1.75 Jun 2012
Apple $1.75 Jul 2012
Apple $1.75 Aug 2012
Pear $0.95 Jan 2012
Pear $1.00 Feb 2012
Pear $1.00 Mar 2012
Pear $1.00 Apr 2012
Pear $1.50 May 2012
Pear $1.50 Jun 2012
Pear $1.50 Jul 2012 [/font]
So, I need to loop through each fruit and determine the amount of the most recent increase. For Apples it's $0.25 in June (ignore the earlier price increase).
For pears it's $0.50 in May. Once I know the fruit & price I can update a summary table with the calculated increase amount ($0.25 or $0.50 )
I'm hoping there's a more elegant way than the convoluted scheme I'm coming up with (temp tables and variables to hold temp values ... yuck). I was searching a bit, but couldn't think of what type of problem this is, so didn't get the right keywords.
I know I'm supposed to post the real DDL and sample data, but I thought this would be simpler for discussion.
use tempdb
create table dbo.Fruit(Item varchar(10) not null, price dec(6,2) not null, EntryDate datetime )
insert into fruit values ('Apple', 1.00, '2012-01-01')
insert into fruit values ('Apple', 1.00, '2012-02-01')
insert into fruit values ('Apple', 1.50, '2012-03-01')
insert into fruit values ('Apple', 1.50, '2012-04-01')
insert into fruit values ('Apple', 1.50, '2012-05-01')
insert into fruit values ('Apple', 1.75, '2012-06-01')
insert into fruit values ('Apple', 1.75, '2012-07-01')
insert into fruit values ('Apple', 1.75, '2012-08-01')
insert into fruit values ('Pear', .95, '2012-01-01')
insert into fruit values ('Pear', 1.00, '2012-02-01')
insert into fruit values ('Pear', 1.00, '2012-03-01')
insert into fruit values ('Pear', 1.00, '2012-04-01')
insert into fruit values ('Pear', 1.50, '2012-05-01')
insert into fruit values ('Pear', 1.50, '2012-06-01')
insert into fruit values ('Pear', 1.50, '2012-07-01')
insert into fruit values ('Pear', 1.50, '2012-08-01')
November 29, 2012 at 5:34 am
Smells suspiciously like homework to me...
Here's a solution. It has a minor error in it. See if you can find it...
create table #fruit(Item varchar(10) not null, price dec(6,2) not null, EntryDate datetime )
insert into #fruit values ('Apple', 1.00, '2012-01-01')
insert into #fruit values ('Apple', 1.00, '2012-02-01')
insert into #fruit values ('Apple', 1.50, '2012-03-01')
insert into #fruit values ('Apple', 1.50, '2012-04-01')
insert into #fruit values ('Apple', 1.50, '2012-05-01')
insert into #fruit values ('Apple', 1.75, '2012-06-01')
insert into #fruit values ('Apple', 1.75, '2012-07-01')
insert into #fruit values ('Apple', 1.75, '2012-08-01')
insert into #fruit values ('Pear', .95, '2012-01-01')
insert into #fruit values ('Pear', 1.00, '2012-02-01')
insert into #fruit values ('Pear', 1.00, '2012-03-01')
insert into #fruit values ('Pear', 1.00, '2012-04-01')
insert into #fruit values ('Pear', 1.50, '2012-05-01')
insert into #fruit values ('Pear', 1.50, '2012-06-01')
insert into #fruit values ('Pear', 1.50, '2012-07-01')
insert into #fruit values ('Pear', 1.50, '2012-08-01')
; with cte as (
select Item, Price, max(EntryDate) as LastEntryAtPrice, min(EntryDate) as FirstEntryAtPrice
from #fruit
group by Item, Price
)
, cte2 as (
select *
, row_number() over (partition by Item order by LastEntryAtPrice) as RowKey
from cte
)
select cte2.Item
, OldPrice = cte2.Price
, NewPrice = offset.Price
, PriceIncrease = offset.Price - cte2.Price
, offset.FirstEntryAtPrice as ChangeDate
from cte2
join cte2 offset
on cte2.Item = offset.Item
and cte2.RowKey = offset.RowKey - 1
Regards, Iain
November 29, 2012 at 8:57 am
No, not homework. Actually it's for a friend who works at another company but has even less sql coding skills than me. I just made a dummy table instead of using their confusing live tables.
I really shouldn't have offered to help due to lack of time, but I did anyway,
I need to catch up on some of these "new" features. I spend most of my time in administration & networking and my coding skills are suffering.
Thanks
November 29, 2012 at 9:51 am
I messed around with it last night & got the same result as what your solution gives with this, but it shows both price increases, not just the most recent. Maybe your "mistake" fixes that ? I don't really understand how CTE works. :blush:
Could you explain how the logic of your example works ?
SELECT ph.Item, convert(varchar(10),ph.EntryDate,120) as 'Entry Date', ph.price,
(SELECT TOP (1) h.price
FROM dbo.fruit h
WHERE h.Item = ph.Item AND h.EntryDate < ph.EntryDate
ORDER by h.EntryDate DESC) AS [PrevPrice],
ph.price - ISNULL((SELECT TOP (1) h.price
FROM dbo.fruit h
WHERE h.Item = ph.Item AND h.EntryDate < ph.EntryDate
ORDER by h.EntryDate DESC), ph.price )
AS PriceDiff
FROM dbo.fruit ph
where ph.price - ISNULL((SELECT TOP (1) h.price
FROM dbo.fruit h
WHERE h.Item = ph.Item AND h.EntryDate < ph.EntryDate
ORDER by h.EntryDate DESC), ph.price ) > 0
ORDER BY ph.Item, ph.EntryDate
ItemEntryDateprice PrevPrice PriceDiff
Apple2012-03-011.501.000.50
Apple2012-06-011.751.500.25
Pear2012-02-011.000.950.05
Pear2012-05-011.501.000.50
November 29, 2012 at 2:54 pm
<hit the post button in error>
November 29, 2012 at 3:09 pm
Think of a cte as another type of table variable or temp table, that instead of lasting for the duration of the session just lasts for one query. They're useful for situations like this, where you want to do some pre-aggregation, as well as lots of other things.
So:
;with cte as (
select Foo
from FooTable
)
Is a bit like:
select Foo
into #foo
from FooTable
The difference being that the cte lasts for just one query, so this is fine:
;with cte as (
select Foo
from FooTable
)
select * from cte
But this isn't:
;with cte as (
select Foo
from FooTable
)
select * from cte
select * from cte -- this will error
My example groups up each Item and the range of dates for which a price was charged. It then assigns a row number, ordered by date, so internally, the data looks a bit like this (only showing apples for clarity):
Item Price LastEntryAtPrice FirstEntryAtPrice RowKey
---------- --------------------------------------- ----------------------- ----------------------- --------------------
Apple 1.00 2012-02-01 00:00:00.000 2012-01-01 00:00:00.000 1
Apple 1.50 2012-05-01 00:00:00.000 2012-03-01 00:00:00.000 2
Apple 1.75 2012-08-01 00:00:00.000 2012-06-01 00:00:00.000 3
By joining on the RowKey, but with an offset of -1, I can put the old and new price on the same row. This makes it simple to do the maths:
Item Price LastEntryAtPrice FirstEntryAtPrice RowKey Item Price LastEntryAtPrice FirstEntryAtPrice RowKey
---------- --------------------------------------- ----------------------- ----------------------- -------------------- ---------- --------------------------------------- ----------------------- ----------------------- --------------------
Apple 1.00 2012-02-01 00:00:00.000 2012-01-01 00:00:00.000 1 Apple 1.50 2012-05-01 00:00:00.000 2012-03-01 00:00:00.000 2
Apple 1.50 2012-05-01 00:00:00.000 2012-03-01 00:00:00.000 2 Apple 1.75 2012-08-01 00:00:00.000 2012-06-01 00:00:00.000 3
But - be careful. The simple inner join used eliminates rows, as you can see from the output. Not a problem for this set of data. But could be in the real world.
Once you have this, it is then a simple matter of picking the most recent price change. I'd probably use row_number() here again.
Hope that helps.
Regards, Iain
November 29, 2012 at 3:19 pm
irobertson, I agree with this would usually smell like a HW question, but for homebrew I can assure you it isn't. 🙂 Sometimes being a regular lets you simplify things down to HW levels for our benefit. 😉
Homebrew, you've got a few issues here. First, you've got no way to easy map nextrow/previousRow. We'll take care of that with ROW_NUMBER() and a cte. All a CTE does is create a one-shot view.
Also, minor adjustment, I prefer real temp tables.
IF OBJECT_ID ('tempdb..#Fruit') IS NOT NULL
DROP TABLE #Fruit
create table #Fruit(Item varchar(10) not null, price dec(6,2) not null, EntryDate datetime )
insert into #Fruit values ('Apple', 1.00, '2012-01-01')
insert into #Fruit values ('Apple', 1.00, '2012-02-01')
insert into #Fruit values ('Apple', 1.50, '2012-03-01')
insert into #Fruit values ('Apple', 1.50, '2012-04-01')
insert into #Fruit values ('Apple', 1.50, '2012-05-01')
insert into #Fruit values ('Apple', 1.75, '2012-06-01')
insert into #Fruit values ('Apple', 1.75, '2012-07-01')
insert into #Fruit values ('Apple', 1.75, '2012-08-01')
insert into #Fruit values ('Pear', .95, '2012-01-01')
insert into #Fruit values ('Pear', 1.00, '2012-02-01')
insert into #Fruit values ('Pear', 1.00, '2012-03-01')
insert into #Fruit values ('Pear', 1.00, '2012-04-01')
insert into #Fruit values ('Pear', 1.50, '2012-05-01')
insert into #Fruit values ('Pear', 1.50, '2012-06-01')
insert into #Fruit values ('Pear', 1.50, '2012-07-01')
insert into #Fruit values ('Pear', 1.50, '2012-08-01')
Now, for the Row_Number():
;WITH cte AS
(
SELECT
ROW_NUMBER() OVER ( PARTITION BY Item ORDER BY EntryDate) AS rn,
f.*
FROM
#Fruit AS f
)
SELECT * FROM cte
Simple enough, right? Now we have a way to join back from the leading entry. However, that's really just the start of our process. I'm going to use a slightly abnormal approach for this, avoiding a whole islanding scenario and letting Row_Number do the work.
So with this as our baseline:
;WITH cte AS
(
SELECT
ROW_NUMBER() OVER ( PARTITION BY Item ORDER BY EntryDate) AS rn,
ROW_NUMBER() OVER (PARTITION BY Item, Price ORDER BY EntryDate) AS rn2,
f.*
FROM
#Fruit AS f
)
SELECT * FROM cte
We now have a # that covers the range for each fruit, and then a subset that covers each price continuity. Now, we get a little zany.
The second CTE in this (the big long name) merely goes and finds the largest rn where it's the first entry for that rn2. So, you'll find the first entry of the last 'pattern'.
Next, it just links back to the original CTE to get the information.
;WITH cte AS
(
SELECT
ROW_NUMBER() OVER ( PARTITION BY Item ORDER BY EntryDate) AS rn,
ROW_NUMBER() OVER (PARTITION BY Item, Price ORDER BY EntryDate) AS rn2,
f.*
FROM
#Fruit AS f
)
, FirstEntryForLastPrice AS
(SELECT
Item,
MAX( rn) AS MaxRN
FROM
cte
WHERE
rn2 = 1
GROUP BY
Item
)
SELECT
--fe.*,
cte.*
FROM
FirstEntryForLastPrice AS fe
JOIN
cte
ONfe.Item = cte.Item
AND fe.MaxRN = cte.RN
Does that help any?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 29, 2012 at 3:23 pm
I operate much better at HW level 🙂
I guess I should have glanced at the visit stats before throwing around my egregious accusations ;-D
November 29, 2012 at 5:35 pm
Goes to show that just because someone has a lot of visits doesn't mean they know anything :hehe:
Thanks for the explanations ...... getting so close ...
"... Once you have this, it is then a simple matter of picking the most recent price change...."
Simple for you 😀
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply