SQL help - consecutive nights stay query

Tools

SQL help - consecutive nights stay query

I have a table with the below sample data (minus 'desired output' column).  I need to figure out how to query that data to get the values in the 'desired output' column.  I only care about the EMPL_ID, DATE_IN, DATE_OUT data and want to determine the total number of consecutive nights the employee stayed in a hotel (no matter what hotel). 

For example b/w 6/6/11 & 6/17/11 the employee stayed 10 consecutive nights at a hotel (spanning 3 different hotels).

PLEASE HELP!!!!!










TRX_ID TRANS_NO EMPL_ID HOTEL_NUM DATE_IN DATE_OUT desired

output
1 202339 123456 56584 5/25/2011 5/26/2011 2
3 202339 123456 56584 5/26/2011 5/27/2011 2
4 555888 123456 58183 5/26/2011 5/27/2011 1
5 448899 123456 26149 6/6/2011 6/7/2011 10
9 448899 123456 26149 6/7/2011 6/8/2011 10
8 448899 123456 26149 6/8/2011 6/9/2011 10
5 448899 123456 26149 6/9/2011 6/10/2011 10
16 134810 123456 26188 6/10/2011 6/11/2011 10
11 598748 123456 28174 6/11/2011 6/12/2011 10
22 598748 123456 28174 6/12/2011 6/13/2011 10
28 598748 123456 28174 6/13/2011 6/14/2011 10
36 598748 123456 28174 6/14/2011 6/15/2011 10
37 598748 123456 28174 6/15/2011 6/16/2011 10
40 598748 123456 28174 6/16/2011 6/17/2011 10
44 598748 123456 28174 6/20/2011 6/21/2011 3
50 598748 123456 28174 6/21/2011 6/22/2011 3
51 598748 123456 28174 6/22/2011 6/23/2011 3
Tags (2)
1 REPLY
Senior Apprentice

Re: SQL help - consecutive nights stay query

You probably need to a query calculating a flag based on "date_in = date_out of the previous row".

This is quite similar:

http://forums.teradata.com/forum/database/how-to-find-the-continious-records-based-on-key-column

But i don't understand the calculation for the 2nd/3rd row:

The same employee in two different hotels at the same time?

And why is row #3 retruning 1?

Dieter