SQL Query help - SQL Server Forums

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/

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

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

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources