Find subsequent days

SQL Apprentice Question
I have the following table:

CREATE TABLE [dbo].[ShortStay](
[SS_ID] [int] IDENTITY(1,1) NOT NULL,
[SS_WLID] [int] NULL,
[SS_From] [smalldatetime] NOT NULL,
[SS_Till] [smalldatetime] NOT NULL)


There are these records for example:


Insert into ShortStay (SS_WLID, SS_From, SS_Till)
values (3, '2006-05-05', '2006-05-21')
Insert into ShortStay (SS_WLID, SS_From, SS_Till)
values (3, '2006-05-02', '2006-05-04')
Insert into ShortStay (SS_WLID, SS_From, SS_Till)
values (3, '2006-05-22', '2006-05-25')
Insert into ShortStay (SS_WLID, SS_From, SS_Till)
values (4, '2006-05-05', '2006-05-21')
Insert into ShortStay (SS_WLID, SS_From, SS_Till)
values (3, '2006-05-26', '2006-05-29')


I'm trying to find a query that returns the number of consequent days
based on a certain date:


So if I input: '2006-05-09' and SS_WLID = 3
then I should get: 2006-05-02 till 2006-05-29 thus: 28 days.


You get 2006-05-02 because the SS_Till is the day before 2006-05-05
that's why this row is ok.


You get 2006-05-29 because:


2006-05-09 - 2006-05-21 - 2006-05-22 - 2006-05-25 - 2006-05-26 -
2006-05-29


I hope this makes sense. So the days have to be consequent in order to
be able to be part of the count.


I have been trying to join the table to itself but I'm getting nowhere.

Celko Answers
People will tell you to use a Calendar table. But your real problem is
a bad design. You have no key (IDENTITY cannot ever be a key! Quit
mimicing a sequential file), and your "wl_id" can be NULL so the query
is impossible to answer. You also have no constraints and use
proprietary data types. Then on top of all of that, you have attribute
splitting -- the fact of a continous stay is spread over many rows.

Why did you use a singular name for a table with more than one element
in it? You have no idea what an identifier is and stuck it on
everything. Your table should look more like this:


CREATE TABLE ShortStays
(wl_id INTEGER NOT NULL,
arrival_date DATETIME NOT NULL,
depart_date DATETIME NOT NULL,
CHECK(arrival_date < depart_date),
PRIMARY KEY (wl_id, arrival_date));


When a stay is extended, you update it instead of mimicking a paper
hotel register form. Ideally, you should have constraint to prevent
overlaps, but that is hard to do in SQL Server since it still lacks
much of the SQL-92 features. You can do it with a TRIGGER or with an
updatable VIEW that has WITH CHECK OPTION clause and a Calendar table.



>> There are these records for example: <<


Please learn the differences between rows and records. Fail to know
that lead to attribute splitting.