Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL question Expand / Collapse
Author
Message
Posted Wednesday, June 12, 2013 11:03 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, February 4, 2014 9:14 AM
Points: 44, Visits: 111
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
3 2012-04-03 00:00:00 2012-04-06 00:00:00 1
3 2012-04-07 00:00:00 2012-04-10 00:00:00 0
3 2012-04-11 00:00:00 2012-04-13 00:00:00 1
3 2012-04-14 00:00:00 2012-04-14 00:00:00 1 --these are not reqd
3 2012-04-15 00:00:00 2012-05-01 00:00:00 1 --these are not reqd
3 2012-05-02 00:00:00 2012-07-30 00:00:00 0
3 2012-07-31 00:00:00 2012-07-31 00:00:00 1
3 2012-08-01 00:00:00 2012-10-26 00:00:00 1 --these are not reqd
3 2012-10-27 00:00:00 2012-11-19 00:00:00 1 --these are not reqd
3 2012-11-20 00:00:00 2012-11-25 00:00:00 1 --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
3 2012-04-03 00:00:00 2012-04-06 00:00:00 1 2012-04-06 00:00:00
3 2012-04-07 00:00:00 2012-04-10 00:00:00 0 2012-04-10 00:00:00
3 2012-04-11 00:00:00 2012-04-13 00:00:00 1 2012-05-01 00:00:00
3 2012-05-02 00:00:00 2012-07-30 00:00:00 0 2012-07-30 00:00:00
3 2012-07-31 00:00:00 2012-07-31 00:00:00 1 2012-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
1 2012-01-01 00:00:00 2012-01-15 00:00:00 0 2012-01-20 00:00:00
1 2012-01-21 00:00:00 2012-01-25 00:00:00 1 2012-01-25 00:00:00
1 2012-01-26 00:00:00 2012-01-31 00:00:00 0 2012-01-31 00:00:00
1 2012-02-01 00:00:00 2012-02-15 00:00:00 1 2012-02-26 00:00:00
1 2012-02-27 00:00:00 2012-03-05 00:00:00 0 2012-03-17 00:00:00
1 2012-03-18 00:00:00 2012-03-18 00:00:00 1 2012-03-18 00:00:00

2 2012-01-01 00:00:00 2012-01-09 00:00:00 0 2012-01-11 00:00:00
2 2012-01-12 00:00:00 2012-01-17 00:00:00 1 2012-02-09 00:00:00
2 2012-02-10 00:00:00 2012-02-19 00:00:00 0 2012-02-19 00:00:00
2 2012-02-20 00:00:00 2012-02-21 00:00:00 1 2012-03-19 00:00:00

3 2012-04-03 00:00:00 2012-04-06 00:00:00 1 2012-04-06 00:00:00
3 2012-04-07 00:00:00 2012-04-10 00:00:00 0 2012-04-10 00:00:00
3 2012-04-11 00:00:00 2012-04-13 00:00:00 1 2012-05-01 00:00:00
3 2012-05-02 00:00:00 2012-07-30 00:00:00 0 2012-07-30 00:00:00
3 2012-07-31 00:00:00 2012-07-31 00:00:00 1 2012-11-25 00:00:00

Any help will be really appreciated.

Thanks
Post #1462741
Posted Wednesday, June 12, 2013 2:14 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, February 4, 2014 9:14 AM
Points: 44, Visits: 111
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

Post #1462816
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse