SELECT myData without using a cursor? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SELECT myData without using a cursor?

Author

Topic

Hommer
Aged Yak Warrior

808 Posts

Posted - 2007-12-04 : 10:31:47

Hi, everyone,

I could use a big help to get this one working. Pls see the sample data.

I think I need a cursor to loop backwardly. Is there any other way?

Thanks!

USE tempdb
go

IF Object_ID ('temdb..#myTable') is not NULL Drop Table #myTable

Create table #myTable (myID INT, myData INT, myDate DateTime)

INSERT INTO #myTable
Select 1, 10, Getdate()-5 UNION
Select 2, 12, Getdate()-4 UNION
Select 3, 12, Getdate()-3 UNION
Select 4, 15, Getdate()-2 UNION
Select 5, 15, Getdate()-1 UNION
Select 6, 15, Getdate()

Here is the SELECT result I want to see.

Current_Data, Last_diff_data, data_chg_date
15, 12, 2007-12-01

Note: don't worry about the time portion format.
Please also tell me if your solution only apply to sql05.

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2007-12-04 : 10:37:01

try this...


select t2.mydata,t1.mydata,t1.mydate
from #mytable t1
	join #mytable t2 
	on t1.myid = t2.myid - 1
	and t1.mydata <> t2.mydata
	and t1.mydate <> t2.mydate

Em

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2007-12-04 : 10:39:20

or if you just wanted the most recent...


select top 1 t2.mydata as current_data
			,t1.mydata as last_diff_data
			,t1.mydate as data_chg_date
from #mytable t1
	join #mytable t2 
	on t1.myid = t2.myid - 1
	and t1.mydata <> t2.mydata
	and t1.mydate <> t2.mydate
order by t1.mydate desc

Em

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-04 : 10:40:36

[code]DECLARE @MyTable TABLE (myID INT, myData INT, myDate DateTime)

INSERT @myTable
SELECT 1, 10, GETDATE() - 5 UNION ALL
SELECT 2, 12, GETDATE() - 4 UNION ALL
SELECT 3, 12, GETDATE() - 3 UNION ALL
SELECT 4, 15, GETDATE() - 2 UNION ALL
SELECT 5, 15, GETDATE() - 1 UNION ALL
SELECT 6, 15, GETDATE()

SELECT TOP 1 t1.myData AS Current_Data,
t2.myData AS Last_Diff_Data,
DATEADD(DAY, DATEDIFF(DAY, '19000101', t2.myDate), '19000101') AS Data_Chg_Date
FROM @MyTable AS t1
INNER JOIN @MyTable AS t2 ON t2.myDate < t1.MyDate
WHERE t1.myData <> t2.myData
ORDER BY t1.myDate DESC,
t2.myDate DESC[/code]

E 12°55'05.25"
N 56°04'39.16"

Hommer
Aged Yak Warrior

808 Posts

Posted - 2007-12-04 : 10:51:47

Thank you for both of your quick replies.

Peso's solution works!

elancaster,

Sorry my sample data is a little misleading. the myID in real data is not in a squence order, so t1.myid = t2.myid - 1 is not always true.

Thanks a lot! You guys are great!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources