Can this be simplified?

I have the following table,
CREATE TABLE [dbo].[XXX_TBL] (
[PERSON_ID] [nchar] (10) NOT NULL,
[BEGIN_DT] [datetime] NOT NULL,
[END_DT] [datetime] ,
[TYPE1_ID] [nchar] (10) ,
[TYPE2_ID] [nchar] (10) )
that charts two attributes (TYPE1_ID, TYPE2_ID) assigned to a person,
over time.
The end date field can be left blank and in that case the end date of
the row would be either the day before the next row starts in the table
for that person, or if this does not exist, then the record is valid
until the end of time!
Suppose we have the following rows in the table,
1001, 2005-01-01, NULL, Value 1, Value A
1001, 2005-01-08, NULL, Value 2, Value B
1001, 2005-01-15, NULL, Value 2, Value C
1001, 2005-01-22, NULL, Value 1, Value D
1001, 2005-01-29, NULL, Value 2, Value E
1001, 2005-02-05, NULL, Value 2, Value F
All I am interested in is the values in column, TYPE1_ID. So really
the information in this table looks like,
1001, 2005-01-01, NULL, Value 1
1001, 2005-01-08, NULL, Value 2
1001, 2005-01-22, NULL, Value 1
1001, 2005-01-29, NULL, Value 2
What is the easiest what to manipulate the data in the XXX_TBL table,
so that it looks like above?
I have been able to do this, but only by creating serveral views. The
net effect of is, it is fine if I am looking at a small number of
people only, but if I look at the whole table (about 13500 people, and
around 14500 rows) I get space issues with tempdb.
My method was,
1 - Create a view, VW1, to list both a start and end date for every row
and only show the TYPE1_ID attribute.
So we get,
1001, 2005-01-01, 2005-01-07, Value 1
1001, 2005-01-08, 2005-01-14, Value 2
1001, 2005-01-15, 2005-01-21, Value 2
1001, 2005-01-22, 2005-01-28, Value 1
1001, 2005-01-29, 2005-02-04, Value 2
1001, 2005-02-05, 9999-12-31, Value 2
2 - Join VW1 to a calendar table (a table with a row for each day) so
that we get a row for each day that an individual row is valid for.
ie,
1001, 2005-01-01, 2005-01-07, Value 1
would be transformed to (7 rows),
1001, 2005-01-01, 2005-01-07, Value 1, 2005-01-01
1001, 2005-01-01, 2005-01-07, Value 1, 2005-01-02
...
...
1001, 2005-01-01, 2005-01-07, Value 1, 2005-01-06
1001, 2005-01-01, 2005-01-07, Value 1, 2005-01-07
then save this SQL as VW_2
3 - Join VW_2 to itself where the new date field = row valid for the
next day. This way be can compare the values for TYPE1_ID and identify
on which days it changes. Create a view VW_3 from the above.
So we finally get,
1001, 2005-01-01, 2005-01-07, Value 1
1001, 2005-01-08, 2005-01-21, Value 2
1001, 2005-01-22, 2005-01-28, Value 1
1001, 2005-01-29, 9999-12-31, Value 2
which is the abbreviated data that we originally above. However this
is rather long winded!
Can this be simplified?...
Many thanks in advance.
JonathanI simplified the table:
drop TABLE [dbo].[XXX_TBL]
go
CREATE TABLE [dbo].[XXX_TBL] (
[PERSON_ID] [nchar] (10) NOT NULL,
[BEGIN_DT] [datetime] NOT NULL,
[END_DT] [datetime] ,
[TYPE1_ID] [int])
here is my test data:
insert into [dbo].[XXX_TBL] values('1001', '20050101', NULL, 1)
insert into [dbo].[XXX_TBL] values('1001', '20050103', '20050201', 1
)
insert into [dbo].[XXX_TBL] values('1001', '20050202', NULL, 1)
insert into [dbo].[XXX_TBL] values('1001', '20050209', NULL, 2)
insert into [dbo].[XXX_TBL] values('1001', '20050215', '20050217', 2
)
insert into [dbo].[XXX_TBL] values('1001', '20050219', '20050222', 2
)
insert into [dbo].[XXX_TBL] values('1001', '20050223', null, 2)
insert into [dbo].[XXX_TBL] values('1001', '20050225', null, 1)
select * from dbo.xxx_tbl x1
where not exists(select 1 from dbo.xxx_tbl x2
where x1.person_id=x2.person_id
and x1.type1_id = x2.type1_id
and x2.begin_dt < x1.begin_dt
and ( dateadd(day, 1, x2.end_dt) = x1.begin_dt
or
((x2.end_dt is null)
and not exists(select 1 from dbo.xxx_tbl x3 where
x3.person_id=x2.person_id
and( x3.type1_id <> x2.type1_id or (x3.end_dt is not null))
and (x2.begin_dt < x3.begin_dt) and (x3.begin_dt <
x1.begin_dt))
)
)
)
order by begin_dt
take over from this point, test more - I'm gonna have my lunch now
;)|||That's very much for your reply! It's been a big help. Much
appreciated.