September 6, 2006 at 8:43 am
I want to find out how much time was between the time given and the time between every half an hour time slots.
For example if i have a time of
11:30:04 this would fall into the time slot of 11:30 - 12:00 and the time used within that time slot was 4 seconds.
Another example would be a give time of 08:52:04 this would fall into the time slot of 08:30 - 09:00 and the time used within that time slot was 22 mins 4 seconds.
Now things get a bit tricky what if my 22 mins 4 seconds span over 2 time slots as couldd any time..
Example start time was 08:20 End time was 08:42.
So the first 10 mins was in the time slot of 08:00 to 08:30
and the next 12 mins was in the time slot of 08:30 to 09:00
I have a table that hold time slots they go up in 30 mins starting form 06:00 am to 18:00pm
Example
06:00
06:30
.
.
.
18:00
Has anyone any ideas now how best to aproach this..
September 6, 2006 at 9:19 am
It would help us if you could post the tables definition, sample data and required output from that data!!
September 6, 2006 at 9:57 am
Francis
You don't say what you want to do with the data when you get it, so I have assumed you want to add the number of seconds in each half-hour period into an existing table. You can add computed columns to break down the seconds into hours, minutes and seconds if you need to. This is a crude attempt and may need refining if you need to deal in fractions of a second.
--Create the table
CREATE TABLE #times (PeriodStarts datetime, Seconds INT)
--Populate the table with half-hour periods.
--Each time represents the time the period starts
INSERT INTO #times
SELECT DATEADD (mi, number * 30, '2006-09-06 06:00:00.000'), 0
FROM master.dbo.spt_values
WHERE name IS NULL
AND number < 25
--Verify that everything looks OK
SELECT * FROM #times
GO
--Create proc that will break down given time intervals
--into the seconds in each period
CREATE PROCEDURE AddSeconds
@StartTime datetime,
@EndTime datetime
AS
UPDATE #times
SET Seconds = Seconds +
CASE
WHEN @StartTime > PeriodStarts
THEN 1800 - DATEDIFF (s, PeriodStarts, @StartTime)
WHEN DATEDIFF (s, PeriodStarts, @EndTime) < 1800
THEN DATEDIFF (s, PeriodStarts, @EndTime)
ELSE 1800
END
WHERE PeriodStarts >= DATEADD(mi, -30, @StartTime)
AND PeriodStarts < @EndTime
--Test it out
EXEC AddSeconds '2006-09-06 09:10:03.000', '2006-09-06 11:13:21.000'
SELECT * FROM #times
John
September 6, 2006 at 10:20 am
Francis,
Here a query that might help you.
1.Create a Timeslot table(It Could be Temp Table as well)
Create table TimeSlot(
dt_TimeSlotBegin Datetime,
dt_TimeSlotEnd Datetime)
2.Load the Timeslot table. This table will be used to identify the slot.
Declare @dt_Time datetime
Set @dt_Time ='00:00:01'
while(DateDiff(d,@dt_Time,DateAdd(s,1799,@dt_Time))) < 1
Begin
Insert into TimeSlot(dt_TimeSlotBegin,dt_TimeSlotEnd)
select @dt_Time,DateAdd(s,1799,@dt_Time)
set @dt_Time = DateAdd(mi,30,@dt_Time)
End
Insert into TimeSlot(dt_TimeSlotBegin,dt_TimeSlotEnd)
select @dt_Time,DateAdd(s,1799,@dt_Time)
3.Query to get the Data:
Declare @dt_ProcessBeginTime datetime,@dt_ProcessEndTime datetime,
@dt_MinSlot Datetime,
@dt_MaxSlot Datetime
Set @dt_ProcessBeginTime ='1/1/2000 08:10:00'
Set @dt_ProcessEndTime ='1/1/2000 10:15:00'
SET ROWCOUNT 1
select @dt_MinSlot = dt_TimeSlotBegin from TimeSlot where dt_TimeSlotBegin <= Convert(Char(8),@dt_ProcessBeginTime,108) order by dt_TimeSlotBegin Desc
SET ROWCOUNT 1
select @dt_MaxSlot = dt_TimeSlotEnd from TimeSlot where dt_TimeSlotEnd >= Convert(Char(8),@dt_ProcessEndTime,108) order by dt_TimeSlotBegin asc
select @dt_MinSlot as MinSlot,@dt_MaxSlot as MaxSlot
SET ROWCOUNT 1000
select * ,@dt_ProcessBeginTime as 'ProcessBegin',@dt_ProcessEndTime as 'ProcessEnd',--DateDiff(mi,Cast(Convert(Char(8),@dt_ProcessBeginTime,108) as datetime),dt_TimeSlotEnd),
Case when DateDiff(mi,@dt_ProcessBeginTime,@dt_ProcessEndTime) < 30 Then DateDiff(mi,@dt_ProcessBeginTime,@dt_ProcessEndTime)
Else
Case when Cast(Convert(Char(8),@dt_ProcessBeginTime,108) as datetime) between dt_TimeSlotBegin and dt_TimeSlotEnd Then DateDiff(mi,Cast(Convert(Char(8),@dt_ProcessBeginTime,108) as datetime),Cast(Convert(Char(8),dt_TimeSlotEnd,108) as datetime))
when Cast(Convert(Char(8),@dt_ProcessEndTime,108) as datetime) between dt_TimeSlotBegin and dt_TimeSlotEnd Then DateDiff(mi,Cast(Convert(Char(8),dt_TimeSlotBegin,108) as datetime),Cast(Convert(Char(8),@dt_ProcessEndTime,108) as datetime))
Else 30 End
End as Mins
from TimeSlot where dt_TimeSlotBegin >= @dt_MinSlot and dt_TimeSlotEnd <= @dt_MaxSlot
Hope this helps.
Thanks
Sreejith
September 6, 2006 at 10:26 pm
try this query, it will give desired output for all possible inputs
here I used Sreejith's table def
Declare @dt_ProcessBeginTime datetime,@dt_ProcessEndTime datetime,
@dt_MinSlot Datetime,
@dt_MaxSlot Datetime
Set @dt_ProcessBeginTime ='1/1/2000 09:10:00'
Set @dt_ProcessEndTime ='1/1/2000 09:35:00'
select * ,@dt_ProcessBeginTime as 'ProcessBegin',@dt_ProcessEndTime as 'ProcessEnd',--DateDiff(mi,Cast(Convert(Char(8),@dt_ProcessBeginTime,108) as datetime),dt_TimeSlotEnd),
Case when DateDiff(mi,@dt_ProcessBeginTime,@dt_ProcessEndTime) = @dt_MinSlot and dt_TimeSlotEnd <= @dt_MaxSlot
September 7, 2006 at 12:37 am
Probably this may help:
IF EXISTS (SELECT * FROM sysobjects WHERE name = N'TimeSlotStart')
DROP FUNCTION TimeSlotStart
GO
CREATE FUNCTION TimeSlotStart
(@CurrentTime datetime, @SlotDuration datetime)
RETURNS datetime
AS
BEGIN
DECLARE @HH float
select @HH = cast(@SlotDuration as float)
RETURN CAST(FLOOR(cast(@CurrentTime as float)/@HH)*@HH as smalldatetime)
END
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = N'TimeSlotEnd')
DROP FUNCTION TimeSlotEnd
GO
CREATE FUNCTION TimeSlotEnd
(@CurrentTime datetime, @SlotDuration datetime)
RETURNS datetime
AS
BEGIN
DECLARE @HH float
select @HH = cast(@SlotDuration as float)
RETURN CAST(CEILING(cast(@CurrentTime as float)/@HH)*@HH as smalldatetime)
END
GO
SELECT dbo.TimeSlotSTART ('11:40', '0:30'), dbo.TimeSlotEnd ('11:40', '0:30')
GO
_____________
Code for TallyGenerator
September 7, 2006 at 5:59 am
Hi guys.
Thanks very much for your help with this, i would have found it very dificult to figure out on my own.
I've one last question, my table detials are as follows
These are the values i will be pluging into the PROCEDURE AddSeconds @StartTime datetime,
@EndTime datetime
So what i did was update my table and create 2 new columns of datatype datetime and that mean i have now the format in datetime,
But when i plug my 2 columns into the procedure i get a error "Error converting data type nvarchar to datetime."
[idle_duration] [decimal](18, 6) NULL ,
[idle_time] [decimal](24, 6) NULL ,
Here is the code.
drop table #times
drop table #temp_Metrics_asp
drop PROCEDURE AddSeconds
-- temp table
create table #temp_Metrics_asp
(
Start_Time datetime,
End_Time datetime
)
insert into #temp_Metrics_asp( Start_Time,End_Time)
select mt.New_idle_Time, -- datatype is decimal
mt.New_idle_duration -- datatype is decimal
from tbl_metrics_asp_test mt
--Create the table
CREATE TABLE #times (PeriodStarts datetime, Seconds INT)
--Populate the table with half-hour periods.
--Each time represents the time the period starts
INSERT INTO #times
SELECT DATEADD (mi, number * 30, '2006-09-06 06:00:00.000'), 0
FROM master.dbo.spt_values
WHERE name IS NULL
AND number < 25
--Verify that everything looks OK
SELECT * FROM #times
GO
--Create proc that will break down given time intervals
--into the seconds in each period
CREATE PROCEDURE AddSeconds
@StartTime datetime,
@EndTime datetime
AS
UPDATE #times
SET Seconds = Seconds +
CASE
WHEN @StartTime > PeriodStarts
THEN 1800 - DATEDIFF (s, PeriodStarts, @StartTime)
WHEN DATEDIFF (s, PeriodStarts, @EndTime) < 1800
THEN DATEDIFF (s, PeriodStarts, @EndTime)
ELSE 1800
END
WHERE PeriodStarts >= DATEADD(mi, -30, @StartTime)
AND PeriodStarts < @EndTime
--Test it out
--EXEC AddSeconds '2006-09-06 09:10:03.000', '2006-09-06 11:13:21.000'
--SELECT * FROM #times
--Test it out
EXEC AddSeconds Start_Time,End_Time -- plug values of table into procedure
SELECT * FROM #times
September 7, 2006 at 7:16 am
What sort of information is in the idle_time and idle_duration columns? Is it a number of seconds, or is it a decimal number that represents the number of days... or is it something else?
John
September 7, 2006 at 7:27 am
Hi John,
Here is a sample data whats in each column
idle_duration .000763
idle_time .479976
When i create a new column based on this data it formats into a normal datetime data type. As that what i've set the new columns to be
I would say its the decimal number that represents the number of mintues seconds etc... becuase the date part is set to "1900-01-01"
Here is a sample after converting to datetime
"1900-01-01 11:31:09.923"
September 7, 2006 at 7:36 am
Would this work, then?
INSERT INTO #temp_Metrics_asp( Start_Time,End_Time)
SELECT CAST (mt.New_idle_Time AS datetime), -- datatype is decimal
CAST (mt.New_idle_duration AS datetime) -- datatype is decimal
FROM tbl_metrics_asp_test mt
John
September 7, 2006 at 8:03 am
I tried that, and it does not work, your call to the procedure takes in date like a string,
EXEC AddSeconds '2006-09-06 09:10:03.000', '2006-09-06 11:13:21.000'
But you've defined these as datetime
CREATE PROCEDURE AddSeconds @StartTime datetime, @EndTime datetime
Its strange when i plug the values from the table which are datetime also, they don't work.
September 7, 2006 at 8:47 am
SQL Server implicitly converts the string into a datetime value.
If you are trying to plug in the values in the way you show in your "Test it out" section, then it will fail. You either need to use dynamic SQL and some kind of loop or, better still, change the stored procedure so that it does it all for you in a set-based operation. Something like this (not tested):
CREATE PROCEDURE AddSeconds
AS
UPDATE #times t
SET t.Seconds = t.Seconds +
CASE
WHEN n.New_idle_Time > t.PeriodStarts
THEN 1800 - DATEDIFF (s, t.PeriodStarts, n.New_idle_Time)
WHEN DATEDIFF (s, t.PeriodStarts, n.New_idle_duration ) < 1800
THEN DATEDIFF (s, t.PeriodStarts, n.New_idle_duration )
ELSE 1800
END
FROM tbl_metrics_asp_test n
WHERE t.PeriodStarts >= DATEADD(mi, -30, n.New_idle_Time)
AND t.PeriodStarts < n.New_idle_duration
GO
Note that the procedure no longer takes any parameters since it is updating the #times table with values from tbl_metrics_asp_test. You do not need to create the #temp_Metrics_asp table either if you do it this way. If SQL Server sill has difficulty with implicit conversions then you will need to wrap every New_idle_Time/duration in a CAST statement.
By the way, if your columns are meaningfully named, then they won't map directly to a start time and an end time. To get the end time, you will need something like DATEADD(DAY, New_idle_duration, New_idle_time)
Hope that helps (and works!)
John
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply