Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2003
    Posts
    2

    Unanswered: Read + Statistics from SQl

    I have a very big problem. I must do this vbs or i'll be fired....Help me, i'm newbie at vb...

    I have a SQL2000 database with a columns defined like that:
    Server name, data, service1, service2, ...., service7.

    Records looks something like that:

    k2-ss-ss, 2003-08-13, 1, 0, 1, 1, 1, 1, 0.
    k2-qq-ww, 2003-08-14 0, 0, 1, 1, 0, 0, 1.

    "1" - means running
    "0" - means stopped

    example
    a write: "service1", "2003-08-11 ", "2003-08-14"
    Script list, that service1 worked 94,3% at choosed period, on k2-ss-ss.
    Script list, that service1 worked 06,3% at choosed period, on k2-qq-ww.

    Records to the database are writen every 3 minutes.

    Can anyone help me? Some piece of code?

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    create procedure SaveYerAss
    (@Service varchar(50),
    @StartDate datetime,
    @EndDate datetime)
    as

    select @Service + ' worked ' +
    case @Service
    when 'Service1' then 100 * Service1Count/RecordCount
    when 'Service2' then 100 * Service2Count/RecordCount
    when 'Service3' then 100 * Service3Count/RecordCount
    when 'Service4' then 100 * Service4Count/RecordCount
    when 'Service5' then 100 * Service5Count/RecordCount
    when 'Service6' then 100 * Service6Count/RecordCount
    when 'Service7' then 100 * Service7Count/RecordCount
    end
    + '% at choosed period, on ' + Servername + '.'
    From
    (select servername,
    sum([service1]) Service1Count,
    sum([service2]) Service2Count,
    sum([service3]) Service3Count,
    sum([service4]) Service4Count,
    sum([service5]) Service5Count,
    sum([service6]) Service6Count,
    sum([service7]) Service7Count,
    count(*) RecordCount
    from [YerDataTable]
    where [data] between @StartDate and @EndDate) SummarizedData

  3. #3
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    bm, your subquery lacks a
    group by servername
    clause.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Well, he ought to do SOMETHING to save his own job!

  5. #5
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    bm, I take it as a THANKS.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  6. #6
    Join Date
    Dec 2002
    Posts
    63
    What do you want to bet that was a homework problem?

Posting Permissions

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