Post reply

complex query

  • August 7, 2014 at 9:06 am

    #291697

    hi all,

    i need to find gap in time (minutes) when value exceed the value: 55.

    create table #temp ( [value] int, date_value datetime)

    insert #temp ([value], date_value )

    select 10 ,'2014-07-15 08:00:31.000'

    union all

    select 55,'2014-07-15 08:00:41.000'

    union all

    select 40, '2014-07-15 08:00:52.000'

    union all

    select 35,'2014-07-15 08:01:10.000'

    union all

    select 55,'2014-07-15 08:01:10.000'

    union all

    select 55,'2014-07-15 08:01:31.000'

    union all

    select 50,'2014-07-15 08:01:43.000'

    result :

    --record number 2 --record number 3 record number= 5 record number= 7

    select sum (datediff (min,'2014-07-15 08:00:41.000','2014-07-15 08:00:52.000')) +datediff (min,'2014-07-15 08:01:10.000','2014-07-15 08:01:43.000'))_

    find first value above 55 when found check next record :

    next record below 55 summerize value of datediff between 2 record.

    if next record is still above 55 continue to next recode until you find value

    below 55 if found then summerize datediff between first value the exceed 55

    to the record the contain value below 55.

    i hope i was clear 🙂 and thanks in advance

    sharon

  • August 7, 2014 at 9:32 am

    #1736049

    What SQL Server version do you use?

    If it's SS2k8 (as indicated by the forum you posted in) it looks like an issue for Jeff's running total[/url].

    Disclaimer: please make sure you fully understand the requirements to get valid an consistent result when using the soltion I referred to!


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • August 7, 2014 at 10:04 am

    #1736061

    hi ,

    sql 2005 sp4

    sharon

  • August 7, 2014 at 10:42 am

    #1736087

    sharon-472085 (8/7/2014)

    hi ,

    sql 2005 sp4

    sharon

    LutzM's recommendation still stands. Read the article and the follow on discussion so that you fully understand how to implement this solution. The rules are very important.

Viewing 4 posts - 1 through 3 (of 3 total)

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