Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2005
    Posts
    25

    Unanswered: Last 30 Days, Last 60 Days, Last 90 Days, Total

    I have an application that creates a usage record when a user accesses an application. For talking purposes, I will refer to an application like MS Project. I am concerned with two usage files, one, usage which tells me how long the application was running and how long it had focus, and two, a table which contains date and time stamps. I don't know why the designer split the two tables, it appears to be a one to one relationship, but, I won't question that.

    Currently, in my query, I return the MAX(DisplayDate) as the Last Time Accessed, as well as a SUM(RunTimeSecs / 3600). I maintain data in the usage file for 1 year. Therefore my current query reports how many Run Time Hours a user has logged for an application in the last year. My goal is to return data based on that Time Stamp.

    Tables: Usage and TimeTable
    Tables are related by ttoid (TimeTableOID)

    TimeTable contains a Date Field labeled DisplayDate
    Usage contains a SmallInt labeled RunTimeSeconds

    The third subject table is called Product which contains SW descriptions. I should note that not every product has usage records so I use an outer join to get usage. product.prodid = usage.prodid (+)

    So, after all that, my question is: How do I aggregate usage within a date range of 30, 60, 90 days?

    SELECT
    product.name
    SUM(usage.RunTimeSeconds / 3600) AS RunTimeHrs30 (for last 30 days)
    SUM(usage.RunTimeSeconds / 3600) AS RunTimeHrs60 (for last 60 days)
    ...
    FROM
    product,
    usage,
    timetable
    WHERE product.name = 'MS Project'
    AND usage.prodid (+) = product.prodid
    AND timetable.ttoid (+) = usage.ttoid
    GROUP BY
    product.name;

    You know what I mean.

    Thanks

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Like this:

    SELECT
    product.name
    SUM(CASE WHEN something >= trunc(sysdate)-30 THEN usage.RunTimeSeconds / 3600 END) AS RunTimeHrs30 --(for last 30 days)
    SUM(CASE WHEN something >= trunc(sysdate)-60 THEN usage.RunTimeSeconds / 3600 END) AS RunTimeHrs60 --(for last 60 days)

  3. #3
    Join Date
    Oct 2005
    Posts
    25

    Thanks

    Gracias! That makes sense.

  4. #4
    Join Date
    Oct 2005
    Posts
    25
    A couple more questions about this...

    Could statement be rewritten into a single statement like
    CASE
    WHEN something >= TRUNC(sysdate) - 30 THEN SUM(...) AS runhrs30
    WHEN something >= TRUNC(sysdate) - 60 THEN SUM(...) AS runhrs60
    END.

    -or-

    Do I need "CASE" for single condition.

    SUM(case WHEN something >= trunc(sysdate)-30 THEN usage.RunTimeSeconds / 3600 end) AS RunTimeHrs30 --(for last 30 days)

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    A CASE per column in the SELECT clause.

  6. #6
    Join Date
    Oct 2005
    Posts
    25

    In that case, this is my resulting code...

    Is there anyway to simplify this? Not that it needs it for performance (might) just curious.


    ...,
    wkst.LastSuccessfulLoadDate - comp.createdate AS Days_Installed,
    SUM(sfwu.startupcount) AS Starts,
    SUM(CASE WHEN ttab.displaydate >= TRUNC(SYSDATE)-180
    AND ttab.displaydate < TRUNC(SYSDATE)-90
    THEN sfwu.startupcount END) AS Starts180,
    SUM(CASE WHEN ttab.displaydate >= TRUNC(SYSDATE)-90
    AND ttab.displaydate < TRUNC(SYSDATE)-60
    THEN sfwu.startupcount END) AS Starts90,
    SUM(CASE WHEN ttab.displaydate >= TRUNC(SYSDATE)-60
    AND ttab.displaydate < TRUNC(SYSDATE)-90
    THEN sfwu.startupcount END) AS Starts60,
    SUM(sfwu.runtimeseconds) / 3600 AS RunHrs,
    SUM(CASE WHEN ttab.displaydate >= TRUNC(SYSDATE)-180
    AND ttab.displaydate < TRUNC(SYSDATE)-90
    THEN sfwu.runtimeseconds END) /3600 AS RunHrs180,
    SUM(CASE WHEN ttab.displaydate >= TRUNC(SYSDATE)-90
    AND ttab.displaydate < TRUNC(SYSDATE)-60
    THEN sfwu.runtimeseconds END) /3600 AS RunHrs90,
    SUM(CASE WHEN ttab.displaydate >= TRUNC(SYSDATE)-60
    AND ttab.displaydate < TRUNC(SYSDATE)-90
    THEN sfwu.runtimeseconds END) /3600 AS Starts60,
    SUM(sfwu.activeseconds) / 3600 AS ActHrs,
    SUM(CASE WHEN ttab.displaydate >= TRUNC(SYSDATE)-180
    AND ttab.displaydate < TRUNC(SYSDATE)-90
    THEN sfwu.activeseconds END) /3600 AS ActHrs180,
    SUM(CASE WHEN ttab.displaydate >= TRUNC(SYSDATE)-90
    AND ttab.displaydate < TRUNC(SYSDATE)-60
    THEN sfwu.activeseconds END) /3600 AS ActHrs90,
    SUM(CASE WHEN ttab.displaydate >= TRUNC(SYSDATE)-60
    AND ttab.displaydate < TRUNC(SYSDATE)-90
    THEN sfwu.activeseconds END) /3600 AS ActHrs60,
    MAX(ttab.displaydate) AS Last_Used,...
    Thanks!

  7. #7
    Join Date
    Oct 2005
    Posts
    25

    Does SQL have a between command

    Perhaps that would shorten the code is I could rewrite something like

    SUM(CASE WHEN something BETWEEN TRUNC(SYSDATE)-60 AND TRUNC(SYSDATE)-90 THEN sfwu.startupcount END) AS Starts90

    Just learning...

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    But that doesn't have the same meaning. "a BETWEEN b AND c" is equivalent to "a >= b AND a <= c" not "a >= b and a < c"

  9. #9
    Join Date
    Oct 2005
    Posts
    25
    Tony,

    You are very helpful. I appreciate your time and apologize for not stating my question correctly. You are correct, it doesn't have the same meaning as what it looks like I was asking for to begin with. I asked for SUMS for Last 30, 60, and 90. That appears to be inclusive. The fact is, however, for this purpose, I don't want it inclusive. I report total starts, starts between 90 and 180 days, starts between 60 and 90 days, therefore, BETWEEN is a good solution. Code now looks like this:

    SUM(sfwu.startupcount) AS Starts,
    SUM(CASE WHEN ttab.displaydate BETWEEN TRUNC(SYSDATE)-180 AND TRUNC(SYSDATE)-91 THEN sfwu.startupcount END) AS Starts180,
    SUM(CASE WHEN ttab.displaydate BETWEEN TRUNC(SYSDATE)-90 AND TRUNC(SYSDATE)-61 THEN sfwu.startupcount END) AS Starts90,
    SUM(CASE WHEN ttab.displaydate >= TRUNC(SYSDATE)-60 THEN sfwu.startupcount END) AS Starts60,
    SUM(sfwu.runtimeseconds) / 3600 AS RunHrs,
    SUM(CASE WHEN ttab.displaydate BETWEEN TRUNC(SYSDATE)-180 AND TRUNC(SYSDATE)-91 THEN sfwu.runtimeseconds END) /3600 AS RunHrs180,
    SUM(CASE WHEN ttab.displaydate BETWEEN TRUNC(SYSDATE)-90 AND TRUNC(SYSDATE)-61 THEN sfwu.runtimeseconds END) /3600 AS RunHrs90,
    SUM(CASE WHEN ttab.displaydate >= TRUNC(SYSDATE)-60 THEN sfwu.runtimeseconds END) /3600 AS Starts60,
    SUM(sfwu.activeseconds) / 3600 AS ActHrs,
    SUM(CASE WHEN ttab.displaydate BETWEEN TRUNC(SYSDATE)-180 AND TRUNC(SYSDATE)-91 THEN sfwu.activeseconds END) /3600 AS ActHrs180,
    SUM(CASE WHEN ttab.displaydate BETWEEN TRUNC(SYSDATE)-90 AND TRUNC(SYSDATE)-61 THEN sfwu.activeseconds END) /3600 AS ActHrs90,
    SUM(CASE WHEN ttab.displaydate >= TRUNC(SYSDATE)-60 THEN sfwu.activeseconds END) /3600 AS ActHrs60,
    I am so new to this SQL stuff that I am almost embarrassed to even ask a question, but, everyone here is very helpful. Thanks

Posting Permissions

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