SQL Server: View and DateTimes

Question

I have the following table Foo:

    start    |     end     |  cause
-------------+-------------+----------
 2007/12/22  |  2008/02/12 |     2
 2008/03/18  |  1900/01/01 |    -1

there are stored various periods of time. cause '-1' and end '1900/01/01' means the time period has no end. Under certain conditions, the application using this table writes records into the table that look like the following:

    start    |     end     |  cause
-------------+-------------+----------
 2007/12/22  |  2008/02/12 |     2
 2008/03/18  |  2008/05/16 |     9
 2008/05/17  |  1900/01/01 |    -1

A time period with cause '9' should be read over. Each each of those periods have another period starting the next day. Look at the 2nd and 3rd record of Foo, the time period ends with 2008/05/16 (cause '9'), and the next starts with 2008/05/17.

I want to create a view that looks like the following:

    start    |     end     |  cause
-------------+-------------+----------
 2007/12/22  |  2008/02/12 |    2
 2008/03/18  |  1900/01/01 |   -1

I hope it is clear what I am trying to do. I tried my best, but I don't get it. I hope somebody can help me.

I am using SQL Server 2005 Express and I know the whole table design is crap (since I would be best just to not write those 'cause 9' records), but it is a legacy design and I can't change it.

Answer 1

Try a case statement:

select start, 
case cause 
  When 9 THEN '1900/01/01'
  ELSE end
END as end,
case cause
  when 9 then -1
else cause 
end as cause
from 
Table

I don't have a sql server handy right now to test this but this should put you on the right track at least. Good luck!

Answer 2

Although your db design seems really wrong to me, (this sort of thing should be done in application logic),

try this.

Select s.Start StartDate, 
   Case S.Cause When 9 Then e.end else s.end End EndDate,
   Case S.Cause When 9 Then -1 Else s.Cause Cause 
From Table s 
    Left Join Table e 
         On e.Start = s.end
            And s.cause = 9
            And e.cause = -1
Where s.Cause <> -1

Answer 3

Just for the record, I'm not crazy about the table design, but this should give you what you want. There might be more efficient ways to do this (if so, I'd love to learn), but here you go:

SELECT [start],[end],[cause] FROM [foo]
WHERE [cause] != 9 AND [start] NOT IN (SELECT CAST(CAST([end] AS float)+1 AS smallDateTime) FROM foo WHERE [cause]=9)
UNION
SELECT 
(SELECT TOP 1 [start] FROM [foo] WHERE cause=9 AND [end] IN (SELECT CAST(CAST([start] AS float)-1 AS smallDateTime) FROM foo WHERE [start]=f.[start])) AS [start]
,f.[end],f.[cause] FROM [foo] f
WHERE f.[start] IN (SELECT CAST(CAST([end] AS float)+1 AS smallDateTime) FROM foo WHERE [cause]=9)

I'm basically selecting all the records that don't need to be altered and then unioning them with newly-constructed records that have the start from the cause=9 records and the end and cause from a record whose start is the next day. I made an arbitrary decision about which record to use if you have conflicting start records in the table. All of the casting is to get around SQL Server's non-existent date comparison tools.