Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2012

    Unanswered: Aggregate, Group and DateDiff function required.

    Hi. I am Oatmeal from Atlanta, GA. Need to this forum and hope I can learn and eventually contribute to this forum But for now I have some questions.

    Below is a list of the 3 tables.

    Table1-Location = LocationID
    Table2-CableBoxes = CableBoxID, LocationID, StartDate, EndDate
    Table3-Events = CableBoxID, EventID

    Table1 is a location table for all locationID. Each location can have only ONE active cablebox but can have a history of other cables boxes that have been used and replaced.. Table2 is a table that contains all information about CableBoxes, which can be current or previous cablebox that is/was tied to the LocationID. It is a 2nd level dimension table which includes a history of any previous cables boxes that tie to a locationID. There is a StartDate and EndDate for all cablebox. Enddate = '12-31-9999' means it is the current cablebox. Other cable boxes with different enddate means it was replaced because of fault.

    I am trying to find how many CableBoxID have been in each location (lcoationID). I also want to include how many days the previous non-active cables boxes stayed at each location. I want my results to be anything that is greater than 1 for cable boxes in a locaiton. Basically how many cablebox have been send to the location in total but also group by duration(time/days) the results to how long the 'previous' cable boxes stayed at the location. Essentially I want the data so I can analyize for patterns.

    My in progress statement:

    SELECT L.LocationID, COUNT(C.CableBoxID)
    FROM Location L INNER JOIN
    CableBoxes C
    ON L.locationID = C.locationID
    INNER JOIN Events E
    ON C.CableBoxID = E.CableboxID
    WHERE C.CableBoxID > 1
    GROUP BY L.LocationID

    Example of DATE


    PK (Surrogate Key), LocationID
    12345, 0000001
    12312, 0000002
    12329, 0000003

    ** LocationID could technically be the PK but it is not.

    ABC1 (Suggogate Key), CableBoxID, LocationID, StartDate, EndDate
    1z234, 123A2, 0000001, 01-01-2007, 06-01-2007
    1sw23, 132G1, 0000001, 06-02-2007, 10-01-2007
    21aq1, 1awh1, 0000001, 10-02-2007, 12-31-9999
    3123a, 123fr, 0000002, 01-01-2007, 03-01-2008
    321as, 321j3, 0000002, 03-02-2008, 12-31-9999
    18812, 1ss23, 0000003, 02-15-2007, 12-31-9999

    ** As you can see Location 0000001 have a history with a total 3 cable boxes or prior history of 2 defective boxes, 0000002 has 1 defective box and 0000003 have zero. All 3 currently have a working Cablebox


    EVentLogID (Surrogate Key), EventID, Event Name, CableboxID

    ASDF, 001, Overheat, 123A2
    REWQ, 002, NoConnection, 132G1
    VCXZ, 010, VoltageFlux, 123fg

    ** The Event table is huge as it has every event ever recorded. Some events does not mean the cablebox does not work but just totality of the events being sent from the cablebox.

    What I require on top of the statement you provided is a way to calculate the uptime (days) of the cablebox before they become inoperable. In the data provided above. Defective Cablebox: 123A2 had an uptime of 150 days or 5 (months) x 30 days(days in month) (I am rounding off here). Defective CableBox: 132G1 has 119 days OR 4 months - 1 day and both were in location 0000001.

    I wanted to go DateDiff per someone's suggestion but Oracle does not use this function. Anyhelp would be appreciated.

  2. #2
    Join Date
    Aug 2003
    Where the Surf Meets the Turf @Del Mar, CA
    Provided Answers: 1
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Feb 2012


    Can you offer any incite? I posted in the other forum hoping someone would steer me in the right direction?

  4. #4
    Join Date
    Jun 2003
    Toronto, Canada
    Provided Answers: 1
    To calculate the date difference you simply subtract one date from another.

Tags for this Thread

Posting Permissions

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