Interesting T-SQL problems

When I wrote my first article with the same name here, Interesting T-SQL problems, I was thinking it will be a weekly series. However, as it often happens, not all intentions become a reality.So, about a month and a half later, I want to discuss a few problems I encountered in different forums during the time since the last blog, that somehow caught my attention.

The first problem is found here (MSDN thread) and this is a problem of finding duplicates with a twist: the duplicates are considered duplicates by FirstName, LastName, Address, EMail, but also the interval between incoming_timestamp should be less than 4 minutes. Now, let's create the test table simulating the real table and populate it randomly with duplicates:

set nocount on
if OBJECT_ID('Test','U') is not null drop table Test

CREATE TABLE [dbo].[TEST](
[ID] [int] NOT NULL,
[LASTNAME] [varchar](60) NULL ,
[FIRSTNAME] [varchar](60)NULL ,
[ADDRESS1] [varchar](40) NULL,
[HOMEPHONE] [varchar](14) NULL,
[EMAIL] [varchar](70) NULL,
[INCOMING_TIMESTAMP] [datetime] NULL
) ON [PRIMARY]

declare @i int
set @i = 0

while @i < 100000
begin
set @i= @i+1

insert into Test values(@i, 'K','Th','Add','Ph','Em', dateadd(ms, CHECKSUM(newid()), getdate()) )

Read more: Beyond Relational

Posted via email from .NET Info