T-sql Multiple Grouping

I have follwing data : Product Price StartDate EndDate Apples 4.9 2010-03-01 00:00:00.000 2010-03-01 00:00:00.000 Apples 4.9 2010-03-02 00:00:00.

Solution 1:

My approach.

Data:

createtable t ( producte varchar(50), 
                 price money, 
                 start_date date,
                 end_date date);

insertinto t values
( 'apple', 4.9, '2012-01-01', '2012-01-01' ),
( 'apple', 4.9, '2012-01-02', '2012-01-02' ),
( 'apple', 8, '2012-01-04', '2012-01-04' ),
( 'cat', 5, '2012-01-01', '2012-01-01' ),
( 'cat', 6, '2012-01-02', '2012-01-02' ),
( 'cat', 6, '2012-01-03', '2012-01-03' );

Query:

with start_dates as (
  select 
    t.producte, t.price, t.start_date, t.end_date, t.start_date as gr_date    
  from 
    t leftouterjoin 
    t t1 on 
        t.price = t1.price and--new
        t.producte = t1.producte and
        t.start_date = dateadd(day,1, t1.end_date )
  where t1.producte isnullunionallselect 
      t.producte, t.price, t.start_date,t. end_date, gr_date
  from
      t innerjoin 
      start_dates t1 on  
        t.price = t1.price and--new
        t.producte = t1.producte and
        t.start_date = dateadd(day,1, t1.end_date )
)
select t.producte, t.price , min( t.start_date ), max( t.end_date )
from start_dates t
groupby  t.producte, gr_date  ,t.price

Results:

|PRODUCTE|PRICE|COLUMN_2|COLUMN_3|----------------------------------------------|apple|4.9|2012-01-01|2012-01-02||apple|8|2012-01-04|2012-01-04||cat|5|2012-01-01|2012-01-01||cat|6|2012-01-02|2012-01-03|

Explanation

This is a recursive CTE expression. Base query take inital dates for each group of prices. Recursive query looks for last data with this price.

Solution 2:

SELECT  product, price, MIN(start_date), MAX(end_date)
FROM    (
        SELECT  product, price, start_date, end_date,
                ROW_NUMBER() OVER (PARTITIONBY product ORDERBY startDate) rn1,
                ROW_NUMBER() OVER (PARTITIONBY product, price ORDERBY startDate) rn2
        FROM    mytable
        ) q
GROUPBY
        product, price, rn2 - rn1
ORDERBY
        product, MIN(start_date), price

Solution 3:

Here is a SQLFiddle demo

with t2 as 
(
select t1.*,
(select count(Price) 
  from t 
  where startdate<t1.startdate 
        and Price<>t1.price
        and Product=t1.Product
)
rng  
from t as t1
)
select Product,Price,min(startDate),max(EndDate)  
from t2 groupby Product,Price,RNG
orderby3

Solution 4:

Here's a suggestion: for each row, you must find the maximum previous date for which the price is different and you Group on that. For example, for any line between 2010-03-11 and 2010-03-16, you must retrieve the date 2010-03-10 because this is the maximum previous date for which the price is different (2.5 versus 4.9). The first row(s) will return a null date but that shouldn't be a problem.

However, for a very long table, this kind of query could become very slow. Therefore, if you have some speed problem, you should look into the possibility of adding a column and use a cursor to fill it incrementally: you loop through it by date and each time you see a new price, you change its value. The final Grouping is then trivial.

Here's something:

Select Product, Price, Min(StartDate) as StartDate, PreviousDate from (
    Select product, price, StartDate, (Select max (StartDate) from table_2 t3 where t3.price <> t2.price and t3.StartDate < t2.StartDate and t3.Product = t2.Product) as previousDate
    from table_2 t2) SQ

Groupby Product, Price, PreviousDate
Orderby PreviousDate

Solution 5:

I believe this is the best-performing solution so far:

WITH Calc AS (
   SELECT*,
      Grp = DateAdd(day, -Row_Number()
         OVER (PARTITIONBY Product, Price ORDERBY StartDate), StartDate
      )
   FROM dbo.PriceHistory
)
SELECT Product, Price, FromDate =Min(StartDate), ToDate =Max(StartDate)
FROM Calc
GROUPBY Product, Price, Grp
ORDERBY FromDate;

Try this out yourself