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)
 Time Interval

Author  Topic 

ranganath
Posting Yak Master

209 Posts
Posted - 2007-07-03 : 08:59:52
hi,

Declare @Temp Table (Id int Identity(1,1),PubId int, StartDateTime Datetime)

Insert into @Temp

Select 1046,'2007-07-03 02:40:07.530' union all
Select 1046, '2007-07-03 02:42:58.810'union all
Select 274 ,'2007-07-03 04:19:04.283'union all
Select 274, '2007-07-03 04:19:04.283'union all
Select 274, '2007-07-03 04:19:16.050'union all
Select 274 ,'2007-07-03 04:19:23.940'union all
Select 438 ,'2007-07-03 05:10:45.980'union all
Select 438 ,'2007-07-03 05:10:46.010'union all
Select 438, '2007-07-03 05:10:51.197'union all
Select 438, '2007-07-03 05:10:52.837'union all
Select 438, '2007-07-03 06:07:57.137'union all
Select 438, '2007-07-03 06:07:57.137'union all
Select 438, '2007-07-03 06:08:07.793'union all
Select 438, '2007-07-03 06:08:11.387'union all
Select 411, '2007-07-03 10:41:53.390'union all
Select 256, '2007-07-03 10:41:53.390'union all
Select 256, '2007-07-03 10:41:53.407'union all
Select 411, '2007-07-03 10:41:53.407'union all
Select 256, '2007-07-03 10:42:07.560'union all
Select 411, '2007-07-03 10:42:16.047'

output:
Select Pubid, count number of pubIds, timeinterval For Every one Minute

My desired output as fallows

pubId timeInterval count
411 2007-07-03 10:41:53.390 - 2007-07-03 10:42:16.047 6
256
256
411
256
411
438 2007-07-03 06:07:57.137 - 2007-07-03 06:08:07.793 1

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2007-07-03 : 09:42:56
You must provide more information about expected output.
What are your business rules?


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

ranganath
Posting Yak Master

209 Posts
Posted - 2007-07-03 : 10:35:31
Hi,


how many records (count of records) between '2007-07-03 10:41' and '2007-07-03 10:42'
what are the pubids between '2007-07-03 10:41' and '2007-07-03 10:42'
like that
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2007-07-03 : 11:31:13
You mean your expected output is not complete, kind of sort of?


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2007-07-03 : 11:37:47
Your sample data do not meet your expected output critera.
Try this
-- prepare sample data
declare @sample table (pubid int, startdatetime datetime)

insert	@sample
select	1046,'2007-07-03 02:40:07.530' union all 
select	1046, '2007-07-03 02:42:58.810' union all
select	274 ,'2007-07-03 04:19:04.283' union all
select	274, '2007-07-03 04:19:04.283' union all
select	274, '2007-07-03 04:19:16.050' union all
select	274 ,'2007-07-03 04:19:23.940' union all
select	438 ,'2007-07-03 05:10:45.980' union all
select	438 ,'2007-07-03 05:10:46.010' union all
select	438, '2007-07-03 05:10:51.197' union all
select	438, '2007-07-03 05:10:52.837' union all
select	438, '2007-07-03 06:07:57.137' union all
select	438, '2007-07-03 06:07:57.137' union all
select	438, '2007-07-03 06:08:07.793' union all
select	438, '2007-07-03 06:08:11.387' union all
select	411, '2007-07-03 10:41:53.390' union all
select	256, '2007-07-03 10:41:53.390' union all
select	256, '2007-07-03 10:41:53.407' union all
select	411, '2007-07-03 10:41:53.407' union all
select	256, '2007-07-03 10:42:07.560' union all
select	411, '2007-07-03 10:42:16.047'

-- show the expected output
select		d.pubid,
		convert(varchar, d.mindt, 120) + ' - ' + convert(varchar, d.maxdt, 120) as timeinterval,
		count(*) as [count]
from		(
			select		pubid,
					min(startdatetime) as mindt,
					max(startdatetime) as maxdt
			from		@sample
			group by	pubid
		) as d
inner join	@sample as s on s.startdatetime between d.mindt and d.maxdt
group by	d.pubid,
		d.mindt,
		d.maxdt
order by	d.pubid


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

ranganath
Posting Yak Master

209 Posts
Posted - 2007-07-04 : 02:52:54
hi

If timeInterval Gap is One Minute then show All Publicationid Between one minute
like 2007-07-03 10:41:53 - 2007-07-03 10:42:16
have two pubid's they are 256,411
and count as 6

If timeInterval Gap is more than one minute then leaveit,
EX :2007-07-03 05:10:45 - 2007-07-03 06:08:11 then u have to show the pubids in the timeinterval 2007-07-03 05:10:45 and their count also seperatly and 2007-07-03 06:08:11 seperate records
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2007-07-04 : 03:36:48
How do you calculate the "timeinterval gap"?
You haven't provided enough details for us to solve this. What to do with PubID 438?



Peter Larsson
Helsingborg, Sweden
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2007-07-04 : 03:43:22
[code]-- prepare sample data
declare @sample table (pubid int, startdatetime datetime)

insert @sample
select 1046,'2007-07-03 02:40:07.530' union all
select 1046, '2007-07-03 02:42:58.810' union all
select 274 ,'2007-07-03 04:19:04.283' union all
select 274, '2007-07-03 04:19:04.283' union all
select 274, '2007-07-03 04:19:16.050' union all
select 274 ,'2007-07-03 04:19:23.940' union all
select 438 ,'2007-07-03 05:10:45.980' union all
select 438 ,'2007-07-03 05:10:46.010' union all
select 438, '2007-07-03 05:10:51.197' union all
select 438, '2007-07-03 05:10:52.837' union all
select 438, '2007-07-03 06:07:57.137' union all
select 438, '2007-07-03 06:07:57.137' union all
select 438, '2007-07-03 06:08:07.793' union all
select 438, '2007-07-03 06:08:11.387' union all
select 411, '2007-07-03 10:41:53.390' union all
select 256, '2007-07-03 10:41:53.390' union all
select 256, '2007-07-03 10:41:53.407' union all
select 411, '2007-07-03 10:41:53.407' union all
select 256, '2007-07-03 10:42:07.560' union all
select 411, '2007-07-03 10:42:16.047'

-- show the expected output
select d.pubid,
CASE
WHEN DATEDIFF(ms, d.mindt, d.maxdt) >= 60000 THEN NULL
ELSE convert(varchar, d.mindt, 120) + ' - ' + convert(varchar, d.maxdt, 120)
END as timeinterval,
count(*) as [count]
from (
select pubid,
min(startdatetime) as mindt,
max(startdatetime) as maxdt
from @sample
group by pubid
) as d
inner join @sample as s on s.startdatetime between d.mindt and d.maxdt
group by d.pubid,
d.mindt,
d.maxdt
order by d.pubid[/code]

Peter Larsson
Helsingborg, Sweden
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site