SQLServerCentral

TSQL Return start/end price and start/end datetime by product name.


https://www.sqlservercentral.com/Forums/Topic930464.aspx

By stuff 56271 - Monday, May 31, 2010 7:01 AM

I am trying to return a result set and I can not quite get my head around it at the moment. I know the answer will be simple but I can not seem to get it right as I am unsure of the joins I need.

I have data in a table like this

id int,
name nvarchar(255),
Price decimal(9,4),
DateTime datetime

Here is an example of the data

id Name Price DateTime
5354 $49.95 Value Pack 0.1411 2010-02-18 10:35:01.680
5380 $49.95 Value Pack 0.1411 2010-02-18 11:05:01.593
5471 $49.95 Value Pack 0.1411 2010-02-18 12:50:02.263
5497 $49.95 Value Pack 0.1411 2010-02-18 13:20:01.350
5562 $49.95 Value Pack 0.1411 2010-02-18 14:35:01.377
5588 $49.95 Value Pack 0.1411 2010-02-18 15:05:01.570
5614 $49.95 Value Pack 0.1411 2010-02-18 15:35:01.360
5679 $49.95 Value Pack 0.1411 2010-02-18 16:50:01.557
5705 $49.95 Value Pack 0.1411 2010-02-18 17:20:01.400
2674 $49.95 Value Pack 0.1505 2010-02-15 05:05:29.613
2698 $49.95 Value Pack 0.1505 2010-02-15 07:05:05.537
2782 $49.95 Value Pack 0.1505 2010-02-15 11:09:15.570
2806 $49.95 Value Pack 0.1505 2010-02-15 13:05:01.603
2866 $49.95 Value Pack 0.1505 2010-02-15 18:05:02.240
2890 $49.95 Value Pack 0.1505 2010-02-15 20:05:02.310
2914 $49.95 Value Pack 0.1505 2010-02-15 22:05:02.857
2974 $49.95 Value Pack 0.1505 2010-02-16 03:05:01.583
2998 $49.95 Value Pack 0.1505 2010-02-16 05:05:12.120
89977 Flower Power Top 0.2007 2010-04-25 03:35:05.697
90103 Flower Power Top 0.2007 2010-04-25 05:50:01.513
4 $49.95 Value Pack 0.1693 2010-02-06 22:24:30.663
69 $49.95 Value Pack 0.1693 2010-02-06 23:04:38.220
95 $49.95 Value Pack 0.1693 2010-02-06 23:09:38.053
289 $49.95 Value Pack 0.1693 2010-02-07 13:00:01.783
313 $49.95 Value Pack 0.1693 2010-02-07 15:00:05.420
373 $49.95 Value Pack 0.1693 2010-02-07 20:00:02.977
397 $49.95 Value Pack 0.1693 2010-02-07 22:00:03.670
5796 $49.95 Value Pack 0.1411 2010-02-18 19:05:01.800

What I want to return is the start price and end price and the start and end dates for each product (defined by the name column) and the last item in each product group be null for the end datetime

I know I must perform a join but I really am lacking the smarts right now to do this. If anyone can point me in the right direction that would be great

chris
By Bhuvnesh - Monday, May 31, 2010 3:44 PM

post your problem according to the article referred in my signature to get faster response
By ColdCoffee - Monday, May 31, 2010 4:05 PM

Would this help you:


SELECT name, MIN(price) min_pice , MAX(price) max_pice, min(date) min_date, max(date) max_date
from table
group by name
order by name



As said by Bhuvnesh, please go thro the article he referred and post data in a readily-consumable format along with the clear-cut desired output..
By ColdCoffee - Monday, May 31, 2010 8:01 PM

hi there, here is one piece that might interest you! This is **NOT** the optimal solution for the problem, but for now, you can have this. The desired output can easily be acheived by ROW_NUMBER functions, but as i have some time constraint, i cant sit with that logic.

First lets set-up the dev environment: (Please read thro the article Bhuvnesh has referred and in future post the sample data as i have posted here)


SET DATEFORMAT DMY

IF OBJECT_ID('TEMPDB..#Temp') IS NOT NULL
DROP TABLE #Temp

CREATE TABLE #Temp
(
Name VARCHAR(15),
Price NUMERIC(5,2),
Date DATETIME
)

INSERT INTO #Temp (Name ,Price ,Date)
SELECT 'ProductA', 10.45, '1/June/2010 01:00'
UNION ALL SELECT 'ProductB', 10.90, '1/June/2010 01:00'
UNION ALL SELECT 'ProductA', 10.45, '1/June/2010 02:00'
UNION ALL SELECT 'ProductB', 10.90, '1/June/2010 02:00'
UNION ALL SELECT 'ProductA', 10.45, '1/June/2010 03:00'
UNION ALL SELECT 'ProductB', 10.90, '1/June/2010 03:00'
UNION ALL SELECT 'ProductA', 10.65, '1/June/2010 04:00'
UNION ALL SELECT 'ProductB', 10.90, '1/June/2010 04:00'



Now for the code (cumbersome code to be honest :pinchSmile that will produce teh desired result:


;WITH GROUP_DATA AS
(
SELECT NAME,
MIN(price) min_price ,
MAX(price) max_price
FROM #Temp
GROUP BY
NAME
),
SINGLETON AS
(
SELECT
GD.NAME , GD.min_price , MIN(T.DATE) MIN_DATE, NULL max_price , NULL max_date
FROM
GROUP_DATA GD
INNER JOIN
#Temp T ON T.NAME = GD.NAME AND T.price = GD.max_price
WHERE
GD.min_price = GD.max_price
GROUP BY
GD.NAME , GD.min_price
),
DOUBLET AS
(
SELECT GD.NAME , GD.min_price ,
MIN (CASE WHEN T.PRICE = GD.MIN_PRICE THEN T.DATE END ) MINI,
GD.max_price,
MAX (CASE WHEN T.PRICE = GD.MAX_PRICE THEN T.DATE END ) MAXI
FROM
GROUP_DATA GD
INNER JOIN
#Temp T ON T.NAME = GD.NAME
WHERE
GD.min_price <> GD.max_price
GROUP BY
GD.NAME , GD.min_price , GD.max_price
),
MAXI_CAB AS
(
SELECT
GD.NAME , GD.max_price , MAX(T.DATE) max_date, NULL min_price , NULL MIN_DATE
FROM
GROUP_DATA GD
INNER JOIN
#Temp T ON T.NAME = GD.NAME AND T.price = GD.max_price
WHERE
GD.min_price <> GD.max_price
GROUP BY
GD.NAME , GD.max_price
),
UNION_ALL AS
(
SELECT NAME , min_price , MIN_DATE, max_price , max_date FROM SINGLETON
UNION ALL
SELECT NAME , min_price , MINI, max_price,MAXI FROM DOUBLET
UNION ALL
SELECT NAME , max_price , max_date , min_price , MIN_DATE FROM MAXI_CAB
)
SELECT NAME , min_price , MIN_DATE, max_price , max_date FROM UNION_ALL
ORDER BY NAME



Hope this gets you started, at the least! I will come up with an optimum, should time permit me to relax!
By Eugene Elutin - Tuesday, June 1, 2010 2:16 AM

What about if the price changes back to the same value as it was before:


INSERT INTO #Temp (Name ,Price ,Date)
SELECT 'ProductA', 10.45, '1/June/2010 07:00'


Do you want another record to appear in your results?
By Eugene Elutin - Tuesday, June 1, 2010 2:26 AM

After looking into use of CROSS & OUTER APPLY (my curiosity was influenced by Paul White NZ who suggested more efficient solution for one of the last topics...)
I can suggest the following query (it will return record for every price change occurance):



select bs.Name, bs.Price as StartPrice, bs.Date as StartDT, etp.NextPrice, etp.EndDT
from #Temp bs
outer apply (select top 1 et.Name
,et.Price as NextPrice
,et.Date as EndDt
from #Temp et where et.Name = bs.Name and et.Date > bs.Date
) etp
where bs.Price != etp.NextPrice or etp.NextPrice is null
order by bs.Name, bs.Date






I've started to like APPLY thing, thanks to Paul White NZ!
By Eugene Elutin - Tuesday, June 1, 2010 8:02 AM

I guess, using "quirky" update method will give better performance. I cannot show it today, but will do it tomorrow
By stuff 56271 - Monday, May 31, 2010 5:26 PM

Hi this is not quite what I was after. Basically I am after something like this:

Say here is my input data


Name Price Date/Time
ProductA 10.45 1/june/2010 01:00
ProductB 10.90 1/June/2010 01:00
ProductA 10.45 1/june/2010 02:00
ProductB 10.90 1/June/2010 02:00
ProductA 10.45 1/june/2010 03:00
ProductB 10.90 1/June/2010 03:00
ProductA 10.65 1/june/2010 04:00
ProductB 10.90 1/June/2010 04:00

Now Product B has not changed price at all, but product A changed price at 04:00

What I was wanting is to get the Name, Min Price and Min DateTime, NextPrice, Next Price DateTime

So the record i need returned is

ProductA,10.45,1/June/2010 01:00,10.65,1/june/2010 04:00
ProductA,10.65,1/june/2010 04:00,null,null
ProductB,10.90,1/june/2010 01:00,null,null

So logically it is return the product name and the start price, and the start time, and also the next price and the next price time. This is not the maximum price, or the maximum time, just the next price and time.

My table collects prices every 15 minutes, but the prices only change every few days (but can be every few hours)

So I am only after the actual prices and not the data in the middle where nothing changes.

I hope this makes sense.

cheers

Chris
By stuff 56271 - Tuesday, June 1, 2010 6:07 AM

Thanks for the query - I will try to disect it and see how it works.

Speed wise is pretty slow - 55 minutes to run on 137,000 rows

But it gives me the right idea to investigate

thanks for your time

cheers

chris
By Mark Cowne - Wednesday, June 2, 2010 1:50 AM

Another one to try

WITH CTE1 AS (
SELECT Name ,Price ,Date,
ROW_NUMBER() OVER(PARTITION BY Name ORDER BY DATE) AS rn1,
ROW_NUMBER() OVER(PARTITION BY Name,Price ORDER BY DATE) AS rn2
FROM #Temp),
CTE2 AS (
SELECT Name,Price AS [Min Price],MIN(Date) AS [Min DateTime],MAX(rn1) AS maxRN
FROM CTE1
GROUP BY Name,Price,rn2-rn1)
SELECT a.Name,a.[Min Price],a.[Min DateTime],
b.Price AS [NextPrice],
b.Date AS [Next Price DateTime]
FROM CTE2 a
LEFT OUTER JOIN CTE1 b ON b.Name=a.Name AND b.rn1=a.maxRN+1 AND b.Date>a.[Min DateTime]
ORDER BY a.Name,a.[Min DateTime];

By ChrisM@Work - Wednesday, June 2, 2010 2:06 AM

That works Mark, nice one. So does this:
;WITH OrderedData AS (
SELECT Seq = ROW_NUMBER() OVER (ORDER BY [Name], [DateTime], Price),
[Name], Price, [DateTime]
FROM #Temp
),
Calculator AS (
SELECT fr.Seq, fr.[Name], fr.Price, fr.[DateTime],
Band = CAST('1' AS INT)
FROM OrderedData fr
WHERE fr.Seq = 1
UNION ALL
SELECT tr.Seq, tr.[Name], tr.Price, tr.[DateTime],
Band = CASE WHEN tr.[Name] <> lr.[Name] OR tr.Price <> lr.Price THEN Band+1 ELSE Band END
FROM Calculator lr
INNER JOIN OrderedData tr ON tr.Seq = lr.Seq+1
)
SELECT a.[Name], a.Price, a.StartDate, b.StartDate AS EndDate
FROM (
SELECT Band, [Name], Price, MIN([DateTime]) AS StartDate
FROM Calculator
GROUP BY Band, [Name], Price
) a
LEFT JOIN (
SELECT Band, [Name], Price, MIN([DateTime]) AS StartDate
FROM Calculator
GROUP BY Band, [Name], Price
) b ON b.[Name] = a.[Name] AND b.Band = a.Band + 1
ORDER BY a.[Name], a.Price

By ChrisM@Work - Tuesday, June 1, 2010 11:52 PM

This should be reasonably quick, and it's simple to understand and use, too:
DROP TABLE #Temp
CREATE TABLE #Temp (id INT, [Name] VARCHAR(40), Price Money, [DateTime] DATETIME)
INSERT INTO #Temp (id, [Name], Price, [DateTime])
SELECT 4, '$49.95 Value Pack', 0.1693, 'Feb 6 2010 10:24PM' UNION ALL -- start of price/product
SELECT 69, '$49.95 Value Pack', 0.1693, 'Feb 6 2010 11:04PM' UNION ALL
SELECT 95, '$49.95 Value Pack', 0.1693, 'Feb 6 2010 11:09PM' UNION ALL
SELECT 289, '$49.95 Value Pack', 0.1693, 'Feb 7 2010 1:00PM' UNION ALL
SELECT 313, '$49.95 Value Pack', 0.1693, 'Feb 7 2010 3:00PM' UNION ALL
SELECT 373, '$49.95 Value Pack', 0.1693, 'Feb 7 2010 8:00PM' UNION ALL
SELECT 397, '$49.95 Value Pack', 0.1693, 'Feb 7 2010 10:00PM' UNION ALL -- end of price/product
SELECT 2674, '$49.95 Value Pack', 0.1505, 'Feb 15 2010 5:05AM' UNION ALL -- start of price/product
SELECT 2698, '$49.95 Value Pack', 0.1505, 'Feb 15 2010 7:05AM' UNION ALL
SELECT 2782, '$49.95 Value Pack', 0.1505, 'Feb 15 2010 11:09AM' UNION ALL
SELECT 2806, '$49.95 Value Pack', 0.1505, 'Feb 15 2010 1:05PM' UNION ALL
SELECT 2866, '$49.95 Value Pack', 0.1505, 'Feb 15 2010 6:05PM' UNION ALL
SELECT 2890, '$49.95 Value Pack', 0.1505, 'Feb 15 2010 8:05PM' UNION ALL
SELECT 2914, '$49.95 Value Pack', 0.1505, 'Feb 15 2010 10:05PM' UNION ALL
SELECT 2974, '$49.95 Value Pack', 0.1505, 'Feb 16 2010 3:05AM' UNION ALL
SELECT 2998, '$49.95 Value Pack', 0.1505, 'Feb 16 2010 5:05AM' UNION ALL -- end of price/product
SELECT 5354, '$49.95 Value Pack', 0.1411, 'Feb 18 2010 10:35AM' UNION ALL -- start of price/product
SELECT 5380, '$49.95 Value Pack', 0.1411, 'Feb 18 2010 11:05AM' UNION ALL
SELECT 5471, '$49.95 Value Pack', 0.1411, 'Feb 18 2010 12:50PM' UNION ALL
SELECT 5497, '$49.95 Value Pack', 0.1411, 'Feb 18 2010 1:20PM' UNION ALL
SELECT 5562, '$49.95 Value Pack', 0.1411, 'Feb 18 2010 2:35PM' UNION ALL
SELECT 5588, '$49.95 Value Pack', 0.1411, 'Feb 18 2010 3:05PM' UNION ALL
SELECT 5614, '$49.95 Value Pack', 0.1411, 'Feb 18 2010 3:35PM' UNION ALL
SELECT 5679, '$49.95 Value Pack', 0.1411, 'Feb 18 2010 4:50PM' UNION ALL
SELECT 5705, '$49.95 Value Pack', 0.1411, 'Feb 18 2010 5:20PM' UNION ALL
SELECT 5796, '$49.95 Value Pack', 0.1411, 'Feb 18 2010 7:05PM' UNION ALL -- OPEN end of price/product
SELECT 89977, 'Flower Power Top', 0.2007, 'Apr 25 2010 3:35AM' UNION ALL -- start of price/product
SELECT 90103, 'Flower Power Top', 0.2007, 'Apr 25 2010 5:50AM' -- OPEN end of price/product


;WITH AggregatedData AS (
SELECT Seq = ROW_NUMBER() OVER (PARTITION BY [Name] ORDER BY MIN([DateTime])),
[Name], Price, MIN([DateTime]) AS StartDate
FROM #Temp
GROUP BY [Name], Price
)
SELECT a.Name, a.Price, a.StartDate, b.StartDate AS EndDate
FROM AggregatedData a
LEFT JOIN AggregatedData b ON b.Name = a.Name AND b.seq = a.Seq+1
ORDER BY a.[Name], a.Price

By Eugene Elutin - Wednesday, June 2, 2010 12:41 AM

Chris Morris-439714 (6/2/2010)
This should be reasonably quick, and it's simple to understand and use, too:
...


Unfortunately, your version will not work for situations where the price returns back to what it was after some time. Try to add another record into your test table:



INSERT INTO #Temp SELECT 4444, '$49.95 Value Pack', 0.1693, 'Feb 20 2010 10:24PM'




Sorry...
By Eugene Elutin - Tuesday, June 1, 2010 10:22 PM

As promised, another version. It contains much more code, but it's probably the fastest way to do what you require:



--We need to copy the data from the master table to work table with descending order by date
--and required additional columns
--As update is forward operation, it is possible to set previous date for the current record,
--other than next date.
select Name
,Date As NewDate
,Price As NewPrice
,CAST(null as NUMERIC(5,2)) As PrevPrice
,CAST(null as DATETIME) As PrevDate
into #work
from #temp
order by Name, Date DESC

-- clustered index will enforce order of update and help performance
-- (I'm not sure if data in your table will allow to create unique index of Name/Date combination.
-- If it's unique, then change the following statement to "create unique clustered index")
create clustered index cix_#work on #work(Name, NewDate DESC)

-- we need variables for storing state
declare @Product VARCHAR(15)
declare @NewPrice NUMERIC(5,2)
declare @PrevPrice NUMERIC(5,2)
declare @NewDate DATETIME
declare @PrevDate DATETIME

-- here we will find and set previous different price and relevant date for each of the pricing record
UPDATE #WORK
SET @PrevPrice = PrevPrice = case when @Product = Name and @NewPrice != NewPrice then @NewPrice when @Product != Name then null else @PrevPrice end
,@PrevDate = PrevDate = case when @Product = Name and @NewPrice != NewPrice then @NewDate when @Product != Name then null else @PrevDate end
,@NewDate = case when @Product = Name or @Product is null then NewDate else null end
,@NewPrice = case when @Product = Name or @Product is null then NewPrice else null end
,@Product = Name
OPTION (MAXDOP 1)

-- and here is a final query
select Name
,NewPrice AS StartPrice
,MIN(NewDate) AS StartDate
,PrevPrice AS EndPrice
,MIN(PrevDate) AS EndDate
from #Work
group by Name, NewPrice, PrevPrice
order by Name, StartDate ASC


By Eugene Elutin - Wednesday, June 2, 2010 2:55 AM

This one, does return proper results, However, on the given dataset sample, it performs 3 scans & 26 logical reads (incl. 24 reads of CTE worktable) vs 34 scans but only 7 logical reads. If you double the test data, then the CTE method will perform twice as more of reads while the number of reads using "quirky update" will stay the same. Inreasing the volume of data will stop SQL to perform logical reads of CTE worktable and number of reads in "quirky update" increases.
I guess, it should be tested on the real set of data to check what performs best.
I also think, that with proper indices (for example unique clustered on Name and Date), it is possible to achieve the acceptable performance of the version (revised & fixed) which uses APPLY, and, from my personal point of view is the most qute one :-D:


select bs.Name
,bs.Price as StartPrice
,Min(bs.Date) as StartDT
,etp.NextPrice
,Min(etp.EndDT) as EndDT
from #Temp bs
outer apply (select top 1 et.Name
,et.Price as NextPrice
,et.Date as EndDt
from #Temp et
where et.Name = bs.Name and et.Price != bs.Price and et.Date > bs.Date
order by et.Date asc
) etp
group by bs.Name, bs.Price, etp.NextPrice
order by bs.Name, StartDT


By Eugene Elutin - Wednesday, June 2, 2010 3:06 AM

I need to clarify :
elutin (6/2/2010)
This one, does return proper results, However, on the given dataset sample, it performs 3 scans & 26 logical reads (incl. 24 reads of CTE worktable) vs 34 scans but only 7 logical reads. ...



This should read as:

This one (submitted by Mark-101232), does return proper results, However, on the given dataset sample, it performs 3 scans & 26 logical reads (incl. 24 reads of CTE worktable) vs 4 scans but only 7 logical reads by "quirky update" method.
...

Also, Chris-Morris version of CTE just on 29 test data rows in the #Temp performs :
Table 'Worktable' (CTE). Scan count 5, logical reads 369
Table '#Temp'. Scan count 60, logical reads 60

All versions should be tested against the real data. Would be interesting to know which one performs best...
By Eugene Elutin - Wednesday, June 2, 2010 11:10 PM

I couldn't believe your performance figures and I've desided to test myself.
After loading 140707 records into PowerItem table and adding index on PowerUpdateID (which I'm sure you have, otherwise your view will be always slow) I have run the "quirky" update and got 267 rows as results in 3 seconds. This result matches Bhuvnesh's 267 rows and one row short of Mark-101232 268 rows.
Where this extra row comes from? It does come from situation when the same price change happens multiple times: from 1750 to 1700 then from 1700 to 1750 and then again from 1750 to 1700. Therefore small fix in "quirky" update is required in the final query:

select Name
,NewPrice AS StartPrice
,MIN(NewDate) AS StartDate
,PrevPrice AS EndPrice
,PrevDate AS EndDate
from #Work
group by Name, NewPrice, PrevPrice, PrevDate
order by Name, StartDate ASC


I am running tests in Express edition on local pc, so my tempdb is on the same drive as main db. In real life, tempdb usually located on dedicated disk, which make additional performance benefits in relation of temp tables.
Looking into similar perfomance figures Mark CTE version is defenetly a winner as it's more elegant.
BTW. Version with OUTER APPLY is really slow in this case due to use of non-equal conditions and view which has no helpful indices.
Cheers,
Eugene
By Eugene Elutin - Tuesday, June 1, 2010 11:14 PM

Bhuvnesh (6/2/2010)
elutin (6/2/2010)
As promised, another version. It contains much more code, but it's probably the fastest way to do what you require:



...
,@Product = Name
OPTION (MAXDOP 1)

...






Do we need here (maxdop 1 ) ?


Yes, it is a "must have" one. In case of parallel processing the state of variables cannot be guaranteed.
The full explanation of method can be found in http://www.sqlservercentral.com/articles/T-SQL/68467/ nice article by Jeff Moden.
By Bhuvnesh - Tuesday, June 1, 2010 10:50 PM

elutin (6/2/2010)
As promised, another version. It contains much more code, but it's probably the fastest way to do what you require:



--We need to copy the data from the master table to work table with descending order by date
--and required additional columns
--As update is forward operation, it is possible to set previous date for the current record,
--other than next date.
select Name
,Date As NewDate
,Price As NewPrice
,CAST(null as NUMERIC(5,2)) As PrevPrice
,CAST(null as DATETIME) As PrevDate
into #work
from #temp
order by Name, Date DESC

-- clustered index will enforce order of update and help performance
-- (I'm not sure if data in your table will allow to create unique index of Name/Date combination.
-- If it's unique, then change the following statement to "create unique clustered index")
create clustered index cix_#work on #work(Name, NewDate DESC)

-- we need variables for storing state
declare @Product VARCHAR(15)
declare @NewPrice NUMERIC(5,2)
declare @PrevPrice NUMERIC(5,2)
declare @NewDate DATETIME
declare @PrevDate DATETIME

-- here we will find and set previous different price and relevant date for each of the pricing record
UPDATE #WORK
SET @PrevPrice = PrevPrice = case when @Product = Name and @NewPrice != NewPrice then @NewPrice when @Product != Name then null else @PrevPrice end
,@PrevDate = PrevDate = case when @Product = Name and @NewPrice != NewPrice then @NewDate when @Product != Name then null else @PrevDate end
,@NewDate = case when @Product = Name or @Product is null then NewDate else null end
,@NewPrice = case when @Product = Name or @Product is null then NewPrice else null end
,@Product = Name
OPTION (MAXDOP 1)

-- and here is a final query
select Name
,NewPrice AS StartPrice
,MIN(NewDate) AS StartDate
,PrevPrice AS EndPrice
,MIN(PrevDate) AS EndDate
from #Work
group by Name, NewPrice, PrevPrice
order by Name, StartDate ASC






Do we need here (maxdop 1 ) ?
By stuff 56271 - Wednesday, June 2, 2010 8:50 PM

Hi All,

I have looked at your solutions and tried them against my data and here are the results.

Bhuvnesh

Your code worked fine, quite fast - 11 Seconds, 267 rows

Table 'PowerUpdate'. Scan count 5, logical reads 88, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'PowerItem'. Scan count 5, logical reads 1820, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(141337 row(s) affected)
Table '#work_______________________________________________________________________________________________________________000000000170'. Scan count 4, logical reads 4904, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#work_______________________________________________________________________________________________________________000000000170'. Scan count 1, logical reads 1275, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(141337 row(s) affected)

(267 row(s) affected)
Table '#work_______________________________________________________________________________________________________________000000000170'. Scan count 5, logical reads 1274, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Warning: Null value is eliminated by an aggregate or other SET operation.


Chris Morris-439714 (1:52pm)

No end price in your query, 240 rows returned, results not accurate

(240 row(s) affected)
Table 'PowerUpdate'. Scan count 10, logical reads 176, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'PowerItem'. Scan count 10, logical reads 3640, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Here are the first two rows of the results

$49.95 Value Pack 0.1411 2010-02-17 00:05:11.623 2010-03-21 09:20:07.430
$49.95 Value Pack 0.1491 2010-02-08 13:00:02.357 2010-02-11 00:05:09.663

The First Row has the wrong End Date


Mark-101232

Your code is fast and apprears accurate, 268 row(s) in ~6 seconds

Table 'PowerUpdate'. Scan count 10, logical reads 176, physical reads 6, read-ahead reads 18, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'PowerItem'. Scan count 10, logical reads 3622, physical reads 24, read-ahead reads 1632, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Chris Morris-439714 : 4:06pm

Sorry but this did not work and failed with the following after 1 minute 20 seconds.

Msg 530, Level 16, State 1, Line 4
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.


Eugene Elutin

Your code had not completed after 14 minutes


For anyone who wants the real data that I was working with you can download the data from here.

http://blog.crowe.co.nz/attachments/powerdata.rar

I really appreciate the time you all have put into this query. I find it very cool to see the different solutions and hope one day I will be able to help others as you have done with me.

At this time Marks' code is fast and looks accurate, thanks very much.

Cheers

Chris
By stuff 56271 - Thursday, June 3, 2010 7:09 AM

I made a small mistake on my test results in that I said that the first result from was "Bhuvnesh's" but it was in fact from "Cold Coffee"

I did not notice the quote and just grabbed the wrong name

Cheers

Chris
By Mark Cowne - Wednesday, June 23, 2010 11:19 PM

stuff 56271 (6/22/2010)
Hi again

I just wanted to let you know that I put the query I liked the most in this thread into a blog post.

http://blog.crowe.co.nz/blog/archive/2010/06/22/SQL-Server--TSQL-Return-startend-price-and-startend-date.aspx

The reason was that I appreciated the solution but did not fully understand why it works (the one that I choose)

So I tried to break it down into steps and hence the post came about.

Thanks again for your help

chris


Great explanation and thanks for the feedback.

Regards

Mark
By scott.pletcher - Wednesday, June 23, 2010 5:59 AM

NOTE: This may be obsolete by now, I got very busy and just now able to spend a quick couple of minutes on this.


Please try this, testing it on a larger batch of data.

If you have have/add index of (Name, Price, Datetime) in that exact order, this query should be super-fast. Even without that, hopefully it will be reasonable :-) .

If not, you might have to put the CTE into a temp table so that it can be indexed.



;WITH CtePriceDate AS (
SELECT Name, Price, MIN(Datetime) AS Datetime,
ROW_NUMBER() OVER (ORDER BY Name, Price, MIN(Datetime)) AS RowNumber
FROM #Temp
GROUP BY Name, Price
)
SELECT cpd1.Name, cpd1.Price AS MinPrice, cpd1.Datetime AS MinDate,
cpd2.Price AS NextPrice, cpd2.Datetime AS NextDatetime
FROM CtePriceDate cpd1
LEFT OUTER JOIN CtePriceDate cpd2 ON cpd1.Name = cpd2.Name AND
cpd1.RowNumber = cpd2.RowNumber - 1

By scott.pletcher - Wednesday, June 23, 2010 10:12 AM

Sorry about that. I don't have much time to look ... I just do this for a quick minute or two when I need a break. This is my break! -- am I a database-geek or what!! :-)


Anyway, please try code below. I think it will work, but I'm not sure about how it will perform. Please let me know on both fronts.


--add another row so that the price goes back to an earlier price
--but at a later date, e.g., from $4 to $5 and then back to $4 (!)


INSERT INTO #Temp (id, [Name], Price, [DateTime])
SELECT 2998, '$49.95 Value Pack', 0.1505, 'Feb 22 2010 22:05'

;WITH CteDatePrice AS (
SELECT Name, Datetime, Price,
ROW_NUMBER() OVER (PARTITION BY Name ORDER BY Datetime) AS RowNumber
FROM #Temp
), CteMinPriceDate AS (
SELECT cdp1.Name, cdp1.Price AS MinPrice, cdp1.Datetime AS MinDate,
ROW_NUMBER() OVER (ORDER BY cdp1.Name, cdp1.Datetime) AS RowNumber
FROM CteDatePrice cdp1
WHERE RowNumber = 1 OR NOT EXISTS(
SELECT 1
FROM CteDatePrice cdp2
WHERE cdp2.Name = cdp1.Name
AND cdp2.RowNumber = cdp1.RowNumber - 1
AND cdp2.Price = cdp1.Price)
)
SELECT cmpd1.Name, cmpd1.MinPrice, cmpd1.MinDate,
cmpd2.MinPrice AS NextPrice, cmpd2.MinDate AS NextDate
FROM CteMinPriceDate cmpd1
LEFT OUTER JOIN CteMinPriceDate cmpd2 ON cmpd2.Name = cmpd1.Name AND
cmpd2.RowNumber = cmpd1.RowNumber + 1

By scott.pletcher - Wednesday, June 23, 2010 11:38 AM

Yeah, sorry, I knew the first CTE was a killer, but I just didn't have any time today.
By scott.pletcher - Wednesday, June 23, 2010 12:28 PM

As mentioned already, assuming a proper index -- (name, date) INCLUDE price, in that order -- code below should work OK, although I still don't like how poorly the optimizer handles the query plan.


;WITH CteMinPriceDate AS (
SELECT t1.Name, t1.date AS Mindate, t1.Price AS MinPrice,
ROW_NUMBER() OVER (ORDER BY t1.Name, t1.date) AS RowNumber
FROM #Temp t1
WHERE price <> ISNULL((
SELECT TOP 1 t2.price
FROM #Temp t2
WHERE t2.Name = t1.Name
AND t2.date < t1.date
ORDER BY date DESC), -999)
)
SELECT cmpd1.Name, cmpd1.MinPrice, cmpd1.Mindate,
cmpd2.MinPrice AS NextPrice, cmpd2.Mindate AS NextDate
FROM CteMinPriceDate cmpd1
LEFT OUTER JOIN CteMinPriceDate cmpd2 ON cmpd2.Name = cmpd1.Name AND
cmpd2.RowNumber = cmpd1.RowNumber + 1


The query plan indicates it's worse than the other CTE version, but the execution plan analysis indicates it's better. Unsure

If you've got time, I'd really appreciate knowing how it performed on your actual data :-) .
By stuff 56271 - Wednesday, June 23, 2010 1:43 PM

I killed your last one scott after 14 minutes.

Just to be sure I ran the original one again from Mark-101232

WITH CTE1 AS (
SELECT Name ,Price ,DateTime,
ROW_NUMBER() OVER(PARTITION BY Name ORDER BY DATETime) AS rn1,
ROW_NUMBER() OVER(PARTITION BY Name,Price ORDER BY DATETime) AS rn2
FROM dbo.vPowerData),
CTE2 AS (
SELECT Name,Price AS [Min Price],MIN(DateTime) AS [Min DateTime],MAX(rn1) AS maxRN
FROM CTE1
GROUP BY Name,Price,rn2-rn1)
SELECT a.Name,a.[Min Price],a.[Min DateTime],
b.Price AS [NextPrice],
b.DateTime AS [Next Price DateTime]
FROM CTE2 a
LEFT OUTER JOIN CTE1 b ON b.Name=a.Name AND b.rn1=a.maxRN+1 AND b.DateTime>a.[Min DateTime]
ORDER BY a.Name,a.[Min DateTime];


It took 5 seconds

Chris
By stuff 56271 - Wednesday, June 23, 2010 11:05 AM

I gave your new version a go Scott and I killed it after 40 minutes.

Chris
By stuff 56271 - Wednesday, June 23, 2010 8:49 AM

Hi Scott

I tried your code and got a few issues if I sort by Name, MinDate as can be seen below.

The first row the next Date Time is actually after the 2nd rows's min date whereas they should be the same.

Name MinPrice MinDate NextPrice NextDatetime
$49.95 Value Pack 0.1693 2010-02-06 22:24:30.663 0.1716 2010-03-21 09:20:07.430
$49.95 Value Pack 0.1491 2010-02-08 13:00:02.357 0.1505 2010-02-12 00:05:09.017
$49.95 Value Pack 0.1518 2010-02-11 00:05:09.663 0.1556 2010-03-24 00:05:02.980
$49.95 Value Pack 0.1505 2010-02-12 00:05:09.017 0.1518 2010-02-11 00:05:09.663

It should have been

Name Min Price Min DateTime NextPrice Next Price DateTime
$49.95 Value Pack 0.1693 2010-02-06 22:24:30.663 0.1491 2010-02-08 13:00:02.357
$49.95 Value Pack 0.1491 2010-02-08 13:00:02.357 0.1518 2010-02-11 00:05:09.663
$49.95 Value Pack 0.1518 2010-02-11 00:05:09.663 0.1505 2010-02-12 00:05:09.017
$49.95 Value Pack 0.1505 2010-02-12 00:05:09.017 0.1411 2010-02-17 00:05:11.623
$49.95 Value Pack 0.1411 2010-02-17 00:05:11.623 0.1716 2010-03-21 09:20:07.430
$49.95 Value Pack 0.1716 2010-03-21 09:20:07.430 0.1411 2010-03-22 10:50:03.027

Thanks for the post

Chris
By stuff 56271 - Thursday, June 3, 2010 7:12 AM

Thanks Eugene Elutin

I will check the missing index and look over the TSQL all of you have provided.

It is an idea I need to get better at

cheers

chris
By stuff 56271 - Tuesday, June 22, 2010 3:22 PM

Hi again

I just wanted to let you know that I put the query I liked the most in this thread into a blog post.

http://blog.crowe.co.nz/blog/archive/2010/06/22/SQL-Server--TSQL-Return-startend-price-and-startend-date.aspx

The reason was that I appreciated the solution but did not fully understand why it works (the one that I choose)

So I tried to break it down into steps and hence the post came about.

Thanks again for your help

chris