cursor or subquery? - SQL Server Forums

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)

cursor or subquery?

Author

Topic

Hommer
Aged Yak Warrior

808 Posts

Posted - 2007-12-06 : 15:14:33

Hi, everyone,

I could use a big help to get the right data. Pls see the sample data.

I think I need a cursor or subquery of some kind?

Currently I have an idea of using #table, but I believe there must be a better 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, 13, Getdate()-1 UNION
Select 6, 15, Getdate()

Here is the SELECT result I want to see. The trick I have trouble to solve is on getting the 3rd and 4th columns. You may ignore the time portion of the DateTime data.

ID, Data, Data_Prev, data_Diff
1, 10, Null, Null
2, 12, 10, 2
3, 12, 12, 0
4, 15, 12, 3
5, 13, 15, -2
...

Thanks!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-06 : 15:22:25

Are you sorting by date or by id?

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

Hommer
Aged Yak Warrior

808 Posts

Posted - 2007-12-06 : 15:30:13

Either one will work. I am not sure the difference.
My real data's myID is a subID, which runs from 1 to 52, and myDate is Every Sunday for the past year.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-06 : 15:41:18

Either one?
You don't care or don't know the business rules?

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

Hommer
Aged Yak Warrior

808 Posts

Posted - 2007-12-06 : 15:53:50

I mean they should work the same way. And this is in a sub set of the query I am building, therefore, the overall sort by business rule doesn't apply.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-06 : 15:55:59

Well, if the dates do not match some order as ID, then the PrevData and DataDiff columns are screwed up and you will get different result when sorting by ID than sorting by date.

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

Hommer
Aged Yak Warrior

808 Posts

Posted - 2007-12-06 : 16:06:59

Both are machine generated, and users have no way of changing the values. So, if you have a solution, I think either one sould work.

Pdjach
Starting Member

2 Posts

Posted - 2007-12-06 : 16:08:01

Not sure what you're trying to achieve but this would give you the result displayed.

select m1.myid, m1.mydata,m2.mydata, m1.mydata - m2.mydata
from #mytable m1 left join #mytable m2
on m1.myid-1=m2.myid

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-06 : 16:13:53

What if there are gaps in the ID sequence?

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

Pdjach
Starting Member

2 Posts

Posted - 2007-12-06 : 17:02:42

Without knowing the business rule that was the simpliest suggestion.
The following should handle cases with gaps in the ID.

with CTE (PrevID, CurrID) as
(
select max(m1.myid), m.myid from #mytable m1, #mytable m where m1.myid < m.myid
group by m.myid
)
select m1.myid, m1.mydata ,m2.mydata, m1.mydata - m2.mydata
from #mytable m1 left join CTE
on m1.myid=CTE.CurrID
left join #mytable m2 on m2.myid=CTE.PrevID

Hommer
Aged Yak Warrior

808 Posts

Posted - 2007-12-06 : 18:18:59

The business logic behind this is to detect trend of sales for given items. The myData is qty of item sold in each the given week.

And again, myID doesn't have gap because it is generated by code, and users do not get to change it or delete the whole line.

Anyway, thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources