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
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
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
Bhuvnesh
Bhuvnesh
SSCertifiable
SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)

Group: General Forum Members
Points: 6862 Visits: 4076
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
ColdCoffee
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4583 Visits: 5550
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
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 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
ColdCoffee
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4583 Visits: 5550
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!
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: 6316 Visits: 5478
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
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: 6316 Visits: 5478
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
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
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
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: 6316 Visits: 5478
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
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: 6316 Visits: 5478
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
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