December 4, 2002 at 12:05 pm
I have a table with a start time(datetime), end time(datetime), computer id (varchar), and duration (int) of an event. I need to have a count of computers running during each half hour. The only thing I have come up with so far does not include the computer in more than one half hour (example- start time is 8:15:00 AM, end time is 8:45:00 AM. The computer only shows up in the count for 8:00-8:30, and not in the 8:30 to 9:00 time slot. please help!
Kristen Aranda
Programmer
San Jacinto College
Kristen Aranda
Programmer
San Jacinto College
December 4, 2002 at 12:27 pm
We would need to see more of your table def and your query. If you somehow have each 1/2 hour in a table somewhere, you can see if your period includes either terminal point.
In time, if you have an event that start at 7:59 and ends at 8:31, it is in 3 1/2 hour periods.
You will need to write your query so that you check all overlapping conditions
1) event runs entirely in 1/2 hour
2) event starts within period and last longer than period
3) event ends with this period, but started earlier
4) event started before this period and ended after this period.
Submit your SQL and we'll tell you how to get what you want out of it.
December 4, 2002 at 12:54 pm
Here is the stored procedure I have so far with the data I need to manipulate. The table with the times is called TimeOfDay. Thank you very much for your help. I need to get a distinct count (each computer may appear more than once for a time slot) of the number of computers that are running during a half hour.
CREATE PROCEDURE [dbo].[TimeTempTable]
@StartDate smalldatetime,
@EndDate smalldatetime,
@LabNum varchar(5)
AS
CREATE table #tmp_table(UsageDay varchar(15), UsageHour int, StartWhen smalldatetime, UsageWhen smalldatetime, UsageComputer varchar(64) not null, UsageAction int, ProgramName varchar(64) not null, UsageTime int)
insert into #tmp_table (UsageDay, UsageHour,StartWhen, UsageWhen, UsageComputer, UsageAction, ProgramName, UsageTime)
select datename(dw,dateadd(ss,-usagetime, usagewhen))as UsageDay, datepart(hh,dateadd(ss,-usagetime, usagewhen)) as UsageHour, dateadd(ss,-usagetime, usagewhen) as StartWhen,UsageWhen, UsageComputer, UsageAction,programname, (UsageTime/60) as Minutes
from dbo.Usage1, dbo.Sys_Loc, keyadmin.Programs
where dbo.usage1.usagecomputer=sys_loc.sys_name
and dbo.usage1.usageprogram=keyadmin.programs.programID
and sys_loc.lab_id= @LabNum
and ( dbo.usage1.usageaction <>7 and
dbo.usage1.usageaction <>6 and
dbo.usage1.usageaction <>1 and
dbo.usage1.usageaction <>3 and
dbo.usage1.usageaction <>8 and
dbo.usage1.usageaction <>10 and
dbo.usage1.usageaction<>5)
and (keyadmin.programs.programname<>'keycheckout' and
keyadmin.programs.programname <>'keyaudit' and
keyadmin.programs.programname <>'keyverify' and
keyadmin.programs.programname<>'keysentry' and
keyadmin.programs.programname<>'kevterm')
and dateadd(ss,-usagetime, usagewhen) between @StartDate and @EndDate
group by datename(dw,dateadd(ss,-usagetime, usagewhen)), datepart(hh,dateadd(ss,-usagetime, usagewhen)), dateadd(ss,-usagetime, usagewhen), usagewhen, UsageComputer, UsageAction,programname, UsageTime/60
order by datename(dw,dateadd(ss,-usagetime, usagewhen)), datepart(hh,dateadd(ss,-usagetime, usagewhen)), dateadd(ss,-usagetime, usagewhen), usagewhen, UsageComputer, UsageAction,programname, UsageTime/60
--select * from #tmp_table
drop table #tmp_table
GO
Kristen Aranda
Programmer
San Jacinto College
Kristen Aranda
Programmer
San Jacinto College
December 4, 2002 at 2:24 pm
Had too long day to really dive into this. I suggest the following :
1. Build a (temp?) table with all the time intervals you want to have results for :
create table Interval
(StartTime datetime not null,
EndTime datetime not null)
You fill it up with :
StartTime EndTime
--------- --------
00:00:00 00:30:00
00:30:00 01:00:00
01:00:00 01:30:00
etc.
2. You join this Interval table to your Event table (containing the event data for the computers). For full results (i.e. any computer that was active during an interval), the join clause would look something like:
(--Checks if Event.StartTime in interval
Event.StartTime > Interval.StartTime
AND
Event.StartTime < Interval.EndTime
)
OR
(--Event.EndTime in interval
Event.EndTime < Interval.EndTime
AND
Event.EndTime > Interval.StartTime
)
OR
(--Starts before and ends after interval
Event.StartTime < Interval.EndTime
AND
Event.EndTime > Interval.EndTime
)
This might give you some doubles and you'll have to fiddle with the comparisons (inclusive or not), but it should get you started.
December 5, 2002 at 4:37 pm
Actually I do this with a schedual adhrence app that I created to verify a person is on the phone when they should be and not when they should not.
Since you are dealing with only two times you only need to do
JOIN ON
Convert(char,Comp.EndTime,108) > list.StartTime AND
Convert(char,Comp.StartTime,108) < list.EndTime
I do suggest using a permanent table with start and end times for your time frames.
Also, the reason on the join I do a convert is you have a list of times and you need to compare to the time portion of the data only. I would also suggest as this can take a bit to complete use a WHERE with a STartTime > and EndTime < in the query to narrow to a specific date and create a summary table if possible so you do not have to run again. Then you just run a procedure thru the day to update the summary table then no worryies and you can report on much faster.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply