/*Matching Adjacent Rows based on a consecutive value*/

CREATE TABLE Nums

(nbr INTEGER NOT NULL PRIMARY KEY,

val INTEGER NOT NULL);

INSERT INTO Nums (nbr, val)

VALUES (1, 0), (5, 7), (9, 4);

--===========Mohammad...

Sorry for late reply.

Here you are:

SET STATISTICS IO ON;

SET STATISTICS TIME ON;

--Mohammad Salimabadi Solution

WITH C0 AS

(

SELECT Ph.*, I.item,

ROW_NUMBER() OVER...

Cade Roux (4/12/2011)

;WITH C AS

(

SELECT seq_nbr, k,

DENSE_RANK() OVER(ORDER BY seq_nbr ASC) + k AS grp_fct

FROM [Sample]

...

>>

Hi Mohammad. I can run this on my data - right now it looks like it's picking out the current, previous and next 'date' or seq-nbr? What I'd need is...

Can you demonstrate your technique for matching current rows with previous and next values is faster and more efficient as my best solution?

See:

CREATE TABLE [Sample]

(

seq_nbr INTEGER NOT NULL PRIMARY KEY

);

INSERT...

Thank you for the link.

But I wrote a set-based approach:

WITH C AS

(SELECT '0' AS i

UNION SELECT '1')

SELECT ROW_NUMBER() OVER(ORDER BY v) - 1 AS decimal_value,

...

Thank you,

Please compare the results.

Declare @t table (v integer not null primary key, check(v >= 0));

Insert @t values (0)

Insert @t values (1)

Insert @t values (2)

Insert @t values (3)

Insert @t values...

Hi,

Here is a new approach for splitting a delimited string. It is set-based, without CHARINDEX, using number table…

See:

DECLARE @s-2 VARCHAR(620)='item_01,item_02,item_03';

SET @s-2 = ','+ @s-2...

Could you introduce an article about values of data integrity and enforcing and implementing it?

And Do we must implement it with using DDL or we can implementing it at front...

Okay I find the mistake and fix it

Here the solution:

Declare @t table (id int, score int)

insert @t select 1, 1

union select 20, 3

union select 300, 4

union select 4000, 2;

select *...

The solution (at the second post in this topic) will not word when my sample data be like following, and my desire result is:

Declare @t table (id int, score int)

insert...

Remove duplicate side-by-side characters from a string

New approach using numbers table

CREATE FUNCTION dbo.fnRemoveDupesI (@String VARCHAR(8000)) 

RETURNS VARCHAR(8000) 

AS

BEGIN

    DECLARE @result VARCHAR(8000) = '';

   ;WITH DataOrder

   AS

   (

      SELECT ID, Data

             ,ROW_NUMBER() OVER (PARTITION BY Data ORDER BY ID) AS RowNum

        FROM (SELECT SUBSTRING(@String, nbr, 1), nbr 

                FROM Nums 

               WHERE nbr <= LEN(@String)

             ) D(data, ID)

   )

  SELECT @result = @result + Data

    FROM (SELECT ID, Data

                 ,DENSE_RANK() OVER (ORDER BY ID - RowNum) As [Rank]

            FROM DataOrder

         )D

   GROUP BY Data, [Rank]

   ORDER BY MIN(ID)

   RETURN @result

END;

THANK YOU ALL

SELECT TOP 100 CLACCT, DATE

FROM

(

SELECT CLACCT, CAST(MONTH AS VARCHAR(2)) + CAST(YEAR AS VARCHAR(4)) AS DATE

FROM cu102 (nolock)

) D

WHERE DATE > 062007

Thank you,

But I do not mean publishing number table, I mean Splitting method is not simpler?

Also, A simplified method for publishing number table (no loop, no recursion, and no...