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

Question

Post reply

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

stuff 56271

SSC Eights!

Points: 938
More actions
May 31, 2010 at 2:01 pm

#223208

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
idName PriceDateTime
5354$49.95 Value Pack0.14112010-02-18 10:35:01.680
5380$49.95 Value Pack0.14112010-02-18 11:05:01.593
5471$49.95 Value Pack0.14112010-02-18 12:50:02.263
5497$49.95 Value Pack0.14112010-02-18 13:20:01.350
5562$49.95 Value Pack0.14112010-02-18 14:35:01.377
5588$49.95 Value Pack0.14112010-02-18 15:05:01.570
5614$49.95 Value Pack0.14112010-02-18 15:35:01.360
5679$49.95 Value Pack0.14112010-02-18 16:50:01.557
5705$49.95 Value Pack0.14112010-02-18 17:20:01.400
2674$49.95 Value Pack0.15052010-02-15 05:05:29.613
2698$49.95 Value Pack0.15052010-02-15 07:05:05.537
2782$49.95 Value Pack0.15052010-02-15 11:09:15.570
2806$49.95 Value Pack0.15052010-02-15 13:05:01.603
2866$49.95 Value Pack0.15052010-02-15 18:05:02.240
2890$49.95 Value Pack0.15052010-02-15 20:05:02.310
2914$49.95 Value Pack0.15052010-02-15 22:05:02.857
2974$49.95 Value Pack0.15052010-02-16 03:05:01.583
2998$49.95 Value Pack0.15052010-02-16 05:05:12.120
89977Flower Power Top0.20072010-04-25 03:35:05.697
90103Flower Power Top0.20072010-04-25 05:50:01.513
4$49.95 Value Pack0.16932010-02-06 22:24:30.663
69$49.95 Value Pack0.16932010-02-06 23:04:38.220
95$49.95 Value Pack0.16932010-02-06 23:09:38.053
289$49.95 Value Pack0.16932010-02-07 13:00:01.783
313$49.95 Value Pack0.16932010-02-07 15:00:05.420
373$49.95 Value Pack0.16932010-02-07 20:00:02.977
397$49.95 Value Pack0.16932010-02-07 22:00:03.670
5796$49.95 Value Pack0.14112010-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

Viewing 15 posts - 1 through 15 (of 30 total)

You must be logged in to reply to this topic. Login to reply

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 1

post your problem according to the article referred in my signature to get faster response

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

ColdCoffee SSC-Dedicated Points: 39971 More actions · Answer 2

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

stuff 56271 SSC Eights! Points: 938 More actions · Answer 3

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

ColdCoffee SSC-Dedicated Points: 39971 More actions · Answer 4

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 :pinch:) 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,MAXIFROM 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!

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 5

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?

_____________________________________________
"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[/url]

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 6

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!

_____________________________________________
"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[/url]

stuff 56271 SSC Eights! Points: 938 More actions · Answer 7

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

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 8

I guess, using "quirky" update method will give better performance. I cannot show it today, but will do it tomorrow

_____________________________________________
"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[/url]

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 9

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

_____________________________________________
"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[/url]

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 10

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 SSC Guru Points: 59322 More actions · Answer 11

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[/url]

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 12

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

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 13

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[/url]

Mark Cowne One Orange Chip Points: 26944 More actions · Answer 14

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