Checking values row by row

Question

Checking values row by row

Dugi

SSCoach

Points: 17998
More actions
September 16, 2008 at 4:16 am

#197861

Hi,
is there any script that can check row by row values for comparing the data is it in order or not, and then if the comparison of first with second value is true, must have one field that indicate that this comparison is 1 otherwise is 0 for the false!
is it possible or not!
Thnx!
Dugi
============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

Viewing 7 posts - 1 through 6 (of 6 total)

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

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 1

By definition, a table in unordered.

If you post the table structure, some test data, and some information about what you are trying to do, it may be possible to help you.

Forum best practices:

http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

Dugi SSCoach Points: 17998 More actions · Answer 2

Yes

There is the structure of the table:

TR_ID NVARCHAR(15)

TR_YEAR NVARCHAR(2)

TR_OFFICE NVARCHAR(4)

TR_DATECREATED DATETIME

Sample Data:

TR_ID TR_YEAR TR_OFFICE TR_DATECREATED (DD/MM/YYYY)

082011000000001 08 2011 01/01/2008

082011000000002 08 2011 01/01/2008

082011000000003 08 2011 02/01/2008

082011000000005 08 2011 02/01/2008

.

as you can see the 082011000000004 is missing and I want to compare row by row for the values in TR_ID as you can see it is incremental by 1 and find the missing TR_ID for example adding one col and if the order of TR_ID is correct that col must have the value 1 if the TR_ID is broken the col must have the value 0 ...something liike that!

thnx

Dugi

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 3

You still did a pretty poor job of posting usable sample data.

Here is a pretty simple way using a tally table.

[font="Courier New"]CREATE TABLE #MyTest

(

MyID VARCHAR(15)

)

INSERT #MyTest VALUES ('082011000000001')

INSERT #MyTest VALUES ('082011000000002')

INSERT #MyTest VALUES ('082011000000003')

INSERT #MyTest VALUES ('082011000000005')

INSERT #MyTest VALUES ('082011000000006')

; WITH MyList (Val)

AS (SELECT N + 82011000000000 FROM Tally)

SELECT

M.MyID

, CONVERT(BIGINT,M.MyID) AS MyIDInt

, T.Val

FROM

MyList T

LEFT JOIN #MyTest M ON T.Val = CONVERT(BIGINT,M.MyID)

WHERE

T.Val BETWEEN (SELECT MIN(CONVERT(BIGINT,MyID)) FROM #MyTest)

AND (SELECT MAX(CONVERT(BIGINT,MyID)) FROM #MyTest)[/font]

Dugi SSCoach Points: 17998 More actions · Answer 4

YEP your code here works perfect ... and I'm still working to retrieve the results from my table but nothing the results are zero!

I'm trying...to find the solution ...interesting why it is not working on my table!

thank you very much!

Dugi

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

Dugi SSCoach Points: 17998 More actions · Answer 5

ok there is my results you can see them in attach :

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

Dugi SSCoach Points: 17998 More actions · Answer 6

AHA...at the end the results I have the missing ID's ...now is correct !

sorry I didn't check the end of the results couz I thought that they will publish as your result in T-SQL above!

Now is OK !

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]