Solved

CTE using row_number() OVER(partition by...

Posted on 2013-11-20
10
449 Views
Last Modified: 2013-11-21
I'm using sql server 2008 R2 and trying to get the latest fuel price and the first time it was sold at that price to determine an approximate price change time.

Sample table and data:

create table #fuel_sales(store_number int, fuel_grade varchar(4), sales_date_time datetime, fuel_price numeric(5,3))
go

Insert Into #fuel_sales(store_number, fuel_grade, sales_date_time, fuel_price)
values(1,'Unl','2013-11-20 05:00',3.199),
        (1,'Unl','2013-11-20 05:15',3.199),
        (1,'Unl','2013-11-20 07:00',3.219),
        (1,'Unl','2013-11-20 08:11',3.219),
        (1,'Unl','2013-11-20 08:15',3.199),
        (1,'Unl','2013-11-20 09:02',3.199),
        (1,'Prem','2013-11-20 05:08',3.499),
        (1,'Prem','2013-11-20 06:58',3.549),
        (1,'Prem','2013-11-20 08:11',3.499),
        (1,'Prem','2013-11-20 08:15',3.499),
        (1,'Prem','2013-11-20 09:22',3.499),
        (3,'Unl','2013-11-20 06:55',3.099),
        (3,'Unl','2013-11-20 07:22',3.099),
        (3,'Unl','2013-11-20 08:15',3.099),
        (3,'Unl','2013-11-20 09:12',3.159),
        (3,'Unl','2013-11-20 09:22',3.159),
        (3,'Unl','2013-11-20 10:05',3.109),
        (3,'Unl','2013-11-20 10:15',3.109)

go

query I'm using to try and get the current fuel_price with the first time it was sold at this price after the price changed.

;With t as
      (
      Select store_number
              ,fuel_grade
              ,sales_date_time
              ,fuel_price
              ,ROW_NUMBER() OVER(PARTITION BY store_number, fuel_grade, fuel_price ORDER BY sales_date_time) RowNumber
       From #fuel_sales)


       Select t.store_number
               ,t.fuel_grade
               ,last_price_change
               ,fuel_price
       From t
       Inner Join (Select store_number, fuel_grade, max(sales_date_time) as last_price_change
                         From t
                         Where RowNumber = 1
                         group by store_number, fuel_grade) x on t.store_number = x.store_number and t.fuel_grade = x.fuel_grade and t.sales_date_time = x.last_price_change
      Where RowNumber = 1
      order by store_number, fuel_grade
________________________________

-- desired results:

--store_number fuel_grade last_price_change       fuel_price
-------------- ---------- ----------------------- ---------------------------------------
--1            Prem       2013-11-20 08:11:00.000 3.499
--1            Unl        2013-11-20 08:15:00.000 3.199
--3            Unl        2013-11-20 10:05:00.000 3.109



--actual results:

--store_number fuel_grade last_price_change       fuel_price
-------------- ---------- ----------------------- ---------------------------------------
--1            Prem       2013-11-20 06:58:00.000 3.549
--1            Unl        2013-11-20 07:00:00.000 3.219
--3            Unl        2013-11-20 10:05:00.000 3.109

_________________________________

Where store_number = 3, I am getting the correct time and price. The problem is, if there was a price change to a different price and then another change back to the previous price in the same day, I am not getting the correct time or price.

Any help is appreciated!
0
Comment
Question by:edfreels
  • 3
  • 3
  • 2
  • +1
10 Comments
 
LVL 24

Expert Comment

by:chaau
ID: 39664450
I think you need to change the statement a bit, like this (see my comments in the SQL query below):
;With t as
      (
      Select store_number
              ,fuel_grade
              ,sales_date_time
              ,fuel_price
              ,ROW_NUMBER() OVER(PARTITION BY store_number, fuel_grade, fuel_price ORDER BY sales_date_time desc) RowNumber -- here you need desc, so that last sales_date_time comes as RowNumber = 1
       From #fuel_sales)
       Select t.store_number
               ,t.fuel_grade
               ,last_price_change
               ,fuel_price
       From t
       Inner Join (Select store_number, fuel_grade, 
                         min(sales_date_time) as last_price_change -- here you need min() to find the earliest date when this price has been seen
                         From t
                         Where RowNumber = 1
                         group by store_number, fuel_grade) x on t.store_number = x.store_number and t.fuel_grade = x.fuel_grade and t.sales_date_time = x.last_price_change
      Where RowNumber = 1
      order by store_number, fuel_grade

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39664451
Have you tried with DESC in the order by?

              ,ROW_NUMBER() OVER(PARTITION BY store_number, fuel_grade, fuel_price ORDER BY sales_date_time DESC) RowNumber

With that change I got these results:
| STORE_NUMBER | FUEL_GRADE |               LAST_PRICE_CHANGE | FUEL_PRICE |
|--------------|------------|---------------------------------|------------|
|            1 |       Prem | November, 20 2013 09:22:00+0000 |      3.499 |
|            1 |        Unl | November, 20 2013 09:02:00+0000 |      3.199 |
|            3 |        Unl | November, 20 2013 10:15:00+0000 |      3.109 |

Open in new window

http://sqlfiddle.com/#!3/d71d3/3
0
 
LVL 24

Expert Comment

by:chaau
ID: 39664460
@PortletPaul: what about min()? See my previous post.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39664548
yes, that too - well spotted. (btw I had not seen your post before I did)
0
 
LVL 24

Expert Comment

by:chaau
ID: 39664558
That's the problem of EE. It is just a good old html. Comparing with stackoverflow where the answers from other people appear magically as you type yours
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 1

Author Comment

by:edfreels
ID: 39665738
Thanks chaau and PortletPaul for your responses.  I have tried both of your suggestions and it still does not give me the desired results, which is the most current price and the first time that it was sold at that price since the last price change.  

These are the results I am trying to get to:

--store_number fuel_grade last_price_change       fuel_price
-------------- ---------- ----------------------- ---------------------------------------
--1            Prem       2013-11-20 08:11:00.000 3.499
--1            Unl        2013-11-20 08:15:00.000 3.199
--3            Unl        2013-11-20 10:05:00.000 3.109

Chaau, here are the results I got with your suggestion of ordering desc and taking the max date:

store_number fuel_grade last_price_change       fuel_price
------------ ---------- ----------------------- ---------------------------------------
1            Prem       2013-11-20 06:58:00.000 3.549
1            Unl        2013-11-20 08:11:00.000 3.219
3            Unl        2013-11-20 08:15:00.000 3.099

With this, all prices and times are wrong.  store_number 3 had the correct datetime and fuel_price with my original query.  

PortletPaul,

with just ordering in desc and still using max, these are the results:

store_number fuel_grade last_price_change       fuel_price
------------ ---------- ----------------------- ---------------------------------------
1            Prem       2013-11-20 09:22:00.000 3.499
1            Unl        2013-11-20 09:02:00.000 3.199
3            Unl        2013-11-20 10:15:00.000 3.109

This does get me closer to the solution, as all of the prices are correct.  But the times are all incorrect now.  

If I could similate the lead and lag functions from SQL Server 2012, I may be able to get closer.  I'm going to try to delve into that and see if I can solve it from that angle.  If you have any other suggestions to get closer, that would be great!

Thanks!
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 39666401
can you restate your problem?

what do you want  ...
for the current day/(or latest sale date for a store?)  
you want the latest price for a fuel type...
and the first (latest time in the day) that price was used ...

e.g.   today   8 am   1.00
        today    9 am   1.15
        today   10 am  1.00
        today    11 am  1.00
you want today  10am and 1.00

or something else...
0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 500 total points
ID: 39666404
try this

;with cte as ( select a.*
                     ,convert(date,sales_date_time) as Sale_date
             --      ,row_number() over (partition by store_number,fuel_grade,convert(date,sales_date_time) order by sales_date_time desc) as rn_time
                     ,row_number() over (partition by store_number,fuel_grade order by sales_date_time desc) as rn_date
                from #fuel_sales as a
             )
 ,cte1 as (  Select store_number,fuel_grade,sales_date_time,fuel_price,sale_date
               from cte
              where rn_date=1)
 , cte2 as (
 Select a.store_number,a.fuel_grade,b.start_date_time,a.fuel_price
      ,row_number() over (partition by a.store_number,a.fuel_grade order by b.sales_date_time asc) as rn
   from cte1 as a
  Inner Join cte as b
     on a.store_number=b.store_number
    and a.fuel_grade=b.fuel_grade
    and a.fuel_price=b.fuel_price
    and a.sale_date=b.sale_date
    and a.sale_date_time>=b.sale_date_time
  Where not exists (select store_number from cte as x
                     where a.store_number=x.store_number
                      and a.fuel_grade=x.fuel_grade
                      and x.sales_date_time between b.sales_date_time and a.sales_date_time
                      and x.fuel_price <> a.fuel_price)
    )
select store_number,fuel_grade,sales_date_time,fuel_price
  from cte2
 where rn=1
 order by 1,2,3
0
 
LVL 1

Author Comment

by:edfreels
ID: 39666455
Yes!  That is exactly what I was trying to do.  This gets the desired results exactly as stated! Thanks so much!
0
 
LVL 1

Author Closing Comment

by:edfreels
ID: 39666458
Thank you again for the complex solution with the additional cte!  It makes perfect sense!
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
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.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

757 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

21 Experts available now in Live!

Get 1:1 Help Now