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)

query Help

Author

Topic

SQLBoy14
Yak Posting Veteran

70 Posts

Posted - 2014-09-17 : 00:41:39

Hi all,

How do I query if I only want to show the Students who only do the visit within 48 hours between each visit? Per below table, I only want Student A on the visit # 1,2 and 3 because the visit is within 48 hours between visit #1,2 and 3. I will not care about visit 4 for student A because it is out of 48 hour range in between visits.
For Student B I only need to display the visit # 2 and 3 because they are within 48 hours range on each visit.
For Student C I only need to display the visit # 1,2,3,4, and 5 because between visit # 1 and 2 are within 48 hours, and between visit # 3,4 and 5 are within 48 hours.

So basically what I want to do is to do query that would give me the output like on Table 2 below.

Table 1:
STUDENT VISIT DATE
A 1 09/01/2014
A 2 09/02/2014
A 3 09/03/2014
A 4 09/07/2014
B 1 09/01/2014
B 2 09/06/2014
B 3 09/07/2014
C 1 09/02/2014
C 2 09/03/2014
C 3 09/07/2014
C 4 09/08/2014
C 5 09/09/2014
C 6 09/12/2014

Table 2:
STUDENT VISIT DATE
A 1 09/01/2014
A 2 09/02/2014
A 3 09/03/2014
B 2 09/06/2014
B 3 09/07/2014
C 1 09/02/2014
C 2 09/03/2014
C 3 09/07/2014
C 4 09/08/2014
C 5 09/09/2014

Thank you all

SQLBoy

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2014-09-17 : 07:49:44

SELECT STUDENT , VisitID , VisitedDate
FROM ( SELECT datediff(hh,visitedDate, ISNULL(LEAD(VisitedDate) OVER( PARTITION BY Student order by VisitID), VisitedDate)) VisitsTimeDiff , * FROM Table1) A
WHERE VisitsTimeDiff <=48

--
Chandu

SQLBoy14
Yak Posting Veteran

70 Posts

Posted - 2014-09-17 : 09:34:37

Hello Bandi,

I tried and I got nothing in the output.

SQLBoy

SQLBoy14
Yak Posting Veteran

70 Posts

Posted - 2014-09-17 : 11:52:01

Hello ...Bandi,
Please disregard my previous reply. I got the output but it is not whtt I am trying to do. What I want to display is, for example:Student A. I Want to display student A only if student A visited within 48 hours in between calls, like is call #1 to call #2 within 48 hours? Is call from #2 to call #3 within 48 hours and so on. If the call within 48 hours, that item or record need to be on the output.

Thank Bandi for helping.

SQLBoy

SQLBoy14
Yak Posting Veteran

70 Posts

Posted - 2014-09-17 : 22:53:59

Hi Bandi,

So the logic would be like this..based on the Visited order
Student A: Is VisitID # 1 and # 2 within 48 hours (or 2 days)? If its within 48 hours(2 days) then Visit #1 and #2 will be on the output. Then, is the VisitID #2 and #3 within 48 hours? If it is, then Visit#3 will be in the output, if its not then it will drop (not on the output)
StudentB: Same logic as Student A.
Student C: Same logic as Student B

In your query, I got the output but the value start from row 1 instead row 2. How do I have the VisitTimeDiff value start on row 2? if so, I can each row on the output if the VisitTimeFiff >= 48 or VisitTimeDiff > 2?

Thank you Bandi

SQLBoy

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources