Viewing 15 posts - 1 through 15 (of 40 total)
/*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...
April 27, 2011 at 7:50 am
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...
April 14, 2011 at 8:38 am
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]
...
April 12, 2011 at 1:28 pm
>>
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...
April 12, 2011 at 1:22 pm
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...
April 12, 2011 at 10:04 am
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,
...
April 7, 2011 at 3:00 pm
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...
April 7, 2011 at 12:02 pm
Hi,
Here is a new approach for splitting a delimited string. It is set-based, without CHARINDEX, using number table…
See:
DECLARE @S VARCHAR(620)='item_01,item_02,item_03';
August 17, 2010 at 10:24 am
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...
July 13, 2010 at 7:22 am
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 *...
June 21, 2010 at 4:03 am
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...
June 21, 2010 at 3:25 am
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;
May 28, 2010 at 3:27 am
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
May 25, 2010 at 9:43 am
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...
May 23, 2010 at 4:39 pm
Viewing 15 posts - 1 through 15 (of 40 total)