Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

cursor Expand / Collapse
Author
Message
Posted Sunday, March 03, 2013 12:49 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 04, 2013 5:50 PM
Points: 7, Visits: 17
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
Post #1425994
Posted Sunday, March 03, 2013 8:55 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 12:42 AM
Points: 2,338, Visits: 3,158
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



No loops! No CURSORs! No RBAR! Hoo-uh!

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?

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Are you too recursively challenged?
Splitting strings based on patterns can be fast!
Post #1426069
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse