June 12, 2013 at 11:03 am
I am on SQL 2000 -
I have a table that carries ID and values by change dates (Start date and end date).
Of the many values that can change I just want to pick start and end date for only one of those.
If I pick records for just that one VALUE the records look like this - it can have more than one records where the value does not change because may be some other value column was changing ( which I do not care about)
id----start date---------end date-----------Value
32012-04-03 00:00:002012-04-06 00:00:001
32012-04-07 00:00:002012-04-10 00:00:000
32012-04-11 00:00:002012-04-13 00:00:001
32012-04-14 00:00:002012-04-14 00:00:001 --these are not reqd
32012-04-15 00:00:002012-05-01 00:00:001 --these are not reqd
32012-05-02 00:00:002012-07-30 00:00:000
32012-07-31 00:00:002012-07-31 00:00:001
32012-08-01 00:00:002012-10-26 00:00:001 --these are not reqd
32012-10-27 00:00:002012-11-19 00:00:001 --these are not reqd
32012-11-20 00:00:002012-11-25 00:00:001 --these are not reqd
I have this above data in a table and want to run a SQL that can give me an output as below - giving me only those recs where TYPE is changing along with a new end date for that change.
id----start date---------end date----------Value--new_end_date
32012-04-03 00:00:002012-04-06 00:00:0012012-04-06 00:00:00
32012-04-07 00:00:002012-04-10 00:00:0002012-04-10 00:00:00
32012-04-11 00:00:002012-04-13 00:00:0012012-05-01 00:00:00
32012-05-02 00:00:002012-07-30 00:00:0002012-07-30 00:00:00
32012-07-31 00:00:002012-07-31 00:00:0012012-11-25 00:00:00
Here is SQL to create data
drop table test_data
create table test_data (
id int,
start_dt smalldatetime,
end_dt smalldatetime,
type bit
)
insert into test_data values(1, '2012-01-01', '2012-01-15', 0)
insert into test_data values(1, '2012-01-16', '2012-01-20', 0)
insert into test_data values(1, '2012-01-21', '2012-01-25', 1)
insert into test_data values(1, '2012-01-26', '2012-01-31', 0)
insert into test_data values(1, '2012-02-01', '2012-02-15', 1)
insert into test_data values(1, '2012-02-16', '2012-02-25', 1)
insert into test_data values(1, '2012-02-26', '2012-02-26', 1)
insert into test_data values(1, '2012-02-27', '2012-03-05', 0)
insert into test_data values(1, '2012-03-06', '2012-03-15', 0)
insert into test_data values(1, '2012-03-06', '2012-03-17', 0)
insert into test_data values(1, '2012-03-18', '2012-03-18', 1)
insert into test_data values(2, '2012-01-01', '2012-01-09', 0)
insert into test_data values(2, '2012-01-10', '2012-01-11', 0)
insert into test_data values(2, '2012-01-12', '2012-01-17', 1)
insert into test_data values(2, '2012-01-18', '2012-01-20', 1)
insert into test_data values(2, '2012-01-21', '2012-02-09', 1)
insert into test_data values(2, '2012-02-10', '2012-02-19', 0)
insert into test_data values(2, '2012-02-20', '2012-02-21', 1)
insert into test_data values(2, '2012-02-22', '2012-03-03', 1)
insert into test_data values(2, '2012-03-04', '2012-03-19', 1)
insert into test_data values(3, '2012-04-03', '2012-04-06', 1)
insert into test_data values(3, '2012-04-07', '2012-04-10', 0)
insert into test_data values(3, '2012-04-11', '2012-04-13', 1)
insert into test_data values(3, '2012-04-14', '2012-04-14', 1)
insert into test_data values(3, '2012-04-15', '2012-05-01', 1)
insert into test_data values(3, '2012-05-02', '2012-07-30', 0)
insert into test_data values(3, '2012-07-31', '2012-07-31', 1)
insert into test_data values(3, '2012-08-01', '2012-10-26', 1)
insert into test_data values(3, '2012-10-27', '2012-11-19', 1)
insert into test_data values(3, '2012-11-20', '2012-11-25', 1)
I would like the output to look like this below
id----start date----------end date---------Value--new_end_date
12012-01-01 00:00:002012-01-15 00:00:0002012-01-20 00:00:00
12012-01-21 00:00:002012-01-25 00:00:0012012-01-25 00:00:00
12012-01-26 00:00:002012-01-31 00:00:0002012-01-31 00:00:00
12012-02-01 00:00:002012-02-15 00:00:0012012-02-26 00:00:00
12012-02-27 00:00:002012-03-05 00:00:0002012-03-17 00:00:00
12012-03-18 00:00:002012-03-18 00:00:0012012-03-18 00:00:00
22012-01-01 00:00:002012-01-09 00:00:0002012-01-11 00:00:00
22012-01-12 00:00:002012-01-17 00:00:0012012-02-09 00:00:00
22012-02-10 00:00:002012-02-19 00:00:0002012-02-19 00:00:00
22012-02-20 00:00:002012-02-21 00:00:0012012-03-19 00:00:00
32012-04-03 00:00:002012-04-06 00:00:0012012-04-06 00:00:00
32012-04-07 00:00:002012-04-10 00:00:0002012-04-10 00:00:00
32012-04-11 00:00:002012-04-13 00:00:0012012-05-01 00:00:00
32012-05-02 00:00:002012-07-30 00:00:0002012-07-30 00:00:00
32012-07-31 00:00:002012-07-31 00:00:0012012-11-25 00:00:00
Any help will be really appreciated.
Thanks
June 12, 2013 at 2:14 pm
Looks like I made the some part of it work.
This SQL will drop the unwanted records and now I have to somehow add the new end dates.
delete A
from test_data A
join test_data b
on a.id = b.id
and a.type = b.type
and a.start_dt = b.end_dt + 1
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply