January 25, 2010 at 3:56 pm
Hello all,
I am trying to find duplicates when the range of date of service changes in 6 days. For instance in the following table with the same id number or code number, I could find date of service (dos) from 20 to 25 when I set the value 20. For this, I could implement the following simple query:
DECLARE @value int; SET @value = 20;
SELECT *
FROM dbo.Ranges AS r
--WHERE r.dos > @value AND r.dos <(@value +6)
WHERE n.dos BETWEEN 20 AND 25
id code dos
1 123 20
1 123 25
I do not want to set a value (or I can set 1) and then the query will fetch all matching values (same id and/or code number) with the range of 6 for dos. For instance, just a query could fetch the following values:
id code dos
1 123 20
1 123 25
1 123 250
1 123 255
4 224 43
4 224 45
I would appreciate any suggestions.
CREATE TABLE [dbo].[Ranges](
[Id] int NOT NULL,
[varchar](3) NOT NULL,
[dos] int NOT NULL,
) ON [PRIMARY]
INSERT INTO Ranges VALUES (1, '123', '20');
INSERT INTO Ranges VALUES (1, '123', '308');
INSERT INTO Ranges VALUES (1, '123', '255');
INSERT INTO Ranges VALUES (1, '123', '20');
INSERT INTO Ranges VALUES (1, '123', '25');
INSERT INTO Ranges VALUES (1, '123', '250');
INSERT INTO Ranges VALUES (1, '123', '300');
INSERT INTO Ranges VALUES (2, '124', '200');
INSERT INTO Ranges VALUES (3, '145', '230');
INSERT INTO Ranges VALUES (4, '224', '43');
INSERT INTO Ranges VALUES (4, '224', '45');
INSERT INTO Ranges VALUES (4, '224', '45');
January 25, 2010 at 6:38 pm
Do not cross post.
All replies at this thread.
http://www.sqlservercentral.com/Forums/Topic853346-149-1.aspx
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply