DateTime Interval - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

DateTime Interval

Author

Topic

Clas
Starting Member

33 Posts

Posted - 2008-10-12 : 15:54:21

I have 2 question about datetime-interval

Create table #source
(
nr int,
IndividualNo int,
MeasuringValue_A int,
EventDate date
)

Create table #result
(
nr int,
IndividualNo int,
MeasuringValue_A int,
EventDate date,
StopDate date
)

insert into #source
values
(5,100000,4,'1921-01-05'),
(25,100000,4,'1922-05-05'),
(4,100000,4,'1923-10-10'),
(9,100000,4,'1929-11-04'),
(32,100000,5,'1932-05-04'),
(54,100000,5,'1937-08-04'),
(2,100000,6,'1945-05-05'),
(3,100000,9,'1950-07-08'),
(8,100001,7,'1925-06-07'),
(22,100001,5,'1928-08-07')

select * from #source
order by IndividualNo,EventDate
----------------------------------------

First Step:
Insert row in #result when change in MeasuringValue occurs,

Order By Date / Loop/ Case / ????
sql: ????????????????

RESULT:
5 -- 100000 -- 4 -- 1921-01-05
32 -- 100000 -- 5 -- 1932-05-04
2 -- 100000 -- 6 -- 1945-05-05
3 -- 100000 -- 9 -- 1950-07-08
8 -- 100001 -- 7 -- 1925-06-07
22 -- 100001 -- 5 -- 1928-08-07

In first step sql can run more then one times. User can choose more then one type of MeasuringValue.

Second Step: Create Spell/ Update #result

sql ???????????

Result:

IndividualNo, MeasuringValue, EventDate - StopDate (Next Eventdate-1 for each IndividualNo)
100000, 4 1921-01-05 - 1932-05-03
100000, 5 1932-05-04 - 1945-05-04
100000, 6 1945-05-05 - 1950-07-07
100000, 9 1950-07-08 - null

100001, 7, 1925-06-07 - 1928-08-06
100001, 5, 1928-08-07 - null

( null-value is updated in step 3)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-13 : 00:26:04

[code]Create table #source
(
nr int,
IndividualNo int,
MeasuringValue_A int,
EventDate datetime
)

Create table #result
(
nr int,
IndividualNo int,
MeasuringValue_A int,
EventDate datetime,
StopDate datetime
)

insert into #source
values
(5,100000,4,'1921-01-05')
insert into #source
values (25,100000,4,'1922-05-05')
insert into #source
values (4,100000,4,'1923-10-10')
insert into #source
values (9,100000,4,'1929-11-04')
insert into #source
values (32,100000,5,'1932-05-04')
insert into #source
values (54,100000,5,'1937-08-04')
insert into #source
values (2,100000,6,'1945-05-05')
insert into #source
values (3,100000,9,'1950-07-08')
insert into #source
values (8,100001,7,'1925-06-07')
insert into #source
values (22,100001,5,'1928-08-07')

;WIth Your_CTE (Seq,nr,IndividualNo,MeasuringValue_A,EventDate)
AS
(
SELECT * FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY IndividualNo,MeasuringValue_A ORDER BY EventDate) AS Seq,
nr,IndividualNo,MeasuringValue_A,EventDate
FROM #Source
)t
WHERE Seq=1
)
INSERT INTO #result
SELECT c1.nr,c1.IndividualNo,c1.MeasuringValue_A,c1.EventDate,c2.EventDate
FROM Your_CTE c1
OUTER APPLY (SELECT Top 1 EventDate
FROM Your_CTE
WHERE IndividualNo=c1.IndividualNo
AND EventDate >c1.EventDate
ORDER BY EventDate)c2
ORDER BY c1.EventDate

select IndividualNo, MeasuringValue_A, EventDate, StopDate from #result order by IndividualNo,EventDate

drop table #source
drop table #result

output
--------------------------------------------------------
IndividualNo MeasuringValue_A EventDate StopDate
---------------------------------------------------------------------
100000 4 1921-01-05 00:00:00.000 1932-05-04 00:00:00.000
100000 5 1932-05-04 00:00:00.000 1945-05-05 00:00:00.000
100000 6 1945-05-05 00:00:00.000 1950-07-08 00:00:00.000
100000 9 1950-07-08 00:00:00.000 NULL
100001 7 1925-06-07 00:00:00.000 1928-08-07 00:00:00.000
100001 5 1928-08-07 00:00:00.000 NULL[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources