Fill in the gaps - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Fill in the gaps

Author

Topic

sona
Yak Posting Veteran

68 Posts

Posted - 2002-01-17 : 01:56:21

i have a table like this

Name StartDate EndDate

A 1/14/02 9:00:00 AM 1/14/02 12:00:00 PM
B 1/14/02 1:00:00 PM 1/14/02 6:00:00 PM
A 1/15/02 9:00:00 AM 1/15/02 12:00:00 PM
B 1/15/02 1:00:00 PM 1/15/02 6:00:00 PM

No i want to fill the timegaps of each day with another one like this

Name StartDate EndDate

C 1/14/02 00:00:00 AM 1/14/02 9:00:00 AM
A 1/14/02 9:00:00 AM 1/14/02 12:00:00 PM
B 1/14/02 12:00:00 PM 1/14/02 6:00:00 PM
C 1/14/02 6:00:00 PM 1/14/02 00:00:00 AM
C 1/15/02 00:00:00 AM 1/15/02 9:00:00 AM
A 1/15/02 9:00:00 AM 1/15/02 12:00:00 PM
B 1/15/02 12:00:00 PM 1/15/02 6:00:00 PM
C 1/15/02 6:00:00 PM 1/15/02 00:00:00 AM

Some timegaps has to be done inbetween also
help me out please

Edited by - sona on 01/17/2002 01:58:01

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2002-01-17 : 02:42:37

Sona,

You have conflicting data in you example

eg
1PM - 6PM in the first example and 12PM - 6PM in the second..

I will assume that the second example is correct...

Anyway.....

First create a base table with the time range...


create table TimeSeries(StartDate datetime not null, EndDate Datetime not null constraint PK_TimeSeries Primary Key (StartDate, EndDate))
go
insert TimeSeries (StartDate, EndDate)
	values ('00:00:00','09:00:00')
insert TimeSeries (StartDate, EndDate)
	values ('09:00:00','12:00:00')
insert TimeSeries (StartDate, EndDate)
	values ('12:00:00','18:00:00')
insert TimeSeries (StartDate, EndDate)
	values ('18:00:00','00:00:00')
go

Now that we have our "sequence" table....


Insert into Data (Name, StartDate, EndDate)
Select  distinct 'C' as name, Dateadd(d,Datediff(d,TS.StartDate,D.StartDate),TS.StartDate) as StartDate, Dateadd(d,Datediff(d,TS.EndDate,D.EndDate),TS.EndDate) as EndDate from TimeSeries TS CROSS JOIN Data D
where not exists (Select 1 from Data D where D.StartDate = Dateadd(d,Datediff(d,TS.StartDate,D.StartDate),TS.StartDate))

HTH

DavidM

Tomorrow is the same day as Today was the day before.

deepa
Starting Member

17 Posts

Posted - 2002-01-17 : 04:24:46

no david,
i want the missed time gap as

c 1/14/02 12:00:00 PM 1/14/02 1:00:00 PM

Deepa

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2002-01-17 : 16:55:27

Add any new time gap to the "sequence" table...

DavidM

Tomorrow is the same day as Today was the day before.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources