Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Linking to the Previous Row Expand / Collapse
Author
Message
Posted Wednesday, March 12, 2008 11:00 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, April 17, 2014 6:56 AM
Points: 641, Visits: 1,794
Comments posted to this topic are about the item Linking to the Previous Row
Post #468501
Posted Thursday, March 13, 2008 3:05 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, March 07, 2014 2:41 AM
Points: 50, Visits: 396
a very nice article indeed, however i recently had the same issue on SQL Server 8.0, Is their a related atricle/method for SQL 8?

Knock Knock, Who's There?, sp underscore, sp underscore who?
spid1, spid2, spid3....
Post #468560
Posted Thursday, March 13, 2008 3:29 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, April 17, 2014 6:56 AM
Points: 641, Visits: 1,794
Hi Jordon,
As you probably realise, SQL 2000 doesn't have CTEs or RowNumber.

I was happy to discover CTEs precisely because I'm hopeless at doing queries like the one below!! So here's something to get you started...

...but I'm sure someone will chip in with a complete / better solution.

select  currow.ItemId,
( select max(PriceStartDate)
from PriceHistory phPrev
where phPrev.PriceStartDate < currow.PriceStartDate
and phPrev.ItemId=currow.ItemId
) as OldPrice,
currow.PriceStartDate,
currow.Price
from PriceHistory currow

Change the smiley for a closing bracket.

Hope this helps....

David.
Post #468567
Posted Thursday, March 13, 2008 3:51 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, March 07, 2014 2:41 AM
Points: 50, Visits: 396
hmmm, yes based on that i have come up with a simple sub-select approach:

select * from SELECT     DailyMileageID, MileageAtStartOfShift, MileageAtEndOfShift, MileageAtEndOfShift - MileageAtStartOfShift AS DayMileage, MileageAtEndOfShift -
(SELECT MileageAtStartOfShift
FROM dbo.DailyMileages AS I
WHERE (DailyMileageID = b.DailyMileageID + 1)) AS NightMileage, ShopID, DateEntered
FROM dbo.DailyMileages AS b

which works, but i am a little bothered about performance, but yes, thanks for the tip :

next task... convince this company to upgrade their sql server :D


Knock Knock, Who's There?, sp underscore, sp underscore who?
spid1, spid2, spid3....
Post #468574
Posted Thursday, March 13, 2008 3:57 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, April 17, 2014 6:56 AM
Points: 641, Visits: 1,794
Be careful about using DailyMileageID+1. You could have holes if it's an identity column (when you delete rows, for example), and also is the sort order definitely correct?

Look in your data for another way of identifying the next / previous row.

Regards,

David.
Post #468577
Posted Thursday, March 13, 2008 6:27 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, March 31, 2014 10:11 AM
Points: 431, Visits: 604
As you mention in the article I maintain audit trails by storing both a start date and an end date in my history tables. How is the overall performance of the CTE method for extracting a point-in-time view of your data? Is it fast enough to handle reporting on demand?

Regards,
Michael Lato
Post #468621
Posted Thursday, March 13, 2008 6:35 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:12 AM
Points: 10,910, Visits: 12,553
Nice article. I like the fact that is clearly takes you from start to finish and offers a solution to a commonly encountered problem.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #468626
Posted Thursday, March 13, 2008 6:36 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 13, 2013 2:07 PM
Points: 34, Visits: 117
For this date range technique, for the end date:
nextrow.PriceStartDate AS EndDate
I usually do:
dateadd(day, -1, nextrow.PriceStartDate) AS EndDate

Also truncate to midnight the start/end date and any date comparisons to the range
Post #468629
Posted Thursday, March 13, 2008 7:27 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, April 17, 2014 6:56 AM
Points: 641, Visits: 1,794
How is the overall performance of the CTE method for extracting a point-in-time view of your data? Is it fast enough to handle reporting on demand?


I haven't done any specific volume testing on this. All I can say is that I'm using it in production systems, without any noticeable performance hit. Obviously storing the data inline is going to be faster, but to what degree I honestly don't know.

I'm one of those developers who will often choose the elegant solution over the fastest solution, providing it is fast enough for the application being developed.

Regards,

David.
Post #468682
Posted Thursday, March 13, 2008 7:35 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:17 PM
Points: 36,002, Visits: 30,296
Jack Corbett (3/13/2008)
Nice article. I like the fact that is clearly takes you from start to finish and offers a solution to a commonly encountered problem.


Agreed... and test data used was attached in the Resources area... nicely done.



--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #468687
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse