Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2005

    Unanswered: I posted this question once before...

    But I think I needed it to be more hypothetical. I am only a couple of weeks into this SQL stuff, but, I have considerable Progress 4GL experience. I thought I would just fall right in, but, I thought wrong. I have something I want to do which I could do easily in PROGRESS, but, confuses me greatly here

    I have an application which tracks software usage per PC. I get one record a day for each application executed on each machine that executes it. I have about 30K machines which have resulted in about 57 Million usage records per year (we only keep 1 year of history).

    I need to provide my requestor with an Excel Sheet showing how many hours each PC that has an installed application has executed that app.

    I don't care how the output is sorted, I can do that in Excel, but, I don't want to include the usage table in the select because that kills the memory usage. I want:

    FOR EACH workstation
    EACH product
    OF workstation
    WHERE = "DreamWeaver"

    SET acthrs = 0
    runhrs = 0.

    FOR EACH usage
    OF product

    SET acthrs = acthrs + (usage.actsecs / 3600)
    runhrs = runhrs + (usage.runsecs / 3600).



    Granted there is more involved, but that's the general idea (in Progress form). Can I do something similar in straight SQL. Where's the beginner's forum?

  2. #2
    Join Date
    Jul 2003
    what tables are involved?
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Oct 2005

    Many tables are involved, but, as a skeleton

    the component table is a good stepping off point.

    the component table is related to workstation by workstationoid.
    component is related to product by productoid.
    swusage is related to component by componentoid.


    SELECT wk.assettag, wk.serial, wk.owner,, pr.ver, acthrs, runhrs
    FROM workstation wk, component cp, product pr
    WHERE wk.workstationoid = cp.workstationoid
    AND pr.productoid = cp.productoid;

    now I need to count up usage within this loop for each wk/pr combination.

    loop through all swusage records where cp.componentoid = swusage.componentoid (FK1, I2)

    Total number of starts (swusgae.startupcount) Could be several per day.
    Sum runhrs (swusage.runtimeseconds / 3600) Hours application is running.
    Sum acthrs (swusage.activeseconds / 3600) Hours application had focus.


  4. #4
    Join Date
    Oct 2005

    In fact a little procedure would be nice

    If I could write a standalone procedure where a parameter would be componentoid and the return would be startcnt, runhrs, and acthrs, that would be sweet.

  5. #5
    Join Date
    Sep 2002
    Provided Answers: 1
    I think you are barking up the wrong tree here. Trying to beat SQL by writing procedural code is a mug's game. The correct approach is to write a straightforward SQL statement to select, group and sum the data required, and then tune that SQL if necessary. If your tables have been analyzed and have indexes on the join columns then the optimizer normally does a good job anyway.

    In your other thread I have shown the sort of SQL statement I would expect you to be writing for this.

Posting Permissions

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