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.
| 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/20111/2/20111/5/20111/17/20111/18/20111/20/20111/24/20111/29/2011And 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 CListGROUP BY idNum) AS aWHERE DateDiff(day, a.firstDate, c.visitDate) BETWEEN 1 AND 10And 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. |
 |
|
|
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. |
 |
|
|
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 visitscreate table old(idNum int,visitDate datetime)GO--Lets fill it out with the sample data you providedinsert 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 DATAcreate table new(idNum int,visitDate datetime)GO--Lets insert the very first visit date since it's surely an 'independent date' as you saidinsert into newselect idNum, MIN(visitDate) from oldgroup by idNum--Creating some variables we needDECLARE @idNUM intDECLARE @visitDate datetimeDeclare @lastIndependentDate datetime--Now lets run through the data in the OLD table and only insert in NEW the rows those we needDECLARE db_cursor CURSOR FOR SELECT idNUM, visitDate FROM oldorder by visitDate OPEN db_cursor FETCH NEXT FROM db_cursor INTO @idNUM, @visitDateWHILE @@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, @visitDateEND CLOSE db_cursor DEALLOCATE db_cursor if there's any uncomprehensible statement above just ask ;) |
 |
|
|
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! |
 |
|
|
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 saidinsert into newselect idNum, MIN(visitDate) from oldgroup by idNum--Creating some variables we needDECLARE @idNUM intDECLARE @visitDate datetimeDeclare @lastIndependentDate datetime--Google what a T-SQL Cursor is and what it's good for then..--here's the declaration of the cursorDECLARE 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 resultSELECT idNUM, visitDate --we select the idNUMs and visit dates of people in your databaseFROM oldorder 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 cursorFETCH 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 @visitDateWHILE @@FETCH_STATUS = 0 --while there are more rows to go throughBEGIN --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 rowEND --once everything is done : CLOSE db_cursor --close the cursorDEALLOCATE db_cursor --free resources allocated by it |
 |
|
|
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-SaMawith 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; |
 |
|
|
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! |
 |
|
|
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... :--) |
 |
|
|
|
|
|
|
|