January 25, 2010 at 3:21 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:20 pm
Hi there,
Are you looking for something like this:
DECLARE @value int;
SET @value = 40;
select distinct Ranges.*
from Ranges inner join (
SELECT id, code
FROM dbo.Ranges AS r
WHERE r.dos > @value AND r.dos <(@value +6)
) r on r.id = Ranges.id OR r.Code = Ranges.Code
José Cruz
January 25, 2010 at 10:11 pm
Will this do?
SELECT DISTINCT * FROM RANGES R1
WHERE EXISTS (SELECT 1
FROM RANGES R2
WHERE R1.ID = R2.ID
AND R1.Code = R2.CODE
AND ABS(R1.DOS - R2.DOS)< 6
AND ABS(R1.DOS - R2.DOS) <> 0 )
ORDER BY ID, DOS
---------------------------------------------------------------------------------
January 26, 2010 at 9:36 am
Thank you very much for letting me know the code. This is what I have been looking for and it worked wonderfully.
Kind regards,
Akmerve
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply