Post reply

I want to assign SUM of group by values to variable...

  • March 26, 2009 at 2:33 am

    #132508

    this is my query,

    SELECT MachineName,

    SUM( ( CASE WHEN datetime >= DATEADD( MINUTE, -5, GETDATE() ) THEN 1 ELSE 0 END ) ) Last5Min,

    SUM( ( CASE WHEN datetime>= DATEADD( HOUR, -1, GETDATE() ) THEN 1 ELSE 0 END ) ) LastHour,

    SUM( ( CASE WHEN datetime >= DATEADD( DAY, -1, GETDATE() ) THEN 1 ELSE 0 END ) ) Last24Hours

    FROM

    Table2 T

    WHERE

    datetime >= DATEADD( DAY, -1, GETDATE() )

    GROUP

    BY MachineName

    this will give output like this

    MachineName Last5Min LastHour Last24Hour

    A 4 65 1560

    B 5 50 1200

    c 10 80 1300

    Now I want to assign this values to a variables and do some other process according to sum values,

    like if any of last 5min value is equal to zero raise error, or any of the LastHour value is equal to or less than 20 raise an error or any of last24Hour value is equal or less than 1000 raise and error...

    (actually I want to raise an SQL alert message or SQL event that can be captured by windows OS, to write event viewer or to make a SQL agent job to send emails..) but I can manage the rest. just the getting sum values to variables will work as of now... thanks

    really appreciate any (ANY) idea how to do this...

  • March 26, 2009 at 3:00 am

    #966471

    If you are confident the sql statement will return one row, then you can capture the values in variables.

    If more than one row the last row info will available in variable.

    I would like to suggest in your requirement temp tables, cursors are help.

    Thanks,

    KK

  • March 26, 2009 at 3:07 am

    #966473

    Thanks KK, but above query will return multiple rows group by machine name...

    yeah I can simplify.. but number of machines can be vary..

    I know there are some clean and simple ways to do this kind of tasks.., I'm working on this.. but meanwhile appreciate and expect any of your ideas and help..

  • March 26, 2009 at 3:23 am

    #966484

    b

  • March 26, 2009 at 3:45 am

    #966496

    As KK said, I'd change it to insert into a temporary table, and then check that table for the condition that would trigger the alerts you're after.

    You could also write them to a permanent table, along with a GetDate set at the start of the SP. Look for results with that Getdate value, which would scan the latest results but would also supply you with a historic record of the machines and values at set intervals.

    Steve Hall
    Linkedin
    Blog Site

  • March 26, 2009 at 7:46 am

    #966662

    a

  • March 27, 2009 at 12:11 am

    #967214

    Thanks all.. I did this using temp table and a curser its working fine.

    Further I'm using SQL agent to execute this as a JOB and it sends alerts when certain threshold comes.

    again thank you all.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply