SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
Bhuvnesh
Bhuvnesh
SSCertifiable
SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)

Group: General Forum Members
Points: 6840 Visits: 4076
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 ) ?

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Eugene Elutin
Eugene Elutin
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6314 Visits: 5478
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.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
ChrisM@Work
ChrisM@Work
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21526 Visits: 19689
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



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Eugene Elutin
Eugene Elutin
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6314 Visits: 5478
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...

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Mark Cowne
Mark Cowne
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3738 Visits: 24448
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];



____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537




ChrisM@Work
ChrisM@Work
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21526 Visits: 19689
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



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Eugene Elutin
Eugene Elutin
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6314 Visits: 5478
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




_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Eugene Elutin
Eugene Elutin
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6314 Visits: 5478
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...

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
stuff 56271
stuff 56271
SSC-Enthusiastic
SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)

Group: General Forum Members
Points: 170 Visits: 84
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
Eugene Elutin
Eugene Elutin
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6314 Visits: 5478
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

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search