August 22, 2006 at 8:36 am
Can a query be written that produces the results as described below?
Table A
Date
Price
Data looks like this
1/1/07 100
1/2/07 100
1/3/07 100
1/4/07 200
1/5/07 200
1/6/07 100
1/7/07 100
1/8/07 200
1/9/07 200
1/10/07 200
Want a query that returns results like this
1/1/07 – 1/3/07 100
1/4/07 – 1/5/07 200
1/6/07 – 1/7/07 100
1/8/07 – 1/10/07 200
Thanks,
Brian
August 22, 2006 at 9:10 am
Looks to me that you'll have to use a cursor. The reason is that you need to loop through the records in date order to get the data formatted the way you want.
Once you get the first record, you'll need to store the values in variables and then check each subsequent record for a change of the value. When it changes, you'll want to output a record showing the first date, the last date (before value changed), and the value.
You might use a temp table or table variable for the output records, then select from that once you're done looping.
August 22, 2006 at 10:22 am
What is the criteria for the results? do you want to break it up for every 3 days? stay away from cursors.
******************
Dinakar Nethi
Life is short. Enjoy it.
******************
August 22, 2006 at 11:36 am
Dinakar,
To me it looks like the pattern is the changing price value. I don't see a way around the cursor in this case.
August 22, 2006 at 11:38 am
There is probably a better way, but this seems to work.
declare @t table
(
tDate datetime not null primary key
,price int not null
)
insert @t
select '20070101', 100 union all
select '20070102', 100 union all
select '20070103', 100 union all
select '20070104', 200 union all
select '20070105', 200 union all
select '20070106', 100 union all
select '20070107', 100 union all
select '20070108', 200 union all
select '20070109', 200 union all
select '20070110', 200
select T.tDate as DateFrom
,isnull
(
(select min(tdate)
from @t T1
where T1.price <> T.price
and T1.tdate > T.tdate) - 1
,
(select max(t2.tdate)
from @t T2) ) as DateTo
,T.price
from @t T
where T.tDate = isnull
(
(select max(T3.tDate)
from @t T3
where T3.price <> T.price
and T3.tDate < T.tDate) + 1
,
(select min(t4.tdate)
from @t T4) )
August 22, 2006 at 12:24 pm
Very nice!
August 23, 2006 at 3:11 am
This is more efficient:
select T.tDate as DateFrom
,isnull
(
(select min(tdate)
from @t T1
where T1.price <> T.price
and T1.tdate > T.tdate) - 1
, D.MaxTDate) as DateTo
,T.price
from @t T cross join
(
select min(T5.tdate), max(T5.tDate)
from @t T5) D (MinTDate, MaxTDate)
where T.tDate = isnull
(
(select max(T3.tDate)
from @t T3
where T3.price <> T.price
and T3.tDate < T.tDate) + 1
, D.MinTDate )
August 23, 2006 at 6:19 am
Thanks for the suggestions. You are right, the pattern is the changing price. Ken, if I have a year, or 2 years worth of pricing records, I'm not sure I follow the inserts
insert @t
select '20070101', 100 union all
select '20070102', 100 union all
select '20070103', 100 union all
select '20070104', 200 union all
select '20070105', 200 union all
select '20070106', 100 union all
select '20070107', 100 union all
select '20070108', 200 union all
select '20070109', 200 union all
select '20070110', 200
How do I generate these SELECT statements? If I have 1 year's worth of data, I'd need 365 SELECT statements?
August 23, 2006 at 6:46 am
The select statements are only test data. You will have to adapt the query so it works on your table.
August 23, 2006 at 7:05 am
My point is do I have to manually type in these statements or can I write some code that will do it? If I have to manually type them in then this is not a viable solution.
August 23, 2006 at 7:13 am
Take the query:
select T.tDate as DateFrom
,isnull
(
(select min(tdate)
from @t T1
where T1.price <> T.price
and T1.tdate > T.tdate) - 1
, D.MaxTDate) as DateTo
,T.price
from @t T cross join
(
select min(T5.tdate), max(T5.tDate)
from @t T5) D (MinTDate, MaxTDate)
where T.tDate = isnull
(
(select max(T3.tDate)
from @t T3
where T3.price <> T.price
and T3.tDate < T.tDate) + 1
, D.MinTDate )
Replace @t with YourTableName
Replace tDate with YourDateColumn
Replace price with YourPriceColumn
August 23, 2006 at 7:23 am
Now I get it, thanks Ken, works great!
August 23, 2006 at 7:30 am
Hi,
I came up with a slightly different solution on the same theme as Ken's...
declare @DateDemo table
(
TestDate datetime not null primary key
,price int not null
)
insert @DateDemo
select '20070101', 100 union all
select '20070102', 100 union all
select '20070103', 100 union all
select '20070104', 200 union all
select '20070105', 200 union all
select '20070106', 100 union all
select '20070107', 100 union all
select '20070108', 200 union all
select '20070109', 200 union all
select '20070110', 200 union all
select '20070111', 200 union all
select '20070112', 200 union all
select '20070113', 200 union all
select '20070115', 200 union all
select '20070116', 200 union all
select '20070117', 200 union all
select '20070118', 200 union all
select '20070119', 100 union all
select '20070120', 200 union all
select '20070121', 200 union all
select '20070122', 200 union all
select '20070123', 200
SELECT DD1.TestDate, MIN(DD2.TestDate), DD1.Price
FROM @DateDemo DD1, @DateDemo DD2
WHERE DD1.Price <>
(ISNULL
((SELECT TOP 1 DD3.Price FROM @DateDemo DD3
WHERE DD3.TestDate<DD1.TestDate
ORDER BY TestDate DESC),0))
AND DD1.Price=DD2.Price
AND DD1.TestDate<=DD2.TestDate
AND DD2.Price <>
(ISNULL
((SELECT TOP 1 DD4.Price FROM @DateDemo DD4
WHERE DD4.TestDate>DD2.TestDate
ORDER BY TestDate ),0))
GROUP BY DD1.TestDate, DD1.Price
David
If it ain't broke, don't fix it...
August 23, 2006 at 8:45 am
David,
WOW!
It took me 15 minutes to understand what you did. It is a lot more efficient than my approach.
All methods with their percentage of batch cost are below:
declare @t table
(
tDate datetime not null primary key
,price int not null
)
-- Populate test data
-- 6.27% of batch cost
insert @t
select '20070101', 100 union all
select '20070102', 100 union all
select '20070103', 100 union all
select '20070104', 200 union all
select '20070105', 200 union all
select '20070106', 100 union all
select '20070107', 100 union all
select '20070108', 200 union all
select '20070109', 200 union all
select '20070110', 200
-- My original approach
-- 44.88% of batch cost
select T.tDate as DateFrom
,isnull
(
(select min(tdate)
from @t T1
where T1.price <> T.price
and T1.tdate > T.tdate) - 1
,
(select max(t2.tdate)
from @t T2) ) as DateTo
,T.price
from @t T
where T.tDate = isnull
(
(select max(T3.tDate)
from @t T3
where T3.price <> T.price
and T3.tDate < T.tDate) + 1
,
(select min(t4.tdate)
from @t T4) )
-- My altered approach
-- 30.80% of batch cost
select T.tDate as DateFrom
,isnull
(
(select min(tdate)
from @t T1
where T1.price <> T.price
and T1.tdate > T.tdate) - 1
, D.MaxTDate) as DateTo
,T.price
from @t T cross join
(
select min(T5.tdate), max(T5.tDate)
from @t T5) D (MinTDate, MaxTDate)
where T.tDate = isnull
(
(select max(T3.tDate)
from @t T3
where T3.price <> T.price
and T3.tDate < T.tDate) + 1
, D.MinTDate )
-- David's approach
-- 18.04% of batch cost
SELECT T1.tDate as DateFrom
,Min(T2.tDate) as DateTo
,T1.Price
--,T2.tdate
FROM @t T1
join @t T2 on T1.Price = T2.Price
AND T1.tDate <= T2.tDate
WHERE T1.Price <>
ISNULL
((SELECT TOP 1 T3.Price FROM @t T3
WHERE T3.tDate < T1.tDate
ORDER BY T3.tDate DESC),0)
AND T2.Price <>
ISNULL
((SELECT TOP 1 T4.Price FROM @t T4
WHERE T4.tDate > T2.tDate
ORDER BY T4.tDate ),0)
GROUP BY T1.tDate, T1.Price
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply