Need help with a relatively simple query

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 2008 Forums

Transact-SQL (2008)

Need help with a relatively simple query

Author

Topic

jbranch
Starting Member

2 Posts

Posted - 2011-04-11 : 11:09:45

Forgive me, the date functions are from oracle - but are probably similar to sql server.

I have 2 tables:

Table A:
Deployment(date/time) | Retrieval(date/time)

Table B:
OutageDate(date/time) | lengthofouttage (int, in minutes)

I can get the total up time in minutes for each year from Table A with this query:

SELECT SUM(((TO_DATE(RETRIEVAL,'DDHH24MI"Z"MONRR') - TO_DATE(DEPLOYMENT,'DDHH24MI"Z"MONRR')) * 24*60)) as TotalUpTime, EXTRACT(YEAR FROM TO_DATE(DEPLOYMENT,'DDHH24MI"Z"MONRR')) as Year
FROM TABLE.A
GROUP BY EXTRACT(YEAR FROM TO_DATE(DEPLOYMENT,'DDHH24MI"Z"MONRR'))
ORDER BY year ASC

I can get the total downtime in minutes for each year with this query:

SELECT SUM(TIME) as TotalDownTime, EXTRACT(YEAR FROM TO_DATE(OUTAGE_DATE,'DDHH24MI"Z"MONRR')) As Year
FROM TABLE.B
GROUP BY EXTRACT(YEAR FROM TO_DATE(OUTAGE_DATE,'DDHH24MI"Z"MONRR'))
ORDER BY year ASC

I would like to get a percentage of uptime for each year, so:
(TotalUptime - TotalDownTime) / TotalUptime * 100 = uptime in percent

How do I join these 2?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-11 : 11:27:46

One way to do it would be as follows. Not tested because I am not familiar with Oracle. You may get more reliable answers on an Oracle or general DB forum (such as dbforums.com).

WITH Uptime AS
(
    SELECT
       SUM(
           (
               (
                   TO_DATE(RETRIEVAL, 'DDHH24MI"Z"MONRR') - TO_DATE(DEPLOYMENT, 'DDHH24MI"Z"MONRR')
               ) * 24 * 60
           )
       ) AS TotalUpTime,
       EXTRACT(YEAR FROM TO_DATE(DEPLOYMENT, 'DDHH24MI"Z"MONRR')) AS YEAR
    FROM
       TABLE.A
    GROUP BY
       EXTRACT(YEAR FROM TO_DATE(DEPLOYMENT, 'DDHH24MI"Z"MONRR'))
       --ORDER BY year ASC
),
Downtime AS
(
    SELECT
       SUM(TIME) AS TotalDownTime,
       EXTRACT(YEAR FROM TO_DATE(OUTAGE_DATE, 'DDHH24MI"Z"MONRR')) AS YEAR
    FROM
       TABLE.B
    GROUP BY
       EXTRACT(YEAR FROM TO_DATE(OUTAGE_DATE, 'DDHH24MI"Z"MONRR'))
    -- ORDER BY year ASC
)
SELECT
   COALESCE(u.Year, d.Year) AS YEAR,
   TotalUpTime,
   TotalDownTime,
   ISNULL((TotalUptime - TotalDownTime) / NULLIF(TotalUptime, 0), 0) * 100.0 AS 
   UptimePercent
FROM
   Uptime u
   FULL JOIN Downtime d
      ON  u.Year = d.YEAR
ORDER BY
   1 ASC

jbranch
Starting Member

2 Posts

Posted - 2011-04-11 : 11:56:42

Thanks for the help, i will give it a try!

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-04-11 : 19:13:03

>> I have 2 tables: <<

Please post real DDL. Learn to use ISO-11179 rules for the data element names, avoid needless dialect and use ISO-8601 temporal formats, codes and so forth. People cannot read your mind, so post your code and clear specs if you really want help.

This is just basic netiquette and has nothing to do with your product. Also, we do not prefix tables with “T” – that was BASIC-- and stop using reserved words for data element names.

CREATE TABLE Deployments
(deployment time DATETIME NOT NULL PRIMARY KEY,
retrieval_time DATETIME NOT NULL,
CHECK (deployment time < retrieval_time));

Your next table is wrong. You posted:

CREATE TABLE Outages
(outage_time DATETIME NOT NULL,
length_of_outrage INTEGER NOT NULL);

But the ISO model of time is a duration – two points in the continuum or a half-open interval. A NULL end time is an on-going event.

CREATE TABLE Outages
(outage_start_time DATETIME NOT NULL PRIMARY KEY,
outage_end_time DATETIME,
CHECK (outage_start_time < outage_end_time));

I am not sure about the half-open intervals, but here is a guess

SELECT SUM (DATEDIFF (MINUTES, COALESCE (retrieval_time, '2011-12-31 23:59:59.999'), deployment time) AS uptime_tot
FROM Deployments
WHERE CAST (outage_start_time AS DATE)
BETWEEN '2011-01-01' AND '2011-12-31';

SELECT SUM (DATEDIFF (MINUTES, COALESCE (outage_end_time, '2011-12-31 23:59:59.999')
outage_start_time) AS outage_tot
FROM Outages
WHERE CAST (outage_start_time AS DATE)
BETWEEN '2011-01-01' AND '2011-12-31';

Put these in CTEs and do the math.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources