how can i find the intervel if given two dates

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)

how can i find the intervel if given two dates

Author

Topic

Sarakumar
Posting Yak Master

108 Posts

Posted - 2006-04-20 : 01:38:39

Hai,
i need to generate a report based on 15 mins intervel..
but i will be given from and to date as input...
i need to split into 15 mins intervel...how can i do that..i need to split it in the following way
Interval Ending At
01/3/2006 12:15 AM
01/3/2006 12:30 AM
01/3/2006 12:45 AM
01/3/2006 01:00 AM
01/3/2006 01:15 AM
01/3/2006 01:30 AM
01/3/2006 01:45 AM

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-20 : 01:47:34

Something like

select Dateadd(minute,datediff(minute,0,datecol)/15*15,0)
from yourTable group by Dateadd(minute,datediff(minute,0,datecol)/15*15,0)

Madhivanan

Failing to plan is Planning to fail

Sarakumar
Posting Yak Master

108 Posts

Posted - 2006-04-20 : 02:14:19

quote:
Originally posted by madhivanan

Something like

select Dateadd(minute,datediff(minute,0,datecol)/15*15,0)
from yourTable group by Dateadd(minute,datediff(minute,0,datecol)/15*15,0)

Madhivanan

Failing to plan is Planning to fail

sorry i dont understand this...can pls explain

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-20 : 04:06:20

Did you run that query on your table?

Madhivanan

Failing to plan is Planning to fail

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-04-20 : 06:05:55

It sounds to me like you need to use some kind of numbers table...

--input
declare @from datetime
declare @to datetime
set @from = '20060301 00:15'
set @to = '20060301 01:45'

--numbers table
declare @numbers table (i int identity(0, 1), j bit)
insert @numbers select top 1000 null from master.dbo.syscolumns a, master.dbo.syscolumns b

--calculation
select dateadd(minute, 15*i, @from) as Date from @numbers where dateadd(minute, 15*i, @from) <= @to

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Sarakumar
Posting Yak Master

108 Posts

Posted - 2006-04-21 : 04:30:27

Hai, i need to write my query like this,
Select updatedate from mytable
where updatedate between startdate ,startdate+15 sec(till i reach my End date)
how can i do this

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-21 : 04:39:29

Did you try Madhivanan's code ?


select dateadd(minute, datediff(minute, 0, updatedate ) / 15 * 15, 0)
from   mytable
where  updatedate between @startdate and @enddate
group by dateadd(minute, datediff(minute, 0, updatedate ) / 15 * 15, 0)

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-04-21 : 04:54:40

Hi Sarakumar,

If you haven't yet got an answer to what you want, please refer to the suggestions and explain why they don't work for you.

Want the right answer first time? Then post the DDL of the table you're starting with, some example data, and the result you would want for that sample data.

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Sarakumar
Posting Yak Master

108 Posts

Posted - 2006-04-21 : 05:23:50

yes i tried that...in that what im getting if i dont have any action for one hour, that interval it is not calucating..only it is considering the date in the table..
but i want all the 15 mins interval..i dodnt have any activity during that interval i need to display that as 0 ...
the result i got when i execute madhivanan's query

updatedDate
2006-03-24 00:00:00.000
2006-03-24 11:45:00.000
2006-03-28 13:30:00.000
2006-03-29 15:00:00.000
2006-03-30 14:30:00.000
2006-03-31 09:15:00.000
2006-04-03 17:45:00.000
2006-04-04 11:00:00.000
2006-04-05 09:30:00.000
2006-04-06 16:30:00.000
2006-04-07 15:30:00.000
2006-04-08 10:15:00.000
2006-04-10 11:30:00.000
2006-04-10 11:45:00.000
2006-04-10 17:00:00.000
2006-04-13 16:45:00.000
2006-04-19 15:45:00.000
2006-04-19 17:45:00.000

but i need like this
01/3/2006 12:15 AM
01/3/2006 12:30 AM
01/3/2006 12:45 AM
01/3/2006 01:00 AM
01/3/2006 01:15 AM
01/3/2006 01:30 AM
01/3/2006 01:45 AM

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-21 : 06:00:14

try out the following for illustration

create table mytable
(
	updatedate	datetime,
	updateval	int
)

insert into mytable
select	'2006-03-01 01:03', 10	union all
select	'2006-03-01 01:05', 11	union all
select	'2006-03-01 01:07', 12	union all
select	'2006-03-01 01:12', 13	union all
select	'2006-03-01 01:28', 14	union all
select	'2006-03-01 01:46', 15	union all
select	'2006-03-01 02:01', 16	union all
select	'2006-03-01 02:08', 17	union all
select	'2006-03-01 02:16', 18	union all
select	'2006-03-01 02:27', 19

select	d.upd_date, sum(isnull(updateval, 0))
from
(
	select	dateadd(minute, NUMBER * 15, '2006-03-01') as upd_date
	from	dbo.F_TABLE_NUMBER_RANGE(0, 20) 
) d
left join mytable t
on	t.updatedate	>= d.upd_date
and	t.updatedate	<  dateadd(minute, 15, d.upd_date)
group by d.upd_date
order by d.upd_date

get the F_TABLE_NUMBER_RANGE from Script Library forum

Sarakumar
Posting Yak Master

108 Posts

Posted - 2006-04-22 : 10:50:28

Hai, i have only access to select the data from the table..i dont even have access to write user defined functions..
Any other option to achieve this???

quote:

Originally posted by khtan

try out the following for illustration

create table mytable
(
	updatedate	datetime,
	updateval	int
)

insert into mytable
select	'2006-03-01 01:03', 10	union all
select	'2006-03-01 01:05', 11	union all
select	'2006-03-01 01:07', 12	union all
select	'2006-03-01 01:12', 13	union all
select	'2006-03-01 01:28', 14	union all
select	'2006-03-01 01:46', 15	union all
select	'2006-03-01 02:01', 16	union all
select	'2006-03-01 02:08', 17	union all
select	'2006-03-01 02:16', 18	union all
select	'2006-03-01 02:27', 19

select	d.upd_date, sum(isnull(updateval, 0))
from
(
	select	dateadd(minute, NUMBER * 15, '2006-03-01') as upd_date
	from	dbo.F_TABLE_NUMBER_RANGE(0, 20) 
) d
left join mytable t
on	t.updatedate	>= d.upd_date
and	t.updatedate	<  dateadd(minute, 15, d.upd_date)
group by d.upd_date
order by d.upd_date

get the F_TABLE_NUMBER_RANGE from Script Library forum

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-22 : 11:33:47

do you own TABLE_NUMBER_RANGE in query


	select	(m * 10) + n, dateadd(minute, ((m * 10) + n) * 15, '2006-03-01') as upd_date
	from	(
			select	0 as n union all 
			select  1 union all select  2 union all select  3 union all select  4 union all select 5 union all
			select  6 union all select  7 union all select  8 union all select  9 union all select 10
		) n
		cross join
		(
			select	0 as m union all
			select	1 union all select 2
		) m

Sarakumar
Posting Yak Master

108 Posts

Posted - 2006-04-22 : 12:33:02

quote:

Originally posted by khtan

do you own TABLE_NUMBER_RANGE in query


	select	(m * 10) + n, dateadd(minute, ((m * 10) + n) * 15, '2006-03-01') as upd_date
	from	(
			select	0 as n union all 
			select  1 union all select  2 union all select  3 union all select  4 union all select 5 union all
			select  6 union all select  7 union all select  8 union all select  9 union all select 10
		) n
		cross join
		(
			select	0 as m union all
			select	1 union all select 2
		) m

sorry, really i dont understand how can i use this with my logic..
select upd_date ((starttime +15) mins )
from the table group by (starttime +15) mins ) till i reach my end time

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources