Need some Logic Help on some row by row process

Question

Post reply

Need some Logic Help on some row by row process

Dan Standeven

Valued Member

Points: 73
More actions
February 29, 2008 at 7:06 am

#69611

Currently i am using a cursor to accomplish this, but there must be a better way.
I'm trying to pull the dates when widget types change in a table during a certain time period.
Heres an example of the Data I need to extract
Widget Date
1 2008-02-01 <-- I need to pull this date
1 2008-02-02
2 2008-02-03 <-- I need to pull this date
2 2008-02-04
1 2008-02-05 <-- I need to pull this date
1 2008-02-06
If anyone has any ideas, it would sure be helpful

Viewing 15 posts - 1 through 15 (of 30 total)

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

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 1

SELECT TOP 1 *

FROM yourtable

UNION

SELECT y1.*

FROM yourtable y1,

yourtable y2

WHERE y1.Date = y2.Date +1

AND y1.Widget <> y2.Widget

--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: 1004407 More actions · Answer 2

Any feedback on this? Did it do what you wanted?

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

Dan Standeven Valued Member Points: 73 More actions · Answer 3

Sorry i didn't get back to you earlier.

Unfortunately No i didn't work, I was receiving Most of the data for widget 1.

I'm trying to pull the data anytime the widget type changes, Whether it's a new one or an existing one..

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

Take a look at the URL in my signature line... it'll show you how to post some data so we can play with the same data as you...

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

Dan Standeven Valued Member Points: 73 More actions · Answer 5

Ok, Thanks.

I will setup some data and information tomorrow. I appreciate your help so far.

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 6

Perfect. Thanks Dan... you won't spend the time in vain.

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

Dan Standeven Valued Member Points: 73 More actions · Answer 7

Ok, Hopefully i have done this right. If it is incorrect let me know, and I will fix it.

--===== If the test table already exists, drop it

IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL

DROP TABLE #mytable

--===== Create the test table with

CREATE TABLE #mytable

(

Widget Varchar(50),

DateValue DATETIME,

)

--===== Insert the test data into the test table

INSERT INTO #mytable

(Widget, DateValue)

SELECT '1','Jan 16 2008 12:29AM' UNION ALL

SELECT '1','Jan 17 2008 12:02AM' UNION ALL

SELECT '1','Jan 17 2008 6:30PM' UNION ALL

SELECT '1','Jan 17 2008 6:42PM' UNION ALL

SELECT '1','Jan 17 2008 6:44PM' UNION ALL

SELECT '1','Jan 17 2008 6:49PM' UNION ALL

SELECT '1','Jan 17 2008 7:04PM' UNION ALL

SELECT '2','Jan 17 2008 7:07PM' UNION ALL

SELECT '1','Jan 17 2008 7:44PM' UNION ALL

SELECT '1','Jan 17 2008 7:58PM' UNION ALL

SELECT '2','Jan 17 2008 7:59PM' UNION ALL

SELECT '2','Jan 17 2008 8:08PM' UNION ALL

SELECT '1','Jan 17 2008 8:08PM' UNION ALL

SELECT '1','Jan 17 2008 11:44PM' UNION ALL

SELECT '1','Jan 17 2008 11:45PM' UNION ALL

SELECT '1','Jan 17 2008 11:54PM' UNION ALL

SELECT '1','Jan 17 2008 11:56PM'

/*

Here are the Records I am looking to extract

'1','Jan 16 2008 12:29AM'

'2','Jan 17 2008 7:07PM'

'1','Jan 17 2008 7:44PM'

'2','Jan 17 2008 7:59PM'

'1','Jan 17 2008 8:08PM'

*/

Thanks

Dan

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

The code I posted before works fine on the data you posted before 😉 This new problem description is just a wee bit different...

... this will do it... lightning fast...

CREATE TABLE #MyHead

(

Widget VARCHAR(50),

DateValue DATETIME,

ToDisplay BIT,

PRIMARY KEY CLUSTERED (DateValue ASC,Widget DESC)

)

INSERT INTO #MyHead

(Widget, DateValue)

SELECT Widget, DateValue

FROM #MyTable

ORDER BY DateValue ASC, Widget DESC

DECLARE @PrevWidget VARCHAR(50)

DECLARE @PrevDateValue DATETIME --"Anchor"

SET @PrevWidget = ''

DECLARE @Dummy BIT

UPDATE #MyHead

SET @Dummy = ToDisplay = CASE WHEN Widget = @PrevWidget THEN 0 ELSE 1 END,

@PrevWidget = Widget

FROM #MyHead

SELECT Widget,DateValue FROM #MyHead WHERE ToDisplay = 1

DROP TABLE #MyHead

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 9

Okay, Jeff, once again you have amazed me. I did not know you could set variables within an update statement and do the X= Y = Z syntax.

Now explain to me how your update works? I would have thought that previous widget would get 1 value in the whole shebang. How and why did figure this out, or where did you find it?

Jack Corbett
Consultant - Straight Path Solutions
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

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 10

It's actually in Books Online under UPDATE...

Syntax

UPDATE

{

table_name WITH ( [ ...n ] )

| view_name

| rowset_function_limited

}

SET

{ column_name = { expression | DEFAULT | NULL }

| @variable = expression

| @variable = column = expression } [ ,...n ]

{ { [ FROM { } [ ,...n ] ]

[ WHERE

] }

I wish they had similar for INSERT and SELECT.

If you really want to see that type of update in all of it's high performance glory, check out that article I wrote on Running Balances...

http://www.sqlservercentral.com/articles/Advanced+Querying/61716/

--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: 1004407 More actions · Answer 11

Oh yeah... I almost forgot... the CLUSTERED INDEX (PK in this case) is absolutely essential. Normally, I'd name the key in the FROM clause using a WITH(INDEX(indexname,TABLOCKX)) hint to force the use of the correct (clustered) index... but it's not normally necessary to do that on a dedicated temp table.

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 12

Guess I need to read BOL cover to cover. Never would have have even thought of a need for it. I'll take a look at the article.

Jack Corbett
Consultant - Straight Path Solutions
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

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 13

This might not be that fast, but there is no need for clustered index

--Prepare sample data

DECLARE@Sample TABLE

(

Widget TINYINT,

DT SMALLDATETIME

)

INSERT@Sample

SELECT1, 'Jan 16 2008 12:29AM' UNION ALL

SELECT1, 'Jan 17 2008 12:02AM' UNION ALL

SELECT1, 'Jan 17 2008 06:30PM' UNION ALL

SELECT1, 'Jan 17 2008 06:42PM' UNION ALL

SELECT1, 'Jan 17 2008 06:44PM' UNION ALL

SELECT1, 'Jan 17 2008 06:49PM' UNION ALL

SELECT1, 'Jan 17 2008 07:04PM' UNION ALL

SELECT2, 'Jan 17 2008 07:07PM' UNION ALL

SELECT1, 'Jan 17 2008 07:44PM' UNION ALL

SELECT1, 'Jan 17 2008 07:58PM' UNION ALL

SELECT2, 'Jan 17 2008 07:59PM' UNION ALL

SELECT2, 'Jan 17 2008 08:08PM' UNION ALL

SELECT1, 'Jan 17 2008 08:08PM' UNION ALL

SELECT1, 'Jan 17 2008 11:44PM' UNION ALL

SELECT1, 'Jan 17 2008 11:45PM' UNION ALL

SELECT1, 'Jan 17 2008 11:54PM' UNION ALL

SELECT1, 'Jan 17 2008 11:56PM'

-- Prepare staging table

DECLARE@Stage TABLE

(

GrpID INT IDENTITY(1, 1),

Widget TINYINT,

FromDT SMALLDATETIME,

ToDT SMALLDATETIME

)

-- Initialize control variables

DECLARE @widget TINYINT,

@dt SMALLDATETIME,

@GrpID INT

-- Populate staging table with first value

INSERT@Stage

(

Widget,

FromDT

)

SELECT TOP 1Widget,

DT

FROM@Sample

ORDER BYDT

-- Parse all records in @Sample table

WHILE @@ROWCOUNT > 0

BEGIN

-- Get currently inserted data

SELECT@widget = Widget,

@dt = FromDT

FROM@Stage

WHEREGrpID = SCOPE_IDENTITY()

-- Insert earliest next record with different Widget

INSERT@Stage

(

Widget,

FromDT

)

SELECT TOP 1Widget,

DT

FROM@Sample AS sa

WHEREDT > @dt

AND Widget <> @widget

ORDER BYDT

END

-- Get last inserted record

SET@GrpID = SCOPE_IDENTITY()

-- Update datetime information

UPDATEs

SETs.ToDT = (SELECT MAX(x.DT) FROM @Sample AS x)

FROM@Stage AS s

WHEREs.grpID = @GrpID

-- Parse all records again

WHILE @GrpID > 0

BEGIN

-- Get last inserted datetime value

SELECT@dt = FromDT

FROM@Stage

WHEREGrpID = @GrpID

-- Get last datetime available

UPDATEs

SETs.ToDT = (SELECT MAX(x.DT) FROM @Sample AS x WHERE x.DT <= @dt)

FROM@Stage AS s

WHEREGrpID = @GrpID - 1

-- Get previous record

SET@GrpID = @GrpID - 1

END

-- Show the expected result

SELECT*

FROM@Stage

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 14

Yeah, no need for the clustered index, but a While loop is still RBAR and not really set-based. So you are using the table variable and the while to simulate a cursor and in this case 2. Also while I don't fully understand the behind the scenes working of Jeff's solution it is simple to read and I do understand what it is doing just not the how SQL Server is doing it.

Jack Corbett
Consultant - Straight Path Solutions
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