none
Cursor or does there exist other option too

    Question

  • Hello,
    below is the snapshot of my table

    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 to calculate duration and time elapsed... or is there any other option on db side???

    Many thanks

    • Edited by StSingh Sunday, March 03, 2013 9:32 PM
    Sunday, March 03, 2013 7:55 PM

Answers


  • DECLARE @tab TABLE(SLN INT,Id INT,StartDate DATETIME,EndDate DATETIME)
    INSERT INTO @tab
    VALUES(1,1,'1/1/2012 10:12','1/1/2012 10:13'),
    (1,1,'1/1/2012 10:14','1/1/2012 10:15'),
    (1,1,'1/1/2012 10:15','1/2/2012 10:16'),
    (2,2,'1/1/2012 10:12','1/1/2012 10:13'),
    (2,2,'1/1/2012 10:14','1/1/2012 10:15'),
    (2,2,'1/1/2012 10:15','1/2/2012 10:16')
    
    SELECT *
    FROM @tab
    
     ;WITH CTE AS
    (
    SELECT *,
      (DATEDIFF(DAY,StartDate,EndDate))-(DATEDIFF(MONTH,StartDate,EndDate)*30) AS dys,
      (DATEDIFF(HOUR,StartDate,EndDate))-(DATEDIFF(DAY,StartDate,EndDate)*24) AS hrs,
      (DATEDIFF(MINUTE,StartDate,EndDate))-(DATEDIFF(HOUR,StartDate,EndDate)*60) AS mns,
      (DATEDIFF(SECOND,StartDate,EndDate))-(DATEDIFF(MINUTE,StartDate,EndDate)*60) AS scs,
      (DATEDIFF(DAY,mn,EndDate))-(DATEDIFF(MONTH,mn,EndDate)*30) AS Elpsd_dys,
      (DATEDIFF(HOUR,mn,EndDate))-(DATEDIFF(DAY,mn,EndDate)*24) as Elpsd_hrs,
      (DATEDIFF(MINUTE,mn,EndDate))-(DATEDIFF(HOUR,mn,EndDate)*60) AS Elpsd_mns,
      (DATEDIFF(SECOND,mn,EndDate))-(DATEDIFF(MINUTE,mn,EndDate)*60)AS Elpsd_scs
    FROM 
     (
      SELECT *,
    	MIN(StartDate) OVER(PARTITION BY SLN,Id ORDER BY SLN,Id) as mn
      FROM @tab
      )T
    )
    SELECT cte.SLN
    ,cte.Id
    ,cte.StartDate
    ,cte.EndDate
    ,CAST(dys AS VARCHAR(10)) + ' day(s) '+
    CASE 
    	WHEN LEN(hrs)=1 
    	THEN '0'+CAST(hrs AS VARCHAR(10)) 
    	ELSE CAST(hrs AS VARCHAR(10)) 
    END +':'+
    CASE 
    	WHEN LEN(mns)=1 
    	THEN '0'+CAST(mns AS VARCHAR(10)) 
    	ELSE CAST(mns AS VARCHAR(10)) 
    END +':'+
    CASE 
    	WHEN LEN(scs)=1 
    	THEN '0'+CAST(scs AS VARCHAR(10)) 
    	ELSE CAST(scs AS VARCHAR(10)) 
    END AS Duration
    ,CAST(Elpsd_dys AS VARCHAR(10)) + ' day(s) '+
    CASE 
    	WHEN LEN(Elpsd_hrs)=1 
    	THEN '0'+CAST(Elpsd_hrs AS VARCHAR(10)) 
    	ELSE CAST(Elpsd_hrs AS VARCHAR(10)) 
    END +':'+
    CASE 
    	WHEN LEN(Elpsd_mns)=1 
    	THEN '0'+CAST(Elpsd_mns AS VARCHAR(10)) 
    	ELSE CAST(Elpsd_mns AS VARCHAR(10)) 
    END +':'+
    CASE 
    	WHEN LEN(Elpsd_scs)=1 
    	THEN '0'+CAST(Elpsd_scs AS VARCHAR(10)) 
    	ELSE CAST(Elpsd_scs AS VARCHAR(10)) 
    END AS TimeElaspsed
    FROM CTE
    
    


    /*

    SLN    Id   StartDate                             EndDate                                    Duration               TimeElaspsed
    1        1    2012-01-01 10:12:00.000    2012-01-01 10:13:00.000    0 day(s) 00:01:00    0 day(s) 00:01:00
    1        1    2012-01-01 10:14:00.000    2012-01-01 10:15:00.000    0 day(s) 00:01:00    0 day(s) 00:03:00
    1        1    2012-01-01 10:15:00.000    2012-01-02 10:16:00.000    1 day(s) 00:01:00    1 day(s) 00:04:00
    2        2    2012-01-01 10:12:00.000    2012-01-01 10:13:00.000    0 day(s) 00:01:00    0 day(s) 00:01:00
    2        2    2012-01-01 10:14:00.000    2012-01-01 10:15:00.000    0 day(s) 00:01:00    0 day(s) 00:03:00
    2        2    2012-01-01 10:15:00.000    2012-01-02 10:16:00.000    1 day(s) 00:01:00    1 day(s) 00:04:00

    */



    Narsimha

    • Proposed as answer by Kalman TothModerator Monday, March 04, 2013 10:02 AM
    • Marked as answer by StSingh Tuesday, March 05, 2013 12:48 AM
    Sunday, March 03, 2013 11:10 PM
  • You did not bother to tell us the name of the table, and you expect us to help you. That is both rude and silly. Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats – you really screwed up on that! Code should be in Standard SQL as much as possible and not local dialect. We have a DATETIME2(0) data type today 

    This is minimal polite behavior on SQL forums. Now we have to guess at everything for you in order to do your job or homework for you. What you did post has not obvious key and the design flaw called “attribute splitting” where you broke the single fact of a timestamp into multiple columns. This violates First Normal Form (1NF). 

    CREATE TABLE Magic_Events
    (sln INTEGER NOT NULL,
     magical_generic_id INTEGER NOT NULL,
     event_start_timestamp DATETIME2(0) NOT NULL PRIMARY KEY, -- wild guess! 
     event_end_timestamp DATETIME2(0) NOT NULL,
     CHECK (event_start_timestamp < event_end_timestamp)

    INSERT INTO Magic_Events
    VALUES
    (1, 1, '2012-01-01 10:12:00', '2012-01-01 10:13:00'),
    (1, 1, '2012-01-01 10:14:00', '2012-01-01 10:15:00'),
    (1, 1, '2012-01-01 10:15:00', '2012-01-02 10:16:00'), 
    (2, 2, '2012-01-01 10:12:00', '2012-01-01 10:13:00'), 
    (2, 2, '2012-01-01 10:14:00', '2012-01-01 10:15:00'), 
    (2, 2, '2012-01-01 10:15:00', '2012-01-02 10:16:00');

    Notice that we do not compute the duration in the table! 

    >> Can somebody please give me sample query for this requirement..<<

    Where is this requirement? You posted a picture and no specification! Does your boss make you guess at everything, too? 

    .. I have all these values in my temp table, so either I use cursor or call a function and use cursor inside it to calculate duration and time elapsed... or is there any other option on db side? << 

    Duration is easy; you use the DATEDIFF() function. It looks like you reset when (sln, magical_generic_id) changes. Let the front end do the temproal dispaly formatting. 


    SELECT sln, magical_generic_id, event_start_timestamp, event_end_timestamp,
           SUM (DATEDIFF (s, event_start_timestamp, event_end_timestamp))
                OVER (PARTITION BY sln, magical_generic_id
                        ORDER BY  event_start_timestamp
                      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
           AS duration_run_tot
      FROM Magic_Events;

    =====================================================================
    1 1 2012-01-01 10:12:00 2012-01-01 10:13:00 60
    1 1 2012-01-01 10:14:00 2012-01-01 10:15:00 120
    1 1 2012-01-01 10:15:00 2012-01-02 10:16:00 86580
    2 2 2012-01-01 10:12:00 2012-01-01 10:13:00 60
    2 2 2012-01-01 10:14:00 2012-01-01 10:15:00 120
    2 2 2012-01-01 10:15:00 2012-01-02 10:16:00 86580

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    • Proposed as answer by Kalman TothModerator Monday, March 04, 2013 10:03 AM
    • Marked as answer by StSingh Tuesday, March 05, 2013 12:48 AM
    Sunday, March 03, 2013 11:51 PM
  • So we just need to modify Narsimha's query so it will work on SQL 2008 (his query works but uses a feature that is only available on SQL 2012 or later - that is why I asked what version of SQL you are using and is an example of why it is good practice to always tell us what version you are using when asking a question.  The version that works on SQL 2008  would be

    DECLARE @tab TABLE(SLN INT,Id INT,StartDate DATETIME,EndDate DATETIME)
    INSERT INTO @tab
    VALUES(1,1,'1/1/2012 10:12','1/1/2012 10:13'),
    (1,1,'1/1/2012 10:14','1/1/2012 10:15'),
    (1,1,'1/1/2012 10:15','1/2/2012 10:16'),
    (2,2,'1/1/2012 10:12','1/1/2012 10:13'),
    (2,2,'1/1/2012 10:14','1/1/2012 10:15'),
    (2,2,'1/1/2012 10:15','1/2/2012 10:16')
    
    SELECT *
    FROM @tab
    
     ;WITH CTE AS
    (
    SELECT t.*, m.mn,
      (DATEDIFF(DAY,StartDate,EndDate))-(DATEDIFF(MONTH,StartDate,EndDate)*30) AS dys,
      (DATEDIFF(HOUR,StartDate,EndDate))-(DATEDIFF(DAY,StartDate,EndDate)*24) AS hrs,
      (DATEDIFF(MINUTE,StartDate,EndDate))-(DATEDIFF(HOUR,StartDate,EndDate)*60) AS mns,
      (DATEDIFF(SECOND,StartDate,EndDate))-(DATEDIFF(MINUTE,StartDate,EndDate)*60) AS scs,
      (DATEDIFF(DAY,mn,EndDate))-(DATEDIFF(MONTH,mn,EndDate)*30) AS Elpsd_dys,
      (DATEDIFF(HOUR,mn,EndDate))-(DATEDIFF(DAY,mn,EndDate)*24) as Elpsd_hrs,
      (DATEDIFF(MINUTE,mn,EndDate))-(DATEDIFF(HOUR,mn,EndDate)*60) AS Elpsd_mns,
      (DATEDIFF(SECOND,mn,EndDate))-(DATEDIFF(MINUTE,mn,EndDate)*60)AS Elpsd_scs
    FROM @tab t
    INNER JOIN (
      SELECT SLN,Id, MIN(StartDate) as mn
      FROM @tab
      GROUP BY SLN,Id
      ) m ON t.Id = m.id And t.SLN = m.SLN
    ) 
    SELECT cte.SLN
    ,cte.Id
    ,cte.StartDate
    ,cte.EndDate
    ,CAST(dys AS VARCHAR(10)) + ' day(s) '+
    CASE 
    	WHEN LEN(hrs)=1 
    	THEN '0'+CAST(hrs AS VARCHAR(10)) 
    	ELSE CAST(hrs AS VARCHAR(10)) 
    END +':'+
    CASE 
    	WHEN LEN(mns)=1 
    	THEN '0'+CAST(mns AS VARCHAR(10)) 
    	ELSE CAST(mns AS VARCHAR(10)) 
    END +':'+
    CASE 
    	WHEN LEN(scs)=1 
    	THEN '0'+CAST(scs AS VARCHAR(10)) 
    	ELSE CAST(scs AS VARCHAR(10)) 
    END AS Duration
    ,CAST(Elpsd_dys AS VARCHAR(10)) + ' day(s) '+
    CASE 
    	WHEN LEN(Elpsd_hrs)=1 
    	THEN '0'+CAST(Elpsd_hrs AS VARCHAR(10)) 
    	ELSE CAST(Elpsd_hrs AS VARCHAR(10)) 
    END +':'+
    CASE 
    	WHEN LEN(Elpsd_mns)=1 
    	THEN '0'+CAST(Elpsd_mns AS VARCHAR(10)) 
    	ELSE CAST(Elpsd_mns AS VARCHAR(10)) 
    END +':'+
    CASE 
    	WHEN LEN(Elpsd_scs)=1 
    	THEN '0'+CAST(Elpsd_scs AS VARCHAR(10)) 
    	ELSE CAST(Elpsd_scs AS VARCHAR(10)) 
    END AS TimeElaspsed
    FROM CTE

    Tom

    • Marked as answer by StSingh Tuesday, March 05, 2013 12:48 AM
    Monday, March 04, 2013 3:45 PM
  • The following will correct the negative times

    DECLARE @tab TABLE(SLN INT,Id INT,StartDate DATETIME,EndDate DATETIME)
    INSERT INTO @tab(SLN, Id, StartDate, EndDate)
    VALUES(1,1,'1/1/2012 10:12','1/1/2012 10:13'),
    (1,1,'1/1/2012 10:14','1/1/2012 10:15'),
    (1,1,'1/1/2012 10:15','1/2/2012 10:16'),
    (2,2,'1/1/2012 10:12','1/1/2012 10:13'),
    (2,2,'1/1/2012 10:14','1/1/2012 10:15'),
    (2,2,'1/1/2012 10:15','1/2/2012 10:16'),
    (1,            2,       '2012-01-17 08:44:50.677', '2012-01-17 08:44:54.280'),
    (1,                  2,       '2012-01-17 08:44:50.693', '2012-01-17 08:46:02.667'),
    (1,                  2,       '2012-01-17 08:46:02.517', '2012-01-17 08:50:09.683'),
    (1,                 2,       '2012-01-17 08:50:09.487', '2012-01-17 08:52:37.180'),
    (1,                  2,       '2012-01-17 09:03:53.303', '2012-01-17 09:04:30.367'),
    (1,                  2,       '2012-01-17 09:04:30.043', '2012-01-17 09:05:40.690');
    
    SELECT *
    FROM @tab
    
     ;WITH CteDays AS
    (
    SELECT t.SLN, t.Id, StartDate, EndDate, mnDay, 
       DateDiff(day, StartDate,EndDate) As DurationDays, 
       DateDiff(day, m.mnDay,EndDate) As ElaspedDays
    FROM @tab t
    INNER JOIN (
      SELECT SLN,Id, MIN(StartDate) as mnDay
      FROM @tab
      GROUP BY SLN,Id
      ) m ON t.Id = m.id And t.SLN = m.SLN
    ),
    cteTimes As
    (select SLN, Id, StartDate, EndDate, DurationDays, ElaspedDays,
      DateDiff(ms, DATEADD(day, DurationDays, StartDate), EndDate) As DTime, 
      DateDiff(ms, DATEADD(day, ElaspedDays, mnDay), EndDate) As ETime
      from CTEDays)
    Select SLN, Id, StartDate, EndDate, 
      Cast(DurationDays as varchar(11)) + ' day(s) ' + Convert(char(12), DateAdd(ms, Dtime, '20000101'), 114) As Duration, 
      Cast(ElaspedDays as varchar(11)) + ' day(s) ' + Convert(char(12), DateAdd(ms, ETime, '20000101'), 114) As TimeElasped 
    From cteTimes;
    

    Tom

    • Marked as answer by StSingh Thursday, March 07, 2013 9:30 AM
    Tuesday, March 05, 2013 3:05 AM

All replies

  • What release of SQL Server are you using?  And what is the datatype of StartDate and EndDate?

    Tom

    Sunday, March 03, 2013 9:47 PM

  • DECLARE @tab TABLE(SLN INT,Id INT,StartDate DATETIME,EndDate DATETIME)
    INSERT INTO @tab
    VALUES(1,1,'1/1/2012 10:12','1/1/2012 10:13'),
    (1,1,'1/1/2012 10:14','1/1/2012 10:15'),
    (1,1,'1/1/2012 10:15','1/2/2012 10:16'),
    (2,2,'1/1/2012 10:12','1/1/2012 10:13'),
    (2,2,'1/1/2012 10:14','1/1/2012 10:15'),
    (2,2,'1/1/2012 10:15','1/2/2012 10:16')
    
    SELECT *
    FROM @tab
    
     ;WITH CTE AS
    (
    SELECT *,
      (DATEDIFF(DAY,StartDate,EndDate))-(DATEDIFF(MONTH,StartDate,EndDate)*30) AS dys,
      (DATEDIFF(HOUR,StartDate,EndDate))-(DATEDIFF(DAY,StartDate,EndDate)*24) AS hrs,
      (DATEDIFF(MINUTE,StartDate,EndDate))-(DATEDIFF(HOUR,StartDate,EndDate)*60) AS mns,
      (DATEDIFF(SECOND,StartDate,EndDate))-(DATEDIFF(MINUTE,StartDate,EndDate)*60) AS scs,
      (DATEDIFF(DAY,mn,EndDate))-(DATEDIFF(MONTH,mn,EndDate)*30) AS Elpsd_dys,
      (DATEDIFF(HOUR,mn,EndDate))-(DATEDIFF(DAY,mn,EndDate)*24) as Elpsd_hrs,
      (DATEDIFF(MINUTE,mn,EndDate))-(DATEDIFF(HOUR,mn,EndDate)*60) AS Elpsd_mns,
      (DATEDIFF(SECOND,mn,EndDate))-(DATEDIFF(MINUTE,mn,EndDate)*60)AS Elpsd_scs
    FROM 
     (
      SELECT *,
    	MIN(StartDate) OVER(PARTITION BY SLN,Id ORDER BY SLN,Id) as mn
      FROM @tab
      )T
    )
    SELECT cte.SLN
    ,cte.Id
    ,cte.StartDate
    ,cte.EndDate
    ,CAST(dys AS VARCHAR(10)) + ' day(s) '+
    CASE 
    	WHEN LEN(hrs)=1 
    	THEN '0'+CAST(hrs AS VARCHAR(10)) 
    	ELSE CAST(hrs AS VARCHAR(10)) 
    END +':'+
    CASE 
    	WHEN LEN(mns)=1 
    	THEN '0'+CAST(mns AS VARCHAR(10)) 
    	ELSE CAST(mns AS VARCHAR(10)) 
    END +':'+
    CASE 
    	WHEN LEN(scs)=1 
    	THEN '0'+CAST(scs AS VARCHAR(10)) 
    	ELSE CAST(scs AS VARCHAR(10)) 
    END AS Duration
    ,CAST(Elpsd_dys AS VARCHAR(10)) + ' day(s) '+
    CASE 
    	WHEN LEN(Elpsd_hrs)=1 
    	THEN '0'+CAST(Elpsd_hrs AS VARCHAR(10)) 
    	ELSE CAST(Elpsd_hrs AS VARCHAR(10)) 
    END +':'+
    CASE 
    	WHEN LEN(Elpsd_mns)=1 
    	THEN '0'+CAST(Elpsd_mns AS VARCHAR(10)) 
    	ELSE CAST(Elpsd_mns AS VARCHAR(10)) 
    END +':'+
    CASE 
    	WHEN LEN(Elpsd_scs)=1 
    	THEN '0'+CAST(Elpsd_scs AS VARCHAR(10)) 
    	ELSE CAST(Elpsd_scs AS VARCHAR(10)) 
    END AS TimeElaspsed
    FROM CTE
    
    


    /*

    SLN    Id   StartDate                             EndDate                                    Duration               TimeElaspsed
    1        1    2012-01-01 10:12:00.000    2012-01-01 10:13:00.000    0 day(s) 00:01:00    0 day(s) 00:01:00
    1        1    2012-01-01 10:14:00.000    2012-01-01 10:15:00.000    0 day(s) 00:01:00    0 day(s) 00:03:00
    1        1    2012-01-01 10:15:00.000    2012-01-02 10:16:00.000    1 day(s) 00:01:00    1 day(s) 00:04:00
    2        2    2012-01-01 10:12:00.000    2012-01-01 10:13:00.000    0 day(s) 00:01:00    0 day(s) 00:01:00
    2        2    2012-01-01 10:14:00.000    2012-01-01 10:15:00.000    0 day(s) 00:01:00    0 day(s) 00:03:00
    2        2    2012-01-01 10:15:00.000    2012-01-02 10:16:00.000    1 day(s) 00:01:00    1 day(s) 00:04:00

    */



    Narsimha

    • Proposed as answer by Kalman TothModerator Monday, March 04, 2013 10:02 AM
    • Marked as answer by StSingh Tuesday, March 05, 2013 12:48 AM
    Sunday, March 03, 2013 11:10 PM
  • You did not bother to tell us the name of the table, and you expect us to help you. That is both rude and silly. Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats – you really screwed up on that! Code should be in Standard SQL as much as possible and not local dialect. We have a DATETIME2(0) data type today 

    This is minimal polite behavior on SQL forums. Now we have to guess at everything for you in order to do your job or homework for you. What you did post has not obvious key and the design flaw called “attribute splitting” where you broke the single fact of a timestamp into multiple columns. This violates First Normal Form (1NF). 

    CREATE TABLE Magic_Events
    (sln INTEGER NOT NULL,
     magical_generic_id INTEGER NOT NULL,
     event_start_timestamp DATETIME2(0) NOT NULL PRIMARY KEY, -- wild guess! 
     event_end_timestamp DATETIME2(0) NOT NULL,
     CHECK (event_start_timestamp < event_end_timestamp)

    INSERT INTO Magic_Events
    VALUES
    (1, 1, '2012-01-01 10:12:00', '2012-01-01 10:13:00'),
    (1, 1, '2012-01-01 10:14:00', '2012-01-01 10:15:00'),
    (1, 1, '2012-01-01 10:15:00', '2012-01-02 10:16:00'), 
    (2, 2, '2012-01-01 10:12:00', '2012-01-01 10:13:00'), 
    (2, 2, '2012-01-01 10:14:00', '2012-01-01 10:15:00'), 
    (2, 2, '2012-01-01 10:15:00', '2012-01-02 10:16:00');

    Notice that we do not compute the duration in the table! 

    >> Can somebody please give me sample query for this requirement..<<

    Where is this requirement? You posted a picture and no specification! Does your boss make you guess at everything, too? 

    .. I have all these values in my temp table, so either I use cursor or call a function and use cursor inside it to calculate duration and time elapsed... or is there any other option on db side? << 

    Duration is easy; you use the DATEDIFF() function. It looks like you reset when (sln, magical_generic_id) changes. Let the front end do the temproal dispaly formatting. 


    SELECT sln, magical_generic_id, event_start_timestamp, event_end_timestamp,
           SUM (DATEDIFF (s, event_start_timestamp, event_end_timestamp))
                OVER (PARTITION BY sln, magical_generic_id
                        ORDER BY  event_start_timestamp
                      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
           AS duration_run_tot
      FROM Magic_Events;

    =====================================================================
    1 1 2012-01-01 10:12:00 2012-01-01 10:13:00 60
    1 1 2012-01-01 10:14:00 2012-01-01 10:15:00 120
    1 1 2012-01-01 10:15:00 2012-01-02 10:16:00 86580
    2 2 2012-01-01 10:12:00 2012-01-01 10:13:00 60
    2 2 2012-01-01 10:14:00 2012-01-01 10:15:00 120
    2 2 2012-01-01 10:15:00 2012-01-02 10:16:00 86580

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    • Proposed as answer by Kalman TothModerator Monday, March 04, 2013 10:03 AM
    • Marked as answer by StSingh Tuesday, March 05, 2013 12:48 AM
    Sunday, March 03, 2013 11:51 PM
  • Hello Naarasimha, Tom and Celko,

    Thanks for your reply and pointing out the mistakes I did.

    Requirement: I had to calculate duration and TimeStamp based on the values in my StartDate(dateTime) and EndDate(dateTime). These values are in my temp table and I have not created any primary Key on this bz (sln, OId, StartDate and EndDate) can be repetitive for different SLN, OID, Start & End date.

    Duration: days(if greater than 24 hrs) + (if this difference is greater than 24 hours) ":" + EndDate - StartDate

    Elapsed: days (1st Rowwise EndDate - Startdate) -Idea is to get total time taken to process the SLN and Orders. This running total has to reset after 3rd Row, so that I get to know the total time taken to process this.

    I will be using SQL Server 2008

    Thanks for the queries you all have posted, I shall try it and let you know the results and the modification I might make. 

    Thanks,

    Swat



    • Edited by StSingh Monday, March 04, 2013 8:33 AM
    Monday, March 04, 2013 8:23 AM
  • Dear Celko,

    i'm aware of your deep knowledge about SQL Server, ISO, ... but - with respect..

    When I read your posts regarding a request from the thread starter I cannot believe that someone like you will use such a choice of words which may frighten away people from the forum instead of looking for help with their problems. I believe they don't look for scientific essays about ISO and DDL.

    I've read a very interesting statement from Paul Randall according this - generic - problem on his blog.

    "... So next time you see someone asking a question that you think is so simple that they should know the answer, or that *everyone* knows you shouldn't do X or Y, cut them some slack and educate them nicely. Empathize. Don't belittle them. Don't rail on them. And don't equate ignorance with stupidity."

    http://www.sqlskills.com/blogs/paul/ignorance-is-not-stupidity

    Once again i have deep respect concerning your knowledge about sql and it's scientific basics but keep in mind that you've started your career with the same knowledge and your way on the stairways to sql have started from the floor, too ;)


    Uwe Ricken

    MCSE - SQL Server 2012
    MCSA - SQL Server 2012
    MCITP Database Administrator 2005
    MCITP Database Administrator 2008
    MCITP Microsoft SQL Server 2008, Database Development

    db Berater GmbH
    http://www-db-berater.de
    SQL Server Blog (german only)


    Monday, March 04, 2013 9:49 AM
  • So we just need to modify Narsimha's query so it will work on SQL 2008 (his query works but uses a feature that is only available on SQL 2012 or later - that is why I asked what version of SQL you are using and is an example of why it is good practice to always tell us what version you are using when asking a question.  The version that works on SQL 2008  would be

    DECLARE @tab TABLE(SLN INT,Id INT,StartDate DATETIME,EndDate DATETIME)
    INSERT INTO @tab
    VALUES(1,1,'1/1/2012 10:12','1/1/2012 10:13'),
    (1,1,'1/1/2012 10:14','1/1/2012 10:15'),
    (1,1,'1/1/2012 10:15','1/2/2012 10:16'),
    (2,2,'1/1/2012 10:12','1/1/2012 10:13'),
    (2,2,'1/1/2012 10:14','1/1/2012 10:15'),
    (2,2,'1/1/2012 10:15','1/2/2012 10:16')
    
    SELECT *
    FROM @tab
    
     ;WITH CTE AS
    (
    SELECT t.*, m.mn,
      (DATEDIFF(DAY,StartDate,EndDate))-(DATEDIFF(MONTH,StartDate,EndDate)*30) AS dys,
      (DATEDIFF(HOUR,StartDate,EndDate))-(DATEDIFF(DAY,StartDate,EndDate)*24) AS hrs,
      (DATEDIFF(MINUTE,StartDate,EndDate))-(DATEDIFF(HOUR,StartDate,EndDate)*60) AS mns,
      (DATEDIFF(SECOND,StartDate,EndDate))-(DATEDIFF(MINUTE,StartDate,EndDate)*60) AS scs,
      (DATEDIFF(DAY,mn,EndDate))-(DATEDIFF(MONTH,mn,EndDate)*30) AS Elpsd_dys,
      (DATEDIFF(HOUR,mn,EndDate))-(DATEDIFF(DAY,mn,EndDate)*24) as Elpsd_hrs,
      (DATEDIFF(MINUTE,mn,EndDate))-(DATEDIFF(HOUR,mn,EndDate)*60) AS Elpsd_mns,
      (DATEDIFF(SECOND,mn,EndDate))-(DATEDIFF(MINUTE,mn,EndDate)*60)AS Elpsd_scs
    FROM @tab t
    INNER JOIN (
      SELECT SLN,Id, MIN(StartDate) as mn
      FROM @tab
      GROUP BY SLN,Id
      ) m ON t.Id = m.id And t.SLN = m.SLN
    ) 
    SELECT cte.SLN
    ,cte.Id
    ,cte.StartDate
    ,cte.EndDate
    ,CAST(dys AS VARCHAR(10)) + ' day(s) '+
    CASE 
    	WHEN LEN(hrs)=1 
    	THEN '0'+CAST(hrs AS VARCHAR(10)) 
    	ELSE CAST(hrs AS VARCHAR(10)) 
    END +':'+
    CASE 
    	WHEN LEN(mns)=1 
    	THEN '0'+CAST(mns AS VARCHAR(10)) 
    	ELSE CAST(mns AS VARCHAR(10)) 
    END +':'+
    CASE 
    	WHEN LEN(scs)=1 
    	THEN '0'+CAST(scs AS VARCHAR(10)) 
    	ELSE CAST(scs AS VARCHAR(10)) 
    END AS Duration
    ,CAST(Elpsd_dys AS VARCHAR(10)) + ' day(s) '+
    CASE 
    	WHEN LEN(Elpsd_hrs)=1 
    	THEN '0'+CAST(Elpsd_hrs AS VARCHAR(10)) 
    	ELSE CAST(Elpsd_hrs AS VARCHAR(10)) 
    END +':'+
    CASE 
    	WHEN LEN(Elpsd_mns)=1 
    	THEN '0'+CAST(Elpsd_mns AS VARCHAR(10)) 
    	ELSE CAST(Elpsd_mns AS VARCHAR(10)) 
    END +':'+
    CASE 
    	WHEN LEN(Elpsd_scs)=1 
    	THEN '0'+CAST(Elpsd_scs AS VARCHAR(10)) 
    	ELSE CAST(Elpsd_scs AS VARCHAR(10)) 
    END AS TimeElaspsed
    FROM CTE

    Tom

    • Marked as answer by StSingh Tuesday, March 05, 2013 12:48 AM
    Monday, March 04, 2013 3:45 PM
  • I truly understand accept that I should have give more (most) information about my requirement 1st hand, trust me it happened unknowingly.

    Many thanks for your responses.

    When I tried to execute the query it give me negative results as posted below. I believe DateDiff cannot be applied directly. 

    SLN             OrderID        ArrivalDate              ProcessedDate              Duration      TimeElaspsed
    1                  2       2012-01-17 08:44:50.677 2012-01-17 08:44:54.280 0 day(s) 00:00:04  0 day(s) 00:00:04
    1                  2       2012-01-17 08:44:50.693 2012-01-17 08:46:02.667 0 day(s) 00:02:-48  0 day(s) 00:02:-48
    1                  2       2012-01-17 08:46:02.517 2012-01-17 08:50:09.683 0 day(s) 00:04:07  0 day(s) 00:06:-41
    1                  2       2012-01-17 08:50:09.487 2012-01-17 08:52:37.180 0 day(s) 00:02:28  0 day(s) 00:08:-13
    1                  2       2012-01-17 09:03:53.303 2012-01-17 09:04:30.367 0 day(s) 00:01:-23  0 day(s) 01:-40:-20
    1                  2       2012-01-17 09:04:30.043 2012-01-17 09:05:40.690 0 day(s) 00:01:10  0 day(s) 01:-39:-10
    

    Tuesday, March 05, 2013 12:46 AM
  • The following will correct the negative times

    DECLARE @tab TABLE(SLN INT,Id INT,StartDate DATETIME,EndDate DATETIME)
    INSERT INTO @tab(SLN, Id, StartDate, EndDate)
    VALUES(1,1,'1/1/2012 10:12','1/1/2012 10:13'),
    (1,1,'1/1/2012 10:14','1/1/2012 10:15'),
    (1,1,'1/1/2012 10:15','1/2/2012 10:16'),
    (2,2,'1/1/2012 10:12','1/1/2012 10:13'),
    (2,2,'1/1/2012 10:14','1/1/2012 10:15'),
    (2,2,'1/1/2012 10:15','1/2/2012 10:16'),
    (1,            2,       '2012-01-17 08:44:50.677', '2012-01-17 08:44:54.280'),
    (1,                  2,       '2012-01-17 08:44:50.693', '2012-01-17 08:46:02.667'),
    (1,                  2,       '2012-01-17 08:46:02.517', '2012-01-17 08:50:09.683'),
    (1,                 2,       '2012-01-17 08:50:09.487', '2012-01-17 08:52:37.180'),
    (1,                  2,       '2012-01-17 09:03:53.303', '2012-01-17 09:04:30.367'),
    (1,                  2,       '2012-01-17 09:04:30.043', '2012-01-17 09:05:40.690');
    
    SELECT *
    FROM @tab
    
     ;WITH CteDays AS
    (
    SELECT t.SLN, t.Id, StartDate, EndDate, mnDay, 
       DateDiff(day, StartDate,EndDate) As DurationDays, 
       DateDiff(day, m.mnDay,EndDate) As ElaspedDays
    FROM @tab t
    INNER JOIN (
      SELECT SLN,Id, MIN(StartDate) as mnDay
      FROM @tab
      GROUP BY SLN,Id
      ) m ON t.Id = m.id And t.SLN = m.SLN
    ),
    cteTimes As
    (select SLN, Id, StartDate, EndDate, DurationDays, ElaspedDays,
      DateDiff(ms, DATEADD(day, DurationDays, StartDate), EndDate) As DTime, 
      DateDiff(ms, DATEADD(day, ElaspedDays, mnDay), EndDate) As ETime
      from CTEDays)
    Select SLN, Id, StartDate, EndDate, 
      Cast(DurationDays as varchar(11)) + ' day(s) ' + Convert(char(12), DateAdd(ms, Dtime, '20000101'), 114) As Duration, 
      Cast(ElaspedDays as varchar(11)) + ' day(s) ' + Convert(char(12), DateAdd(ms, ETime, '20000101'), 114) As TimeElasped 
    From cteTimes;
    

    Tom

    • Marked as answer by StSingh Thursday, March 07, 2013 9:30 AM
    Tuesday, March 05, 2013 3:05 AM