Hi all, hoping you can help me.
Here's some sample data:
DATE PHONE DUPE
1/27/2006 8888888888 0
2/22/2006 8888888888 0
2/25/2006 8888888888 0
3/30/2006 8888888888 0
2/10/2006 7777777777 0
2/10/2006 7777777777 0
3/18/2006 7777777777 0
etc...
What i'd like to do is make DUPE = 1 if less than 30 days has passed since
he last called, so in the above sample it should look like this after:
DATE PHONE DUPE
1/27/2006 8888888888 0
2/22/2006 8888888888 1
2/25/2006 8888888888 1
3/30/2006 8888888888 0
2/10/2006 7777777777 0
2/10/2006 7777777777 1
3/18/2006 7777777777 0
etc...
There are hundreds of thousands of records in the table and i'm currently
using a proc that uses cursors and it's taking way too long and is very
resource heavy.
Thanks in advance to anyone that can helpDo you plan on updating DUPE for all the records with the same phone
number or only the only the most recent entry...with DUPE. Because if
the person called 3 years ago, and then yesterday and then today, will
all 3 have DUPE updated, because of todays call.In this case you are
updating records which could be so far down in the table and this may
be unneccesary'
Have you considered making a view which shows all distinct phone
numbers which were called in the last 30 days and the last time it was
called ...MAX(calldate) ...then using a JOIN to get occurences after
the date in the view.and update the record based on that.|||Are you saving time part also?
create table t1 (
[date] datetime,
phone varchar(10),
dupe tinyint
)
go
insert into t1 values('2006-01-27 00:00:00.000', '8888888888', 0)
insert into t1 values('2006-02-22 00:00:00.000', '8888888888', 0)
insert into t1 values('2006-02-25 00:00:00.000', '8888888888', 0)
insert into t1 values('2006-03-30 00:00:00.000', '8888888888', 0)
insert into t1 values('2006-02-10 00:00:00.000', '7777777777', 0)
insert into t1 values('2006-02-10 01:00:00.000', '7777777777', 0) <--
trick to diff calls
insert into t1 values('2006-03-18 00:00:00.000', '7777777777', 0)
go
update t1
set dupe = 1
where [date] < dateadd(day, 30, (select min(t2.[date]) from t1 as t2 where
t2.phone = t1.phone and t2.[date] < t1.[date]))
go
select
*
from
t1
order by
phone, [date]
go
AMB
"ttrottier" wrote:
> Hi all, hoping you can help me.
> Here's some sample data:
> DATE PHONE DUPE
> 1/27/2006 8888888888 0
> 2/22/2006 8888888888 0
> 2/25/2006 8888888888 0
> 3/30/2006 8888888888 0
> 2/10/2006 7777777777 0
> 2/10/2006 7777777777 0
> 3/18/2006 7777777777 0
> etc...
> What i'd like to do is make DUPE = 1 if less than 30 days has passed since
> he last called, so in the above sample it should look like this after:
> DATE PHONE DUPE
> 1/27/2006 8888888888 0
> 2/22/2006 8888888888 1
> 2/25/2006 8888888888 1
> 3/30/2006 8888888888 0
> 2/10/2006 7777777777 0
> 2/10/2006 7777777777 1
> 3/18/2006 7777777777 0
> etc...
>
> There are hundreds of thousands of records in the table and i'm currently
> using a proc that uses cursors and it's taking way too long and is very
> resource heavy.
> Thanks in advance to anyone that can help|||UPDATE Whatever
SET DUPE = 1
WHERE EXISTS
(select * from Whatever as D
where Whatever.PHONE = D.PHONE
and Whatever.Date < D.Date)
This would be helped a lot by an index on (PHONE, DATE).
Roy Harvey
Beacon Falls, CT
On Wed, 29 Mar 2006 08:30:02 -0800, ttrottier
<ttrottier@.discussions.microsoft.com> wrote:
>Hi all, hoping you can help me.
>Here's some sample data:
>DATE PHONE DUPE
>1/27/2006 8888888888 0
>2/22/2006 8888888888 0
>2/25/2006 8888888888 0
>3/30/2006 8888888888 0
>2/10/2006 7777777777 0
>2/10/2006 7777777777 0
>3/18/2006 7777777777 0
>etc...
>What i'd like to do is make DUPE = 1 if less than 30 days has passed since
>he last called, so in the above sample it should look like this after:
>DATE PHONE DUPE
>1/27/2006 8888888888 0
>2/22/2006 8888888888 1
>2/25/2006 8888888888 1
>3/30/2006 8888888888 0
>2/10/2006 7777777777 0
>2/10/2006 7777777777 1
>3/18/2006 7777777777 0
>etc...
>
>There are hundreds of thousands of records in the table and i'm currently
>using a proc that uses cursors and it's taking way too long and is very
>resource heavy.
>Thanks in advance to anyone that can help|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
Is this what you meant?
CREATE TABLE PhoneLog
(call_date DATETIME NOT NULL,
phone_nbr CHAR(10) NOT NULL,
dup_flag INTEGER DEFAULT 0 NOT NULL
CHECK (dupe_flag IN (0,1)),
PRIMARY KEY (call_date, phone_nbr));
Try another approach -- relational, not computatinal! Adjust the
following table for 30 business days rather than calendar days if you
need to.
CREATE TABLE ReportRanges30days
(start_date DATETIME NOT NULL,
end_date DATETIME NOT NULL,
CHECK (start_date < end_date),
PRIMARY KEY (start_date, end_date));
Ten years will fit into main storage so now use a join whch can get the
covering index:
UPDATE PhoneLog
SET dup_flag
= CASE WHEN
EXISTS (SELECT *
FROM ReportRanges30days
WHERE call_date
BETWEEN start_date AND end_date)
THEN 1 ELSE 0 END
You might also put this into a VIEW and avoid updating all the time.|||Hi and thanks for the reply.. but this isn't quite what i need,
if you run the following code, you'll see that the last record
is not flagged as a duplicate, but it should be since it was
within 30 days of the previous record.. any ideas?
Thanks very much.
drop table t1
create table t1 (
[date] datetime,
phone varchar(10),
dupe tinyint
)
go
insert into t1 values('2005-08-30 13:39:00', '8888888888', 0)
insert into t1 values('2005-08-30 13:41:00', '8888888888', 0)
insert into t1 values('2005-12-21 10:03:00', '8888888888', 0)
insert into t1 values('2005-12-21 10:05:00', '8888888888', 0)
go
update t1
set dupe = 1
where [date] < dateadd(day, 30, (select min(t2.[date]) from t1
as t2 where
t2.phone = t1.phone and t2.[date] < t1.[date]))
go
select * from t1 order by phone, [date] go
---
Posted with NewsLeecher v2.0 Beta 5
* Binary Usenet Leeching Made Easy
* http://www.newsleecher.com/?usenet
---|||On Fri, 31 Mar 2006 15:10:35 GMT, ttrottier wrote:
>Hi and thanks for the reply.. but this isn't quite what i need,
>if you run the following code, you'll see that the last record
>is not flagged as a duplicate, but it should be since it was
>within 30 days of the previous record.. any ideas?
Hi ttrottier,
UPDATE t1
SET dupe = 1
WHERE EXISTS
(SELECT *
FROM t1 AS d
WHERE t1.Date > d.Date
AND t1.Date <= DATEADD(day, 30, d.Date))
Hugo Kornelis, SQL Server MVP
