Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Clustered dates problem

Author  Topic 

Aleph_0
Yak Posting Veteran

79 Posts

Posted - 2011-03-23 : 19:04:14
Hi there, I'm new to SQL and programming in general, so please bear with me! Here's the problem I have: in my table I keep ID numbers of people and visit dates. I want to start with the first date of a person and consider that an independent visit. Then I want to check if any visits by the same person occurred within 10 days remove them.

The next record will have a date of 11 days later or more, so I want to consider that another independent date. Then starting from this 2nd independent date, remove any visits within 10 days again, etc.

So this is my goal:
1/1/2011
1/2/2011
1/5/2011

1/17/2011
1/18/2011
1/20/2011
1/24/2011

1/29/2011

And here's what I've done so far:

SELECT a.idNum
, a.firstDate
, c.visitDate
, [Interval] = DateDiff(day, a.firstDate, c.visitDate)
FROM
(
SELECT idNum
, [firstDate] = MIN(visitDate)
FROM CList
GROUP BY idNum
) AS a
WHERE DateDiff(day, a.firstDate, c.visitDate) BETWEEN 1 AND 10


And now I'm stuck. I don't know how to do the same thing for the next visit. Please help! Thanks!

K-SaMa
Starting Member

7 Posts

Posted - 2011-03-23 : 19:15:52
umm, why don't you use a trigger that at the moment of visit, it checks if its date shoud be saved at all in the database, unless you want to keep dates of ALL visits and just filter them witha query.
Go to Top of Page

Aleph_0
Yak Posting Veteran

79 Posts

Posted - 2011-03-23 : 19:20:34
I'm creating a new table from an already existing one, and it's still important to have all the original records of all the visits. I'm the only one interested in filtering out extra visits within a 10-day timeframe. Will triggers help in that case too? I've never heard of those so I'll have to do some more reading.
Go to Top of Page

K-SaMa
Starting Member

7 Posts

Posted - 2011-03-23 : 20:06:28
okay man for this you'll need 'Cursors'
take a look at this :


--I'm supposing this is your old table containing ALL records of visits
create table old
(
idNum int,
visitDate datetime
)
GO

--Lets fill it out with the sample data you provided
insert into old values(1,'1/1/2011'),(1,'1/2/2011'),
(1,'1/5/2011'),(1,'1/17/2011'),(1,'1/18/2011'),
(1,'1/20/2011'),(1,'1/24/2011'),(1,'1/29/2011')
GO

--Let this be your NEW table in which we're only inserting FILTERED DATA
create table new
(
idNum int,
visitDate datetime
)
GO

--Lets insert the very first visit date since it's surely an 'independent date' as you said
insert into new
select idNum, MIN(visitDate) from old
group by idNum



--Creating some variables we need
DECLARE @idNUM int
DECLARE @visitDate datetime
Declare @lastIndependentDate datetime

--Now lets run through the data in the OLD table and only insert in NEW the rows those we need
DECLARE db_cursor CURSOR FOR
SELECT idNUM, visitDate
FROM old
order by visitDate

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @idNUM, @visitDate

WHILE @@FETCH_STATUS = 0
BEGIN

Select @lastIndependentDate=MAX(visitDate) FROM new WHERE idNUM=@idNUM
if(DATEDIFF(DAY,@lastIndependentDate,@visitDate)>10)
insert into new values (@idNUM,@visitDate)
FETCH NEXT FROM db_cursor INTO @idNUM, @visitDate
END

CLOSE db_cursor
DEALLOCATE db_cursor


if there's any uncomprehensible statement above just ask ;)
Go to Top of Page

Aleph_0
Yak Posting Veteran

79 Posts

Posted - 2011-03-23 : 20:14:29
That's going to take me a while to understand, but thanks in advance!
Go to Top of Page

K-SaMa
Starting Member

7 Posts

Posted - 2011-03-23 : 20:30:20
okay man maybe this will make it a bit easier hopefully :)


--WHAT you should do is to firstly empty your NEW table completly and execute the folowwing :

--insert the very first visit date since it's surely an 'independent date' as you said
insert into new
select idNum, MIN(visitDate) from old
group by idNum



--Creating some variables we need
DECLARE @idNUM int
DECLARE @visitDate datetime
Declare @lastIndependentDate datetime

--Google what a T-SQL Cursor is and what it's good for then..
--here's the declaration of the cursor
DECLARE db_cursor CURSOR FOR --this cursor's called db_cursor (you can name it whatever you want)
--it's a cursor on the following query result
SELECT idNUM, visitDate --we select the idNUMs and visit dates of people in your database
FROM old
order by visitDate --ad we order them ascendently on the dates column so as to easilly filter dates we don't want ;)

OPEN db_cursor --open the cursor
FETCH NEXT FROM db_cursor INTO @idNUM, @visitDate --put the query result's current row's idNUM in the variable @idNUM and visitDate in the variable @visitDate

WHILE @@FETCH_STATUS = 0 --while there are more rows to go through
BEGIN
--put the last 'independent date' in the variable @lastIndependentDate
--last independent date is surelly the maximum date in the NEW table since we ordered dates ascendently
--in the query before ;)
Select @lastIndependentDate=MAX(visitDate) FROM new WHERE idNUM=@idNUM
--now check wether the current date gotten from the old table is 10 days bigger than the last independent date
if(DATEDIFF(DAY,@lastIndependentDate,@visitDate)>10)
insert into new values (@idNUM,@visitDate) --if it's the case insert it in the new table,
FETCH NEXT FROM db_cursor INTO @idNUM, @visitDate -- else don't do anything and continue on with the next row
END
--once everything is done :
CLOSE db_cursor --close the cursor
DEALLOCATE db_cursor --free resources allocated by it

Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-24 : 07:53:55
If you are doing this only once, or if there are only a few hundred rows or so in the table, cursors would be ok, but many people here will advise you to avoid using cursors if that is not the case, and if possible even in such cases. I am not saying that :-) But, here is one way in which you could get the data without using cursors. I am using the same test data as K-SaMa
with cte1 as -- attaches the next visit date to every visit date
(
select
a.visitDate,
min(b.visitDate) nextDate
from
old a left join old b
on b.visitDate >= dateadd(dd,10,a.visitDate)
group by
a.visitDate
),
cte2 as -- traverse the chain using the next visit date.
(
select top 1
*
from
cte1
order by
visitDate
union all
select
a.visitDate,
a.NextDate
from
cte1 a
inner join cte2 b
on b.NextDate = a.visitDate
)
select visitDate
from cte2
order by visitDate;
Go to Top of Page

Aleph_0
Yak Posting Veteran

79 Posts

Posted - 2011-03-24 : 12:05:24
Thanks sunitabeck! I didn't know you could put an expression in the ON clause of a join, and the UNION ALL is new for me too. I appreciate your help!
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-24 : 14:49:04
YVW Aleph. The union all written the way it is in this query is a recursive common table expression - look it up, it is a neat and fun thing to mess around with!! And, it can even be of use sometimes.. like in this problem, for example... :--)
Go to Top of Page
   

- Advertisement -