Select from a date range

[Sunny K]

Hi guys,

whilst working on a project which I thought was nearly complete I have
come across a problem which was some how over seen, which I am hoping
one of you guys know how to resovle.

Firstly here is the script to create the table and insert some sample
data:
CREATE TABLE [dbo].[tbltemp999] (
[Machine_Name] [char] (17) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NOT
NULL ,
[EventDate] [datetime] NOT NULL ,
[EventID] [int] NOT NULL
) ON [PRIMARY]
GO

INSERT INTO tbltemp999 VALUES ('MOBL','2004-08-16 12:05:14.000',6 006)
INSERT INTO tbltemp999 VALUES ('MOBL','2004-08-16 12:08:32.000',6 005)
INSERT INTO tbltemp999 VALUES ('MOBL','2004-09-22 12:24:45.000',6 006)
INSERT INTO tbltemp999 VALUES ('MOBL','2004-09-22 12:28:25.000',6 005)
INSERT INTO tbltemp999 VALUES ('MOBL','2004-11-03 17:53:38.000',6 006)
INSERT INTO tbltemp999 VALUES ('MOBL','2004-11-03 17:57:02.000',6 005)
INSERT INTO tbltemp999 VALUES ('MOBL','2004-11-04 12:09:10.000',6 006)
INSERT INTO tbltemp999 VALUES ('MOBL','2004-11-04 12:12:48.000',6 005)
The EventID 6006 represents a system shut down, and the EventID 6005
represents a sytem start. Therefore from the above data you cans see
that the machine is not rebooted very often.

My problem occurs when you select a date BETWEEN something where there
is no data. ie BETWEEN 2004-10-01 AND 2004-10-15 my desired result
from this should return 15 days of uptime but as there is no data it
is returning nothing:(

Can anyone work this one out?

Thanks
Sunny

[nib]

Sunny K wrote:

Hi guys,

whilst working on a project which I thought was nearly complete I have
come across a problem which was some how over seen, which I am hoping
one of you guys know how to resovle.

Firstly here is the script to create the table and insert some sample
data:
CREATE TABLE [dbo].[tbltemp999] (
[Machine_Name] [char] (17) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NOT
NULL ,
[EventDate] [datetime] NOT NULL ,
[EventID] [int] NOT NULL
) ON [PRIMARY]
GO

INSERT INTO tbltemp999 VALUES ('MOBL','2004-08-16 12:05:14.000',6 006)
INSERT INTO tbltemp999 VALUES ('MOBL','2004-08-16 12:08:32.000',6 005)
INSERT INTO tbltemp999 VALUES ('MOBL','2004-09-22 12:24:45.000',6 006)
INSERT INTO tbltemp999 VALUES ('MOBL','2004-09-22 12:28:25.000',6 005)
INSERT INTO tbltemp999 VALUES ('MOBL','2004-11-03 17:53:38.000',6 006)
INSERT INTO tbltemp999 VALUES ('MOBL','2004-11-03 17:57:02.000',6 005)
INSERT INTO tbltemp999 VALUES ('MOBL','2004-11-04 12:09:10.000',6 006)
INSERT INTO tbltemp999 VALUES ('MOBL','2004-11-04 12:12:48.000',6 005)
The EventID 6006 represents a system shut down, and the EventID 6005
represents a sytem start. Therefore from the above data you cans see
that the machine is not rebooted very often.

My problem occurs when you select a date BETWEEN something where there
is no data. ie BETWEEN 2004-10-01 AND 2004-10-15 my desired result
from this should return 15 days of uptime but as there is no data it
is returning nothing:(

Can anyone work this one out?

Thanks
Sunny

Can you outline the results you want a little better? Do you want the
count of days up and down between any given date range? Just up days?
Hours? Down days? Etc.

Zach

[Sunny K]

> INSERT INTO tbltemp999 VALUES ('MOBL','2004-08-16 12:05:14.000',6 006)

INSERT INTO tbltemp999 VALUES ('MOBL','2004-08-16 12:08:32.000',6 005)
INSERT INTO tbltemp999 VALUES ('MOBL','2004-09-22 12:24:45.000',6 006)
INSERT INTO tbltemp999 VALUES ('MOBL','2004-09-22 12:28:25.000',6 005)
INSERT INTO tbltemp999 VALUES ('MOBL','2004-11-03 17:53:38.000',6 006)
INSERT INTO tbltemp999 VALUES ('MOBL','2004-11-03 17:57:02.000',6 005)
INSERT INTO tbltemp999 VALUES ('MOBL','2004-11-04 12:09:10.000',6 006)
INSERT INTO tbltemp999 VALUES ('MOBL','2004-11-04 12:12:48.000',6 005)

Hi Zach,

From the above data you can see that the Server was booted (EventID
6005) on the 2004-09-22 (UK Date: 22/09/04) and was running until the
2004-11-03 (03/11/04) when it was rebooted (EventID 6006). So it was up
for nearly 42 days.

If I select to dates between this range the Server would have been up
100% of the time, but nothing gets returned as there is no entries in
the EventDate for the date range.

If I selected BETWEEN 15/09/04 AND 25/09/04, I want to recieve the
result in the following format: x Days, x Hours, x Minutes x Seconds (eg
10 Days, 0 Hours, 0 Minutes, 0 Seconds)

I hope this helps explain it a little better.

Thanks
Sunny

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[nib]

Sunny K wrote:

INSERT INTO tbltemp999 VALUES ('MOBL','2004-08-16 12:05:14.000',6 006)
INSERT INTO tbltemp999 VALUES ('MOBL','2004-08-16 12:08:32.000',6 005)
INSERT INTO tbltemp999 VALUES ('MOBL','2004-09-22 12:24:45.000',6 006)
INSERT INTO tbltemp999 VALUES ('MOBL','2004-09-22 12:28:25.000',6 005)
INSERT INTO tbltemp999 VALUES ('MOBL','2004-11-03 17:53:38.000',6 006)
INSERT INTO tbltemp999 VALUES ('MOBL','2004-11-03 17:57:02.000',6 005)
INSERT INTO tbltemp999 VALUES ('MOBL','2004-11-04 12:09:10.000',6 006)
INSERT INTO tbltemp999 VALUES ('MOBL','2004-11-04 12:12:48.000',6 005)

Hi Zach,

From the above data you can see that the Server was booted (EventID
6005) on the 2004-09-22 (UK Date: 22/09/04) and was running until the
2004-11-03 (03/11/04) when it was rebooted (EventID 6006). So it was up
for nearly 42 days.

If I select to dates between this range the Server would have been up
100% of the time, but nothing gets returned as there is no entries in
the EventDate for the date range.

If I selected BETWEEN 15/09/04 AND 25/09/04, I want to recieve the
result in the following format: x Days, x Hours, x Minutes x Seconds (eg
10 Days, 0 Hours, 0 Minutes, 0 Seconds)

I hope this helps explain it a little better.

Thanks
Sunny

Sorta. What I'm trying to get you to define is what you want returned in
all the various possible combinations. For example, you're concerned
about one particular case, when it has been up during the entire date
range (i.e. no rows returned). However, it is much, much more complex.
Just because no rows are returned doesn't mean that the server has been
up all during that time, it could have been DOWN during that entire time
instead.

But, you also need to define what you want to happen in all the other
cases, for example:

Was up during the start date, then went down, then came back up before
the end date. What do yo want returned? Total up time? The fact that it
went down once?

What about cases where it went up and down several times during the
given date range? Again, do you want total up time/down time?

etc.

Honestly, I don't know how I could create much of any useful results
from this kind of data given what I think you want. There are just too
many issues to deal with (i.e. what if the server was down during the
start date? you need to go back to the last entry and read the code,
then handle it accordingly).

Hopefully someone can offer up more than I can.

Zach

[Vincent Lascaux]

"Sunny K" <su*******@yaho o.com> a écrit dans le message de news:
41************* *********@news. newsgroups.ws.. .

INSERT INTO tbltemp999 VALUES ('MOBL','2004-08-16 12:05:14.000',6 006)
INSERT INTO tbltemp999 VALUES ('MOBL','2004-08-16 12:08:32.000',6 005)
INSERT INTO tbltemp999 VALUES ('MOBL','2004-09-22 12:24:45.000',6 006)
INSERT INTO tbltemp999 VALUES ('MOBL','2004-09-22 12:28:25.000',6 005)
INSERT INTO tbltemp999 VALUES ('MOBL','2004-11-03 17:53:38.000',6 006)
INSERT INTO tbltemp999 VALUES ('MOBL','2004-11-03 17:57:02.000',6 005)
INSERT INTO tbltemp999 VALUES ('MOBL','2004-11-04 12:09:10.000',6 006)
INSERT INTO tbltemp999 VALUES ('MOBL','2004-11-04 12:12:48.000',6 005)

First convert the table to a more useable thing :
SELECT StartTable.Date , DateDiff('s', StartTable.Date , EndTable.Date) AS
Duration
FROM
tbltmp999 AS StartTable,
(SELECT Date
FROM tbltmp999
WHERE Date = (SELECT Min(Date) FROM tbltmp999 WHERE Date >=
StartTable.Date AND Status=6006)) AS EndTable
WHERE StartTable.Stat us = 6005 AND EndTable.Status =6006;

This request should give you a table with date and uptime. Let's call it
UpTimes.

SELECT StartTime + InnerTime AS UpTime FROM
(SELECT Max(0, Duration - DateDiff('s', Date, StartDate)) AS StartTime
FROM UpTimes
WHERE Date = (SELECT Max(Date) FROM UpTimes WHERE Date <= StartDate)),
(SELECT Sum(Min(DateDif f('s', Date, EndDate), Duration)) AS InnerTime
FROM UpTimes WHERE Date >= StartTime);

And Voilà...

--
Vincent

PS: this is fully NOT tested :) and I'm not sure about the order of the
parameters of DateDiff. I'm assuming that DateDiff('s', before, after) is
positive if before<after.

[--CELKO--]

Your real problem is an incorrect model of time. Time exists in
durations, not as instantaneous points; every read Zeno's paradoxes or
Einstein's physics? You are mimicking a clipboard with a paper form
onthe side of the machine, not a proper data model. Try something
like this:

CREATE TABLE MachineHistory -- meaningful name of table?
(machine_name CHAR(17) NOT NULL
REFERENCES Inventory(machi ne_name),
event_type INTEGER NOT NULL
REFERENCES EventCodes(even t_type), -- if the list short use
check()
event_start_tim e DATETIME DEFAULT CURRRENT_TIMEST AMP NOT NULL,
event_end_time DATETIME, -- null means current
CHECK(event_sta rt_time < event_end_time) ,
PRIMARY KEY (machine_name, event_start_tim e),
..);

The EventID 6006 represents a system shut down, and the EventID 6005
represents a system start. <<

You might also read a book on data modeling or ISO-11179. That is not
an identifier; it is a code, type, status or whatever of an event.
Think about it; is each event_id a referecne to an enetity or is it a
value that occur over and over?
My problem occurs when you select a date BETWEEN something where

there is no data. <<

Actually, there is data, but your improper data model lost it! You
might also want to add a Calendar table to the schema; it will make
temporal queries an order of magnitude faster and easier to write.
Most of the queries will take this general shape.

SELECT C1.cal_date, ..
FROM MachineHistory AS H1, Calendar AS C1
WHERE C1.cal_date BETWEEN H1.event_start_ time
AND COALESCE(H1.eve nt_end_time, CURRRENT_TIMEST AMP)
AND .. ;

Obviously, you need to make up business rules about temporal math.

The other approach is a self-join kludge I used in a book ten years
ago. The performance decays rapidly as the table size increases.

[John Gilson]

"Sunny K" <su*******@yaho o.com> wrote in message
news:1e******** *************** ***@posting.goo gle.com...

Hi guys,

whilst working on a project which I thought was nearly complete I have
come across a problem which was some how over seen, which I am hoping
one of you guys know how to resovle.

Firstly here is the script to create the table and insert some sample
data:
CREATE TABLE [dbo].[tbltemp999] (
[Machine_Name] [char] (17) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NOT
NULL ,
[EventDate] [datetime] NOT NULL ,
[EventID] [int] NOT NULL
) ON [PRIMARY]
GO

INSERT INTO tbltemp999 VALUES ('MOBL','2004-08-16 12:05:14.000',6 006)
INSERT INTO tbltemp999 VALUES ('MOBL','2004-08-16 12:08:32.000',6 005)
INSERT INTO tbltemp999 VALUES ('MOBL','2004-09-22 12:24:45.000',6 006)
INSERT INTO tbltemp999 VALUES ('MOBL','2004-09-22 12:28:25.000',6 005)
INSERT INTO tbltemp999 VALUES ('MOBL','2004-11-03 17:53:38.000',6 006)
INSERT INTO tbltemp999 VALUES ('MOBL','2004-11-03 17:57:02.000',6 005)
INSERT INTO tbltemp999 VALUES ('MOBL','2004-11-04 12:09:10.000',6 006)
INSERT INTO tbltemp999 VALUES ('MOBL','2004-11-04 12:12:48.000',6 005)
The EventID 6006 represents a system shut down, and the EventID 6005
represents a sytem start. Therefore from the above data you cans see
that the machine is not rebooted very often.

My problem occurs when you select a date BETWEEN something where there
is no data. ie BETWEEN 2004-10-01 AND 2004-10-15 my desired result
from this should return 15 days of uptime but as there is no data it
is returning nothing:(

Can anyone work this one out?

Thanks
Sunny

CREATE TABLE SystemEvents
(
machine_name VARCHAR(20) NOT NULL,
event_datetime DATETIME NOT NULL,
event_id INT NOT NULL,
PRIMARY KEY (machine_name, event_datetime)
)

-- Sample data
INSERT INTO SystemEvents (machine_name, event_datetime, event_id)
VALUES ('MOBL','2004-08-16 12:05:14.000',6 006)
INSERT INTO SystemEvents (machine_name, event_datetime, event_id)
VALUES ('MOBL','2004-08-16 12:08:32.000',6 005)
INSERT INTO SystemEvents (machine_name, event_datetime, event_id)
VALUES ('MOBL','2004-09-22 12:24:45.000',6 006)
INSERT INTO SystemEvents (machine_name, event_datetime, event_id)
VALUES ('MOBL','2004-09-22 12:28:25.000',6 005)
INSERT INTO SystemEvents (machine_name, event_datetime, event_id)
VALUES ('MOBL','2004-11-03 17:53:38.000',6 006)
INSERT INTO SystemEvents (machine_name, event_datetime, event_id)
VALUES ('MOBL','2004-11-03 17:57:02.000',6 005)
INSERT INTO SystemEvents (machine_name, event_datetime, event_id)
VALUES ('MOBL','2004-11-04 12:09:10.000',6 006)
INSERT INTO SystemEvents (machine_name, event_datetime, event_id)
VALUES ('MOBL','2004-11-04 12:12:48.000',6 005)

-- All uptime periods
CREATE VIEW UptimePeriods (machine_name, uptime_begin, uptime_end)
AS
SELECT SE1.machine_nam e,
SE1.event_datet ime,
COALESCE(MIN(SE 2.event_datetim e),
CAST('99991231' AS DATETIME))
FROM SystemEvents AS SE1
LEFT OUTER JOIN
SystemEvents AS SE2
ON SE1.machine_nam e = SE2.machine_nam e AND
SE2.event_id = 6006 AND
SE2.event_datet ime > SE1.event_datet ime
WHERE SE1.event_id = 6005
GROUP BY SE1.machine_nam e, SE1.event_datet ime

-- Uptime periods between two given datetimes
-- @begin_period is inclusive and @end_period is exclusive
CREATE FUNCTION UptimePeriodsBe tween
(@begin_period DATETIME, @end_period DATETIME)
RETURNS TABLE
AS
RETURN(
SELECT P.machine_name AS machine_name,
CASE WHEN P.uptime_begin <= @begin_period
THEN @begin_period
ELSE P.uptime_begin
END AS uptime_begin,
CASE WHEN P.uptime_end <= @end_period
THEN P.uptime_end
ELSE @end_period
END AS uptime_end
FROM UptimePeriods AS P
WHERE P.uptime_end > @begin_period AND
P.uptime_begin < @end_period
)

-- Uptime period durations between two given datetimes,
-- in days, hrs, mins, secs
-- @begin_period is inclusive and @end_period is exclusive
CREATE FUNCTION UptimeDurationB etween
(@begin_period DATETIME, @end_period DATETIME)
RETURNS TABLE
AS
RETURN(
SELECT machine_name,
SUM(DATEDIFF(SE COND, uptime_begin, uptime_end))/86400 AS days_up,
SUM(DATEDIFF(SE COND, uptime_begin, uptime_end))%86 400/3600
AS hours_up,
SUM(DATEDIFF(SE COND, uptime_begin, uptime_end))%86 400%3600/60
AS minutes_up,
SUM(DATEDIFF(SE COND, uptime_begin, uptime_end))%86 400%3600%60
AS seconds_up
FROM UptimePeriodsBe tween(@begin_pe riod, @end_period)
GROUP BY machine_name
)

-- Examples

-- Uptime from 20041001 12AM, inclusive, to 20041016 12AM, exclusive
SELECT machine_name, days_up, hours_up, minutes_up, seconds_up
FROM UptimeDurationB etween('2004100 1', '20041016')

machine_name days_up hours_up minutes_up seconds_up
MOBL 15 0 0 0

-- Uptime from 20041103 12AM, inclusive, to 20041104 12AM, exclusive
SELECT machine_name, days_up, hours_up, minutes_up, seconds_up
FROM UptimeDurationB etween('2004110 3', '20041104')

machine_name days_up hours_up minutes_up seconds_up
MOBL 0 23 56 36

-- Uptime from 20041103 12AM, inclusive, to now
DECLARE @now DATETIME
SET @now = CURRENT_TIMESTA MP -- 2004-11-19 15:28:12.417
SELECT machine_name, days_up, hours_up, minutes_up, seconds_up
FROM UptimeDurationB etween('2004110 3', @now)

machine_name days_up hours_up minutes_up seconds_up
MOBL 16 15 21 10

--
JAG

[Sunny K]

Thanks Jag, you are a legend!

I owe you!

Thanks again
Sunny