June 18, 2009 at 3:10 am
Hello,
script:
if object_id('tempdb..#temp') is not null drop table #temp
create table #temp (item varchar(10), price1 money, price2 money, date datetime)
insert into #temp (item, price1, price2, date) select '1', 10, 10, '20090601'
insert into #temp (item, price1, price2, date) select '1', 10, 10, '20090602'
insert into #temp (item, price1, price2, date) select '1', 10, 15, '20090603'
insert into #temp (item, price1, price2, date) select '1', 10, 15, '20090604'
insert into #temp (item, price1, price2, date) select '1', 11, 10, '20090605'
insert into #temp (item, price1, price2, date) select '1', 10, 15, '20090606'
select *
from #temp
order by date
I would like to have this result:
110.0010.002009-06-02 00:00:00.000
110.0015.002009-06-04 00:00:00.000
111.0010.002009-06-05 00:00:00.000
110.0015.002009-06-06 00:00:00.000
The table represents changes on price1 and price2 on items. I need all the rows which have a change in price1 or in price2, ordered by date desc. I could use cursor, but... 😛 Is there any easier way to do this?
Thanks
June 18, 2009 at 3:15 am
Hi there,
I'm not quite with you... you ordered by date desc, but your output is not ordered, what is it that needs ordered by date desc?
June 18, 2009 at 3:19 am
With 'ordered by date desc' I mean that if there are two rows with same data (item, price1 and price2), then the returned row should be the one with greater date.
In my example, the first two rows have identical item, price1 and price2. But I need the second row of them, because it has greater date that the first row.
But the whole query should be ordered by date asc...
June 18, 2009 at 3:29 am
try this:
;WITH CTE as (
select
ROW_NUMBER() OVER(PARTITION BY Item,price1,price2 ORDER BY Date DESC) Row,
*
from #temp
)
SELECT *
FROM CTE
WHERE ROW = 1
order by date
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
June 18, 2009 at 3:29 am
Is it what you are looking for??
select item, price1, price2, max(date)
from #temp
GROUP BY item, price1, price2
but i am not still clear about ur requirement.
June 18, 2009 at 3:54 am
No the output results are very unknown and no sense why these results...more explanation if you want the correct solution for what you are looking!
:hehe::w00t::hehe:
June 18, 2009 at 3:56 am
I see I wrote incorrect result in my first post, now it is correct.
Christopher and Pyay, your query returns only distinct sets of item, price1 and price2. That's not ok - see my first post, first and last row of wanted result is same, just date is different.
If I rephrase my question - I need all rows where price1 or price2 is different from previous row.
Sory if I'm not very clear with my question, english is not my first language.
June 18, 2009 at 4:47 am
try this one..
SELECTItem, price1, price2, date
FROM#temp
WHEREdate = (select max(date) from #temp)
UNION
SELECTdistinct T.Item, T.price1, T.price2, (case when (T.price1 = C.price1andT.price2 = C.price2) then C.Date else t.Date end)
FROM#temp T
INNER JOIN#temp C
ONT.Item = C.Item
andC.date = (select min(date) from #temp where date > T.Date)
order by date
June 18, 2009 at 5:02 am
Pyay Nyein (6/18/2009)
try this one..
SELECTItem, price1, price2, date
FROM#temp
WHEREdate = (select max(date) from #temp)
UNION
SELECTdistinct T.Item, T.price1, T.price2, (case when (T.price1 = C.price1andT.price2 = C.price2) then C.Date else t.Date end)
FROM#temp T
INNER JOIN#temp C
ONT.Item = C.Item
andC.date = (select min(date) from #temp where date > T.Date)
order by date
Yes, the results are exact as he wants on first post, but seems that they are sample data and what about if you have more than 1000 records how you will explain the logic of the result set!
Anyway, this code here works perfect!
:hehe::w00t::hehe:
June 18, 2009 at 5:25 am
Hey, of course this are sample data 🙂
Maybe this is better representation of actual data:
if object_id('tempdb..#temp') is not null drop table #temp
create table #temp (item varchar(10), price1 money, price2 money, date datetime)
insert into #temp (item, price1, price2, date) select '1', 10, 10, '20090601'
insert into #temp (item, price1, price2, date) select '1', 10, 10, '20090602'
insert into #temp (item, price1, price2, date) select '1', 10, 15, '20090603'
insert into #temp (item, price1, price2, date) select '1', 10, 15, '20090604'
insert into #temp (item, price1, price2, date) select '1', 11, 10, '20090605'
insert into #temp (item, price1, price2, date) select '1', 10, 15, '20090606'
insert into #temp (item, price1, price2, date) select '2', 100, 100, '20090601'
insert into #temp (item, price1, price2, date) select '2', 100, 100, '20090602'
insert into #temp (item, price1, price2, date) select '2', 111, 100, '20090603'
I should get:
1 10.00 10.00 2009-06-02 00:00:00.000
1 10.00 15.00 2009-06-04 00:00:00.000
1 11.00 10.00 2009-06-05 00:00:00.000
1 10.00 15.00 2009-06-06 00:00:00.000
2 100.00 100.00 2009-06-02 00:00:00.000
2 111.00 100.00 2009-06-03 00:00:00.000
June 18, 2009 at 5:34 am
_simon_ (6/18/2009)
Hey, of course this are sample data 🙂Maybe this is better representation of actual data:
if object_id('tempdb..#temp') is not null drop table #temp
create table #temp (item varchar(10), price1 money, price2 money, date datetime)
insert into #temp (item, price1, price2, date) select '1', 10, 10, '20090601'
insert into #temp (item, price1, price2, date) select '1', 10, 10, '20090602'
insert into #temp (item, price1, price2, date) select '1', 10, 15, '20090603'
insert into #temp (item, price1, price2, date) select '1', 10, 15, '20090604'
insert into #temp (item, price1, price2, date) select '1', 11, 10, '20090605'
insert into #temp (item, price1, price2, date) select '1', 10, 15, '20090606'
insert into #temp (item, price1, price2, date) select '2', 100, 100, '20090601'
insert into #temp (item, price1, price2, date) select '2', 100, 100, '20090602'
insert into #temp (item, price1, price2, date) select '2', 111, 100, '20090603'
I should get:
1 10.00 10.00 2009-06-02 00:00:00.000
1 10.00 15.00 2009-06-04 00:00:00.000
1 11.00 10.00 2009-06-05 00:00:00.000
1 10.00 15.00 2009-06-06 00:00:00.000
2 100.00 100.00 2009-06-02 00:00:00.000
2 111.00 100.00 2009-06-03 00:00:00.000
As you can see with the query above, you can not have the desire results again! For your output results we must change the code again ...and finally as I can see you do not have idea for what results you are looking! In this way seems that no one can help you if you are not much clear for the problem that you have!
June 18, 2009 at 6:11 am
_simon_ (6/18/2009)
Hey, of course this are sample data 🙂Maybe this is better representation of actual data:
if object_id('tempdb..#temp') is not null drop table #temp
create table #temp (item varchar(10), price1 money, price2 money, date datetime)
insert into #temp (item, price1, price2, date) select '1', 10, 10, '20090601'
insert into #temp (item, price1, price2, date) select '1', 10, 10, '20090602'
insert into #temp (item, price1, price2, date) select '1', 10, 15, '20090603'
insert into #temp (item, price1, price2, date) select '1', 10, 15, '20090604'
insert into #temp (item, price1, price2, date) select '1', 11, 10, '20090605'
insert into #temp (item, price1, price2, date) select '1', 10, 15, '20090606'
insert into #temp (item, price1, price2, date) select '2', 100, 100, '20090601'
insert into #temp (item, price1, price2, date) select '2', 100, 100, '20090602'
insert into #temp (item, price1, price2, date) select '2', 111, 100, '20090603'
I should get:
1 10.00 10.00 2009-06-02 00:00:00.000
1 10.00 15.00 2009-06-04 00:00:00.000
1 11.00 10.00 2009-06-05 00:00:00.000
1 10.00 15.00 2009-06-06 00:00:00.000
2 100.00 100.00 2009-06-02 00:00:00.000
2 111.00 100.00 2009-06-03 00:00:00.000
yeah my logic is a bit wrong for the max(date), it should group by item. Try this one again..Hope it would solve ur problem for all...
SELECTItem, price1, price2, date
FROM#temp
WHEREdate in (select max(date) from #temp group by item)
UNION
SELECTdistinct T.Item, T.price1, T.price2, (case when (T.price1 = C.price1andT.price2 = C.price2) then C.Date else t.Date end)
FROM#temp T
INNER JOIN#temp C
ONT.Item = C.Item
andC.date = (select min(date) from #temp where date > T.Date)
order by date
June 18, 2009 at 6:25 am
I can not see where did you change your code in your last post but again no same results as simon wants !!!
As I can see and little bit analyzing no way to find the correct solution for that output results!
:hehe::w00t::hehe:
June 18, 2009 at 6:34 am
Dugi (6/18/2009)
I can not see where did you change your code in your last post but again no same results as simon wants !!!As I can see and little bit analyzing no way to find the correct solution for that output results!
:hehe::w00t::hehe:
sorry, I posted the wrong script. Here is the correct one. I've changed at the where clause of the first part of the Union.
'date = (select max(date) from #temp b where a.item = b.item group by item )'
SELECTItem, price1, price2, date
FROM#temp a
WHEREdate = (select max(date) from #temp b where a.item = b.item group by item )
UNION
SELECTdistinct T.Item, T.price1, T.price2, (case when (T.price1 = C.price1andT.price2 = C.price2) then C.Date else t.Date end)
FROM#temp T
INNER JOIN#temp C
ONT.Item = C.Item
andC.date = (select min(date) from #temp where date > T.Date)
order by item, date
June 18, 2009 at 6:38 am
Thanks Pyay, almost what I want. With your query I get this:
1 10.00 10.00 2009-06-02 00:00:00.000
1 10.00 15.00 2009-06-03 00:00:00.000
1 10.00 15.00 2009-06-04 00:00:00.000
1 11.00 10.00 2009-06-05 00:00:00.000
1 10.00 15.00 2009-06-06 00:00:00.000
2 100.00 100.00 2009-06-02 00:00:00.000
2 111.00 100.00 2009-06-03 00:00:00.000
But there is one row to much (the second row in your result set)
It should be:
1 10.00 10.00 2009-06-02 00:00:00.000
1 10.00 15.00 2009-06-04 00:00:00.000
1 11.00 10.00 2009-06-05 00:00:00.000
1 10.00 15.00 2009-06-06 00:00:00.000
2 100.00 100.00 2009-06-02 00:00:00.000
2 111.00 100.00 2009-06-03 00:00:00.000
Dugi, I think from my last post you could pretty clearly see what I want :ermm:
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply