473,883 Members | 1,665 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Select from a date range

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
Jul 20 '05 #1
7 2833
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
Jul 20 '05 #2
> 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!
Jul 20 '05 #3
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
Jul 20 '05 #4

"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.
Jul 20 '05 #5
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.
Jul 20 '05 #6
"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

Jul 20 '05 #7
Thanks Jag, you are a legend!

I owe you!

Thanks again
Sunny
Jul 20 '05 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
1948
by: Got2Go | last post by:
Hello Group, I have a table that has 3 columns: ID (int), datetime, Value(varchar) ID = ID for the SNMP device datetime = time record was added value = value added for that device. This table contains sample # values taken from a device (SNMP) every 5
4
2742
by: psql-mail | last post by:
I am running a SELECT to get all tuples within a given date range. This query is much slwoer than i expected - am i missing something? I have a table 'meta' with a column 'in_date' of type timestamp(0), i am trying to select all records within a given date range. I have an index on 'in_date' and I also have an index on date(in_date). The queries I am doing are between dates rather than timestamps. Immeadiately prior to running the...
3
6741
by: Ker | last post by:
I have a query that works great. It gives me the min for multiple fields. Within this query, I also need to get the max of some fields too. I currently have output of Date Name Min Start 9/1/03 Walker Rhines 7:00AM I also need Max such as Max End 3:00PM
10
2180
by: MLH | last post by:
Suppose the following... Dim A as Date A=#7/24/2005# I wish to compare value of A against 2 other values: 1) 8/1/2005 2) 9/1/2005 Which is better and why... First:
18
38260
by: dfetrow410 | last post by:
Anyone have some code that will do this? Dave
2
13570
by: chrisale | last post by:
Hi All, I've been racking my brain trying to figure out some sort of Sub-Select mySQL statement that will create a result with multiple rows of averaged values over a years time. What I have is weather data. There is a new record every 5 minutes, every day. So. What I want to do with one SQL statement is figure out the Average of those 5 minute records over each day, for every day of the year.
2
3908
by: shannonwhitty | last post by:
I am able to extract dates in the correct format i.e. SELECT CONVERT(VARCHAR(8), GETDATE(), 3) =dd/mm/yy My issue is that my users are selecting a date in this format and I need to select data based on this range. i.e.
3
3270
by: 6afraidbecause789 | last post by:
If able, can someone please help make a Where clause that strings together IDs in a multi-select listbox AND includes a date range. I wasn’t thinking when I used the code below that strings together the IDs of Clients from a multi-select listbox in an unbound text field, txtCriteria, on a form that is used to pick different reports. It appears that I now have so many clients that I’ve reached the 255 character limit in the txtCriteria...
4
1851
by: scalda34 | last post by:
This in Access 2003. Pardon me if this is in the wrong spot, but I hoped someone could help. I have a table with multiple records that have a text represented date field. The date is in a format like: "06/01/2009" I need to select all the fields from the table but only the 6 oldest rows that fall in a range for each group of "DocType, PayTo, ContactName, ContactNumber, DocFooter, PQBName, LetterDate, RetireeFirstName, RetireeLastName,...
0
9932
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
11112
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10730
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10405
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9559
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
7114
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5980
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
4205
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3228
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.