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
 SQL Server 2012 Forums
 Transact-SQL (2012)
 SQL Query help

Author  Topic 

SQLBoy14
Yak Posting Veteran

70 Posts

Posted - 2014-09-17 : 23:55:28
How do I do the sql query to output only the data within 2 days as shown in the figure Table2? Here is the logic. Look at the Table 1 below.

Table1:
Student VisitDate
A 9/1/2014
A 9/1/2014
A 9/5/2014
A 9/12/2014
A 9/13/2014
A 9/13/2014
A 9/16/2014
A 9/16/2014
A 9/21/2014

I want to display the data/row for Student A where the VisitDate within 48 hours. The output like below image.

Table2:
Student VisitDate
A 9/1/2014
A 9/12/2014
A 9/13/2014
A 9/16/2014

Explaination:

VisitDate 09/01/2014 display because it has two visited within the same day. Still consider within 2 days.

VisitDate 09/05/2014 dropped, because from 09/01/2014 to 09/05/2014, that is more than 2 days and from 09/05/2014 to 09/12/2014 also more than 2 days. So, row like this condition will be dropped.

VisitDate 09/12/2014 to 09/13/2014. Keep 09/12/2014 and 09/13/2014 because within 2 days.

VistDate 09/16/2014. from VisitDate 09/13/2014 to 09/16/2014 is more than 48 hours but from 09/16/2014 to 09/16/2014 is within 2 days, so keep just one 09/16/2014 and dropped the other 09/16/2014

VisitDate 09/16/2014 to 09/21/2014. It is more than 2 days/ So row like this condition will be dropped.

SQLBoy

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2014-09-18 : 01:13:41
Something like this:


WITH studvisits AS
(
SELECT student, visitdate, rownum = ROW_NUMBER() OVER (ORDER BY visitdate)
FROM table1
)
SELECT s1.student, s1.visitdate FROM studvisits s1 join studvisits s2
ON s1.rownum = s2.rownum-1
WHERE DATEDIFF(d, s1.visitdate, s2.visitdate) <= 2


Harsh Athalye
http://www.letsgeek.net/
Go to Top of Page

SQLBoy14
Yak Posting Veteran

70 Posts

Posted - 2014-09-18 : 13:11:36
Hello harsh_athalye,
A 9/1/2014
Thank you for the query. The output almost what I am looking for but row of A 9/1/2014 displayed twice.
So, instead of
A 9/1/2014
A 9/12/2014
A 9/13/2014
A 9/16/2014

it appeared as
A 9/1/2014
A 9/1/2014
A 9/12/2014
A 9/13/2014
A 9/16/2014



SQLBoy
Go to Top of Page

SQLBoy14
Yak Posting Veteran

70 Posts

Posted - 2014-09-18 : 13:28:52
Hello harsh_athalye,

Never mind. Sorry for the confusion. I had triple row of A 09/01/2014. I got the output what I am looking for.

Thank you harsh_athalye

SQLBoy
Go to Top of Page

SQLBoy14
Yak Posting Veteran

70 Posts

Posted - 2014-09-18 : 18:56:46
Hello Harsh_athalye,

Where should I declare my daterange? I got an error when I declare my date1 and date2 on top of the query.

SQLBoy
Go to Top of Page
   

- Advertisement -