Solved

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

Posted on 2013-11-20
10
469 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
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 
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
 
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

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

821 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