comparative stored procedure

Question

Post reply

comparative stored procedure

sharonmtowler

Default port

Points: 1495
More actions
July 23, 2010 at 8:04 am

#221638

how do i create a stored procedure that compares records

i created a table that captures records and everyday a sp is run to add the next days records in.

i need to be able to compare the current value of a field to the previous value to see if the are the same.

this can have a full years worth of data.

how do i say if previous day on_ord_1 = next day on_ord_1 and so on. it has to be able to compare any values that have NOT changed regardless of how many replication days there are for the record.

SELECT

SYSTYL AS STYLE,

SYCLNM AS COLOR,

REPLICATION_DATE,

SYSEA AS SEASON,

SYASZ1 as ON_ORD_1,

SYASZ2 as ON_ORD_2,

SYASZ3 as ON_ORD_3,

SYASZ4 as ON_ORD_4,

SYASZ5 as ON_ORD_5,

SYASZ6 as ON_ORD_6,

SYASZ7 as ON_ORD_7,

SYASZ8 as ON_ORD_8,

SYASZ9 as ON_ORD_9,

SYFSZ1 AS ON_HND_1,

SYFSZ2 AS ON_HND_2,

SYFSZ3 AS ON_HND_3,

SYFSZ4 AS ON_HND_4,

SYFSZ5 AS ON_HND_5,

SYFSZ6 AS ON_HND_6,

SYFSZ7 AS ON_HND_7,

SYFSZ8 AS ON_HND_8,

SYFSZ9 AS ON_HND_9

FROM

dbo.STYLEMST_HISTORY

ORDER BY

SYSTYL

Viewing 5 posts - 1 through 4 (of 4 total)

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

Mike01 SSChampion Points: 11381 More actions · Answer 1

Sharon, can you provide table layouts, sample data and some expected results? Check out the first link in my signature on how to post to this site

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

sharonmtowler Default port Points: 1495 More actions · Answer 2

sharonmtowler

Default port

Points: 1495

July 23, 2010 at 8:23 am

#1197541

I think i have it figured out. dont have time to read all that information you posted for rules and regulations. i think they way i post my questions are fine.

Mike01 SSChampion Points: 11381 More actions · Answer 3

I'm glad that you just started coming to this forum and have it all figured out. As an FYI, we are volunteering our time to try to help others with their issues in our spare time. When you do get some time, I would recommend reading how to post to the forum. By providing Table layouts, sample data, your expected results and what you have tried, it helps us provide a relevant solution that is tested. But if you think "the way i post my questions are fine", then good luck.

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

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

sharonmtowler (7/23/2010)

i think they way i post my questions are fine.

In that case, Use Row_Number to number a the output of a CTE and perform a self join comparision with the CTE with a unit offset based on the Row_Number column.

--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)
Intro to Tally Tables and Functions