Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2003
    Posts
    5

    Unanswered: Help needed with SQL Query

    I have a table called ActiviyLog

    it has columns called HOURS PHASES and months


    for every phase i need to get the sum of hours where
    month = current month and hours where month less than current month but greater than April

    problem is .. i need this information in one record

    so the record will look like

    Column1:Phase Column2um of hours where month=currentmonth
    Column3um of hours where month less than current month but greater than april;

    any way to do this in a sql query ?

  2. #2
    Join Date
    Oct 2003
    Location
    Switzerland
    Posts
    140
    One of the numerous possibilities, might not be the fastest though

    SELECT
    PHASES.PHASE,
    SUM_CURRENT.HOURS AS CURRENT_HOURS,
    SUM_PREVIOUS_HOURS AS PREVIOUS_HOURS

    FROM (SELECT DISTINCT ACTIVITYLOG) PHASES

    LEFT OUTER JOIN
    (SELECT PHASE, SUM(HOURS)
    FROM ACTIVITYLOG
    WHERE MONTHS= EXTRACT (MONTH FROM SYSDATE)
    GROUP BY PHASE) SUM_CURRENT
    ON SUM_CURRENT.PHASE = PHASES.PHASE

    LEFT OUTER JOIN
    (SELECT PHASE, SUM(HOURS)
    FROM ACTIVITYLOG
    WHERE MONTHS<EXTRACT (MONTH FROM SYSDATE))
    AND MONTHS > 4
    GROUP BY PHASE) SUM_PREVIOUS
    ON SUM_PREVIOUS.PHASE = PHASES.PHASE

    Should return 1 row for every distinct PHASE

  3. #3
    Join Date
    Oct 2003
    Location
    Switzerland
    Posts
    140
    Silly me !! (I'll bang my head against the wall after writing this)

    SELECT
    PHASE,
    SUM(CASE WHEN MONTHS = EXTRACT (MONTH FROM SYSDATE)
    THEN HOURS
    ELSE 0
    END) AS CURRENT_HOURS,
    SUM(CASE WHEN MONTHS < EXTRACT (MONTH FROM SYSDATE)
    AND MONTHS > 4
    THEN HOURS
    ELSE 0
    END) AS PREVIOUS_HOURS
    FROM ACTIVITYLOG
    GROUP BY PHASE

  4. #4
    Join Date
    Nov 2003
    Posts
    5

    Gracias

    hey

    thanks for the prompt reply.. the second query seems good....
    I havent got the time to try it right now.. but i will be implementing it soon.

    Could you also suggest some resources on the net where I can learn about stored procedures, functions etc... PL/SQL basically. and generally about database optimizations.. query optimizations et al ?

  5. #5
    Join Date
    Oct 2003
    Location
    Switzerland
    Posts
    140
    Sorry, I don't know about any particular web-sites to learn pl/sql, although there must be, I'm sure.

    I learned the hard way = Oracle documentation...

    Good luck.

Posting Permissions

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