Linking to the Previous Row

Question

Linking to the Previous Row

Viewing 15 posts - 91 through 105 (of 147 total)

You must be logged in to reply to this topic. Login to reply

philmond Old Hand Points: 360 More actions · Answer 1

Could you please tell me what's the advantage of using CTE and ROW_NUMBER() over a mush more intuitive temporary table with an IDENTITY(1,1) field?

BOR15K SSCertifiable Points: 5894 More actions · Answer 2

Nice article, but I wonder, what would be a performance using this PREV and NEXT join statements, as well as to ask

if there is any faster way to implement Oracle's LEAD and LAG functions, in case I need only one or two values from the prev and / or next records.

An example for such a need is a broadcasting (television) scheduling, where I need to know the start time and duration of the previous event to calculate

if I have any gaps in the programmes list, which I will need to reschedule or to populate with promos or adverts.

Regards.

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 3

philmond (8/20/2010)
Could you please tell me what's the advantage of using CTE and ROW_NUMBER() over a mush more intuitive temporary table with an IDENTITY(1,1) field?

IMHO... Just a tiny bit of speed if you only intend to "use" the CTE once in the code. Otherwise, the IDENTITY thing is my favorite for a whole lot of things... especially since I don't need to wrap IDENTITY in an ISNULL during a high speed SELECT INTO to make a NOT NULL column like I'd have to with ROW_NUMBER().

--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.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 4

Zeev Kazhdan (8/21/2010)
... but I wonder, what would be a performance using this PREV and NEXT join statements, as well as to ask
if there is any faster way to implement Oracle's LEAD and LAG functions,...

Ohhhhhh yessssss, there certainly is... MUCH faster. To give you an idea of the performance, it'll do an overall running total, a partitioned (or grouped) running total, and similar running counts all at the same time on a million rows in just a couple of seconds on a good machine. It's NOT, however, a fully documented feature of SQL Server and you either need to follow some very precise rules, or don't use it. Please see the following URL...

http://www.sqlservercentral.com/articles/T-SQL/68467/

There are also some really high speed methods to find gaps in certain types of data that don't rely on the "quirky update" and don't rely on having to make a sequentially numbered temp table although neither of those methods are bad for performance.

--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.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

theSuda SSC Eights! Points: 887 More actions · Answer 5

A really great article indeed. Thanks.

_____________________________________________________________

[font="Tahoma"]'Some people still are alive simply because it is illegal to shoot them... o_O 😎 '[/font]

niyinks SSC Enthusiast Points: 190 More actions · Answer 6

ItemID OldPrice RangePrice StartDate EndDate

----------- --------------------- --------------------- ----------------------- -----------------------

1 NULL 250.00 2004-03-01 00:00:00 2005-06-15 00:00:00

1 250.00 219.99 2005-06-15 00:00:00 2007-01-03 00:00:00

1 219.99 189.99 2007-01-03 00:00:00 2007-02-03 00:00:00

1 189.99 200.00 2007-02-03 00:00:00 NULL

2 NULL 650.00 2006-07-12 00:00:00 2007-01-03 00:00:00

2 650.00 550.00 2007-01-03 00:00:00 NULL

3 NULL 1.99 2005-01-01 00:00:00 2006-01-01 00:00:00

3 1.99 1.79 2006-01-01 00:00:00 2007-01-01 00:00:00

3 1.79 1.59 2007-01-01 00:00:00 2008-01-01 00:00:00

3 1.59 1.49 2008-01-01 00:00:00 NULL

Hi

niyinks SSC Enthusiast Points: 190 More actions · Answer 7

Hi

The table above identifies my problem, it reuses the end date and start date. Thus having multiple entries for a date instead of having a few entries only and in terms of people who were admitted and disharged severally you will have the dates not being properly referenced.

thus some dates will occur more than required

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 8

niyinks (8/23/2010)
Hi
The table above identifies my problem, it reuses the end date and start date. Thus having multiple entries for a date instead of having a few entries only and in terms of people who were admitted and disharged severally you will have the dates not being properly referenced.
thus some dates will occur more than required

Not really... dates are just a part of the equation... you also have ItemID to work with which makes "mini-sets" or "partitions" in the data. What do you want to do with the data you presented? AND, if you were to actually format the data according to the first link in my signature line below, someone might even be able to give you a demonstration.

As a side bar, it doesn't look like your data has anything to do with admittance and discharge of people.

--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.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

niyinks SSC Enthusiast Points: 190 More actions · Answer 9

I just used the example of the data created by someone else, my query does use the actual field names and its about admissions and discharges. But there is a limit I can make available for obvious reasons

here is my query again

with readmissions

as

(select a.PSEUDO_HESID,a.provspno,test,DISCH_DATE,ADMIN_DATE,a.PROCODE3,a.diag1,a.RowNumber,tally

from

(select PSEUDO_HESID,provspno,DISCH_DATE,PROCODE3,diag1,RowNumber =

Row_Number() OVER(PARTITION BY PSEUDO_HESID ORDER BY DISCH_DATE ASC)

from dbo.HES_APC_200809)a

left join

(select PSEUDO_HESID,provspno test,ADMIN_DATE,PROCODE3,diag1,tally =

Row_Number() OVER(PARTITION BY PSEUDO_HESID ORDER BY ADMIN_DATE ASC)

from dbo.HES_APC_200809)b

on a.procode3=b.procode3

and a.PSEUDO_HESID=b.PSEUDO_HESID

and a.diag1=b.diag1

and a.provspno < test

group by a.PSEUDO_HESID,a.provspno,test,DISCH_DATE,ADMIN_DATE,a.PROCODE3,a.RowNumber,a.diag1,tally)

select distinct*

from readmissions

where DISCH_DATE < ADMIN_DATE

but I am the first to admit that it might not be the best

niyinks SSC Enthusiast Points: 190 More actions · Answer 10

The ACTUAL PROBLEM IS GETTING A TABLE TO DISPLAY A 28 DAY READMISSION RATE, THE PARTITION DOES HELP IN SORTING THE DATES BUT HERE LIES THE PROBLEM WHEN YOU USE THE DATEDIFF FUNCTION TO GET THE INTERVAL BETWEEN DISCHARGE AND READMISSION, IT SUBRATCTS EVERY NEW ADMISSION FORM EVERY DISHARGE DATE AVAILABLE GIVING MULTIPLE READMISSIONS AND THIS IS THE FLAW I HAVE TRYING TO OVERCOME

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 11

niyinks (8/23/2010)
ItemID OldPrice RangePrice StartDate EndDate
----------- --------------------- --------------------- ----------------------- -----------------------
1 NULL 250.00 2004-03-01 00:00:00 2005-06-15 00:00:00
1 250.00 219.99 2005-06-15 00:00:00 2007-01-03 00:00:00
1 219.99 189.99 2007-01-03 00:00:00 2007-02-03 00:00:00
1 189.99 200.00 2007-02-03 00:00:00 NULL
2 NULL 650.00 2006-07-12 00:00:00 2007-01-03 00:00:00
2 650.00 550.00 2007-01-03 00:00:00 NULL
3 NULL 1.99 2005-01-01 00:00:00 2006-01-01 00:00:00
3 1.99 1.79 2006-01-01 00:00:00 2007-01-01 00:00:00
3 1.79 1.59 2007-01-01 00:00:00 2008-01-01 00:00:00
3 1.59 1.49 2008-01-01 00:00:00 NULL
Hi

So, what do you want for a result set for this? And, if you don't mind, if you want an actual query for it, can you post it IAW the first link in my signature below?

--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.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

peter-757102 SSCertifiable Points: 6877 More actions · Answer 12

niyinks (8/23/2010)
The ACTUAL PROBLEM IS GETTING A TABLE TO DISPLAY A 28 DAY READMISSION RATE, THE PARTITION DOES HELP IN SORTING THE DATES BUT HERE LIES THE PROBLEM WHEN YOU USE THE DATEDIFF FUNCTION TO GET THE INTERVAL BETWEEN DISCHARGE AND READMISSION, IT SUBRATCTS EVERY NEW ADMISSION FORM EVERY DISHARGE DATE AVAILABLE GIVING MULTIPLE READMISSIONS AND THIS IS THE FLAW I HAVE TRYING TO OVERCOME

Eh?

You do know usage of capitals in the manner you just did, is considered extremely rude, akin to shouting. It is enough for me anyways to keep your problem your problem and not spend any more time on it myself to help you.

Eric L Hackett SSC Eights! Points: 978 More actions · Answer 13

Nice article. I've been using this methodology for sometime, and I'm pretty sure it was here I was first sent stumbling in the right direction. This sums it up nicely.

Eric L Hackett SSC Eights! Points: 978 More actions · Answer 14

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 use the CTE / ROW_NUMBER method, often using both ASC and DESC for AJAX web applications and the performance is very satisfactory.

Ken SSCoach Points: 17191 More actions · Answer 15

Very nice article. My only comment was to set the end date as one day before the start date of the next row. I believe others have already commented on this though. I so wish I had seen this article at my old job. We did reporting similar to this all the time and it involved a lot of ugly looking queries.