Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2010
    Posts
    18

    Unanswered: Help with relatively basic query

    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:

    Code:
    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:
    Code:
    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?

  2. #2
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by ducati321 View Post
    How do I join these 2?
    YEAR column seems like a good choice.
    Code:
    SELECT <expression>
    FROM (<first query without ORDER BY clause>) a
     <proper type of join>
     (<second query without ORDER BY clause>) b
     ON a.year = b.year
    ORDER BY <depends on the above>
    Proper type of join depends on requirements - what shall be a result when there are rows only in one table (A or B - two different cases) for a given year?

  3. #3
    Join Date
    Jun 2010
    Posts
    18
    Quote Originally Posted by flyboy View Post
    YEAR column seems like a good choice.
    Code:
    SELECT <expression>
    FROM (<first query without ORDER BY clause>) a
     <proper type of join>
     (<second query without ORDER BY clause>) b
     ON a.year = b.year
    ORDER BY <depends on the above>
    Proper type of join depends on requirements - what shall be a result when there are rows only in one table (A or B - two different cases) for a given year?
    works perfectly, thanks so much!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •