[SQL] SQL Find consecutive numbers in groups

I have a table similar to the one shown. It contains a list of user ids, the hour value for each hour of the day and an Avail flag to determine if that user is available on that hour.

I need to list all User ids which are available for a number of consecutive hours defined as @n

#####################
# UID # Avail # Hour#
#####################
# 123 # 1 # 0 #
# 123 # 1 # 1 #
# 123 # 0 # 2 #
# 123 # 0 # 3 #
# 123 # 0 # 4 #
# 123 # 1 # 5 #
# 123 # 1 # 6 #
# 123 # 1 # 7 #
# 123 # 1 # 8 #
# 341 # 1 # 0 #
# 341 # 1 # 1 #
# 341 # 0 # 2 #
# 341 # 1 # 3 #
# 341 # 1 # 4 #
# 341 # 0 # 5 #
# 341 # 1 # 6 #
# 341 # 1 # 7 #
# 341 # 0 # 8 #
######################


This should result in the following output for @n=3

#######
# UID #
#######
# 123 #
#######


I have attempted to use the ROW_NUMBER() over (partition by UID,Avail ORDER BY UID,Hour) to assign a number to each row partitioned by the UID and Whether or not they are flagged as available. However this does not work as the periods of availability may change multiple times a day and the ROW_NUMBER() function was only keeping two counts per user based on the Avail flag.

Continue reading...