cursor

  • Hello,

    Following is my requirement.

    Table rows:

    SLN Id StartDate EndDate Duration TimeElapsed

    1 1 1/1/2012 10.12 1/1/2012 10.13 0day 00:01:00 0-days 00:01:00

    1 1 1/1/2012 10.14 1/1/2012 10.15 0day 00:01:00 0-days 00:03:00

    1 1 1/1/2012 10.15 1/2/2012 10.16 1day 00:01:00 1-days 00:04:00 (total- counter reset)

    2 2 1/1/2012 10.12 1/1/2012 10.13 0day 00:01:00 0-days 00:01:00

    2 2 1/1/2012 10.14 1/1/2012 10.15 0day 00:01:00 0-days 00:03:00

    2 2 1/1/2012 10.15 1/2/2012 10.16 1day 00:01:00 1-days 00:04:00 (total- counter reset)

    I need to calculate Duration and TimeElapsed.

    Can somebody please give me sample query for this requirement.... I have all these values in my temp table, so either I use cursor or call a function and use cursor inside it... or is there any other option

    Achieving this C# is quite a cake walk ... Is using cursor the only option or do we have more options in db side.

    Many thanks

  • Something like this should get you started:

    CREATE TABLE #MyDates

    (SLN INT, ID INT, StartDate DATETIME, EndDate DATETIME

    ,Duration AS (DATEDIFF(minute, StartDate, EndDate))

    ,TimeElapsed BIGINT

    ,PRIMARY KEY (SLN, StartDate))

    INSERT INTO #MyDates (SLN, ID, StartDate, EndDate)

    SELECT 1, 1, '1/1/2012 10:12','1/1/2012 10:13'

    UNION ALL SELECT 1, 1, '1/1/2012 10:14','1/1/2012 10:15'

    UNION ALL SELECT 1, 1, '1/1/2012 10:15','1/2/2012 10:16'

    UNION ALL SELECT 2, 2, '1/1/2012 10:12','1/1/2012 10:13'

    UNION ALL SELECT 2, 2, '1/1/2012 10:14','1/1/2012 10:15'

    UNION ALL SELECT 2, 2, '1/1/2012 10:15','1/2/2012 10:16'

    DECLARE @StartTime DATETIME = '1900-01-01'

    ,@SLN INT = 0

    UPDATE #MyDates WITH(TABLOCKX)

    SET TimeElapsed = DATEDIFF(minute, CASE WHEN @SLN = SLN THEN @StartTime ELSE StartDate END, EndDate)

    ,@StartTime = CASE WHEN @SLN <> SLN THEN StartDate ELSE @StartTime END

    ,@SLN = SLN

    OPTION (MAXDOP 1)

    SELECT SLN, ID, StartDate, EndDate

    ,Duration=CAST(Duration / 1440 AS VARCHAR(3)) + ' day' +

    CASE Duration / 1440 WHEN 1 THEN ' ' ELSE 's ' END +

    LEFT(DATEADD(minute, Duration % 1440, CAST('00:00' AS TIME)), 8)

    ,ElapsedTime=CAST(TimeElapsed / 1440 AS VARCHAR(3)) + ' day' +

    CASE TimeElapsed / 1440 WHEN 1 THEN ' ' ELSE 's ' END +

    LEFT(DATEADD(minute, TimeElapsed % 1440, CAST('00:00' AS TIME)), 8)

    FROM #MyDates

    DROP TABLE #MyDates

    You may want to consult the following article by Jeff Moden to learn how to apply the Quirky Update (QU) method I used to solve running totals problems (especially the part about the rules, which I believe I followed in the UPDATE statement).

    Solving the Running Total and Ordinal Rank Problems[/url]


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

Viewing 2 posts - 1 through 1 (of 1 total)

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