June 2, 2005 at 3:13 pm
I have a data set such as this...
| ID | TIME | Price |
| 53A0438C-929B-4650-BD7D-DC4D64C7C87F | 4/18/05 3:15 PM | 5000 |
| 53A0438C-929B-4650-BD7D-DC4D64C7C87F | 1/24/05 8:27 PM | 7000 |
| 53A0438C-929B-4650-BD7D-DC4D64C7C87F | 1/18/05 1:23 PM | 5000 |
| 53A0438C-929B-4650-BD7D-DC4D64C7C87F | 1/18/05 1:38 PM | 5000 |
| 53A0438C-929B-4650-BD7D-DC4D64C7C87F | 1/3/05 11:47 AM | 13000 |
| 53A0438C-929B-4650-BD7D-DC4D64C7C87F | 1/18/05 1:21 PM | 5000 |
| 53A0438C-929B-4650-BD7D-DC4D64C7C87F | 1/25/05 1:39 PM | 5000 |
I need to find out what the initial price is and then whenever the price changes but weed out the instances where it stays the same.
I'm not well versed in Loops and Cursors but I feel I may need one here. Can anyone point me in the right direction???
Thanks in advance
June 2, 2005 at 9:33 pm
No, you don't need a cursor.
I would start by listing the ids, and their earliest instance.
select t.id, min(t.time)
from tbl t
group by t.id
Then you can work out what the first time instance is, by looking it up.
select t.id, min(t.time) as earliest_time, (select t_earliest.price from tbl t_earliest where t_earliest.id = t.id and t_earliest.time = min(t.time)) as initial_price
from tbl t
group by t.id
As for the changes...
Start with listing each record with its next one:
select *
from tbl t
join
tbl t_next
on t_next.id = t.id
and t_next.time = (select min(t_rest.time) from tbl t_rest where t_rest.id = t.id and t_rest.time > t.time)
Then you can compare the two, and see if a change has taken place, listing when that was, and what the price was before and after.
select t_next.time, t.price as orig_price, t_next.price as new_price
from tbl t
join
tbl t_next
on t_next.id = t.id
and t_next.time = (select min(t_rest.time) from tbl t_rest where t_rest.id = t.id and t_rest.time > t.time)
where t_next.price t.price
Ok?
Rob Farley
LobsterPot Solutions & Adelaide SQL Server User Group
Company: http://www.lobsterpot.com.au
Blog: http://blogs.lobsterpot.com.au
June 3, 2005 at 7:46 am
Rob,
This works awesome...and no loop...wonderful!
Thanks!
Marty
June 3, 2005 at 7:51 am
It's really rare that we have no other options than to use a loop with TSQL.
Good luck with this project.
June 3, 2005 at 8:07 am
can you repost Joe?.. something went wrong.
June 3, 2005 at 8:46 am
Thanx for the info Joe... but may I request that you delete your first post on this thread so that we regain a more conventionnal html design?
Thanx in advance.
June 3, 2005 at 1:22 pm
Wow...I wonder what's going to happen when I post this.
Just wanted to say, nice technique, Rob...somehow I missed that one. It's funny, because I was looking at a problem that needed this. An example of the bad design that Joe is mentioning is the MSDB SysJobHistory table, where Job outcomes are stored as there own row, and there's no key referencing the record that started the job. Worse and worse.
So....
select
t.instance_id,
t.step_name,
t.run_date,
t.run_time,
t_next.step_name as out_step,
t_next.instance_id as out_id,
t_next.run_date as out_date,
t_next.run_time as out_time
from msdb.dbo.SysJobHistory t
join msdb.dbo.SysJobHistory t_next
on t_next.job_id = t.job_id
and t_next.instance_id = (select min(t_rest.instance_id) from msdb.dbo.SysJobHistory t_rest where t_rest.job_id = t.job_id and t_rest.instance_id > t.instance_id and t_rest.step_name = '(Job outcome)')
where t.step_name <> '(Job outcome)'
Signature is NULL
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply