SQL Server Performance

assistance with counting script...

Discussion in 'General Developer Questions' started by newlandj, Sep 10, 2003.

  1. newlandj New Member

    Hello,

    I have a table called transactionxlog with the following columns and sample data in it:

    Tmessage logdate


    Printed (3432) 09/08/03 Building: 67 09/09/03 09:46:19
    viewed (3431) 09/07/03 Building: 23 09/09/03 09:46:19

    I have to report a count of each use by building since the previous run date of the script. Basically on a Monday morning, I need to run a script that totals the use by building 67 since the last time I ran the script (usually Friday morning before unless Friday was a holiday). I don't need to count every different building in one script, I can just replicate the script and have the billing admin click on different ones.

    Can anyone help me out with this?

    Thanks

    Jason
  2. Twan New Member

    Hi Jason,

    Are all the messages nicely formatted like the above?

    if so, then I'd do



    alter table x
    add building_number as
    convert( int, substring( tmessage, charindex( 'Building: ', tmessage ) + datalength( 'Building: ' ), 30 ) )


    create index ix1 on x( logdate, building_number )

    select building_number,
    count(*)
    from x
    where logdate > @date
    group by building_number

    you can also replace the group by with a where building_number = x if you only want a single building (in which case I'd consider changing the order of the columns in the index


    If your message is not as nicely formatted then the formula for the column may need to be a little more complicated such as



    alter table x
    add building_number as
    case when charindex( 'Building: ', tmessage ) > 0
    then convert( int, substring( tmessage, charindex( 'Building: ', tmessage ) +
    datalength( 'Building: ' ), 30 ) )
    end

    Cheers
    Twan
  3. newlandj New Member

    Thanks for the input Twan. Unfortunately, the powers that be are limiting me to query only. I can not alter table, or create indexes. Any way to do it without using those options?

    Thanks

    Jason
  4. newlandj New Member

    I almost forgot, the information in the tmessage column is not constant, or as pretty as listed, they are all different lengths, the only thing that is constant in there is the 'Building: 23' part


    Thanks

    Jason
  5. Twan New Member

    Hi Jason,

    In that case you'll need to do the query with brute force...



    select building_number = case when charindex( 'Building: ', tmessage ) > 0
    then convert( int, substring( tmessage, charindex( 'Building: ', tmessage ) +
    datalength( 'Building: ' ), 30 ) )
    end,
    count(*)
    from x
    where logdate > @date
    group by case when charindex( 'Building: ', tmessage ) > 0
    then convert( int, substring( tmessage, charindex( 'Building: ', tmessage ) +
    datalength( 'Building: ' ), 30 ) )
    end

    Haven't got SQL to check the syntax... so I hope it works off the bat

    Cheers
    Twan

    PS note that it may require a tablescan to do this... This may not be the best for performance...

Share This Page