Results 1 to 12 of 12
  1. #1
    Join Date
    Oct 2003
    Location
    now in UAE, i am an INDIAN.
    Posts
    16

    Unanswered: Subtract value from next record

    Hi,
    I am trying to solve the following issue, can anybody throw some lights...
    Here is some sample data from the table
    TIME STAMP PROJECTID FUNCTION
    2007-10-31-01.10.05.00 3333311111 First
    2007-10-31-01.10.06.00 3333311111 Second
    2007-10-31-01.10.08.00 3333311111 Third
    2007-10-31-01.10.10.00 3333311111 Complete
    2007-10-31-01.10.11.00 2222244444 First
    2007-10-31-01.10.12.00 2222244444 Second
    2007-10-31-01.10.14.00 2222244444 Third
    2007-10-31-01.10.15.00 2222244444 Complete
    ... and result I am looking something like below
    PROJECT ID FUNCTION DURATION
    3333311111 First 0:00:01 (2007-10-31-01.10.06.00 - 2007-10-31-01.10.05.00)
    3333311111 Second 0:00:06 (2007-10-31-01.10.08.00 - 2007-10-31-01.10.06.00)
    3333311111 Third 0:00:02 (2007-10-31-01.10.10.00 - 2007-10-31-01.10.08.00)
    2222244444 First 0:00:01 (2007-10-31-01.10.12.00 - 2007-10-31-01.10.11.00)
    2222244444 Second 0:00:02 (2007-10-31-01.10.14.00 - 2007-10-31-01.10.12.00)
    2222244444 Third 0:00:01 (2007-10-31-01.10.15.00 - 2007-10-31-01.10.14.00)

    hope the question is clear!
    thanks for your time,
    Murali
    TEAM (Together Everybody Achieve More)

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    with rownumbers as ( 
    select timestamp
         , projectid
         , funktion
         , row_number() 
              over(partition by projectid 
                   order by timestamp) as r
      from table1 ) 
    select r1.projectid
         , r1.funktion
         , datediff(s,r1.timestamp,r2.timestamp) as duration
      from rownumbers as r1
    inner
      join rownumbers as r2
        on r2.projectid = r1.projectid
       and r2.r = r1.r + 1
    
    projectid   funktion  duration
    2222244444  First        1
    2222244444  Second       2
    2222244444  Third        1
    3333311111  First        1
    3333311111  Second       2
    3333311111  Third        2
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2003
    Location
    now in UAE, i am an INDIAN.
    Posts
    16

    Getting error

    Thank you r937,

    I am getting following error message while running the code you provided:

    Server: Msg 156, Level 15, State 1, Line 1
    Incorrect syntax near the keyword 'with'.
    Server: Msg 195, Level 15, State 1, Line 5
    'row_number' is not a recognized function name.


    regards,
    Murali.
    TEAM (Together Everybody Achieve More)

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sorry

    you should have said you were on sql server 2000

    my solution is for sql server 2005
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    --  ptp  20071105  See http://www.dbforums.com/showthread.php?t=1624041
    
    CREATE TABLE #dbforums (		-- Create the empty schema
       [TIME STAMP]		DATETIME
    ,  [PROJECTID]		VARCHAR(20)
    ,  [FUNCTION]		VARCHAR(20)
       )
    
    INSERT INTO #dbforums			-- Load the test data
       SELECT           '2007-10-31 01:10:05.00', '3333311111', 'First'
       UNION ALL SELECT '2007-10-31 01:10:06.00', '3333311111', 'Second'
       UNION ALL SELECT '2007-10-31 01:10:08.00', '3333311111', 'Third'
       UNION ALL SELECT '2007-10-31 01:10:10.00', '3333311111', 'Complete'
       UNION ALL SELECT '2007-10-31 01:10:11.00', '2222244444', 'First'
       UNION ALL SELECT '2007-10-31 01:10:12.00', '2222244444', 'Second'
       UNION ALL SELECT '2007-10-31 01:10:14.00', '2222244444', 'Third'
       UNION ALL SELECT '2007-10-31 01:10:15.00', '2222244444', 'Complete'
    
    SELECT					-- Generate the results
       c.[PROJECTID], c.[FUNCTION], n.[TIME STAMP], c.[TIME STAMP]
       FROM #dbforums AS c
       INNER JOIN #dbforums AS n
          ON (n.[PROJECTID] = c.[PROJECTID]
          AND n.[TIME STAMP] = (SELECT Min([TIME STAMP])
             FROM #dbforums AS z
             WHERE  z.[PROJECTID] = c.[PROJECTID]
                AND c.[TIME STAMP] < z.[TIME STAMP]))
    
    DROP TABLE #dbforums			-- Clean up when we're done
    -PatP

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i figured it would be you pat

    i knew somebody was going to do it, i left a big enough hole...

    nice job

    one too many table aliases for my liking, but nevermind...

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm just that kind of guy, doncha know?

    There might be a way to do it in the SQL 2000 environment with fewer aliases, but this works and I'm too lazy to whittle it down without some input from the original poster saying that they need fewer!

    -PatP

  8. #8
    Join Date
    Oct 2003
    Location
    now in UAE, i am an INDIAN.
    Posts
    16
    Thank you PatP for your quick reply,
    I have 2 queries

    1. The original table which I am going to use is huge, it is a log table, contains thousands of records and growing rapidly, Do you feel this code generate any performace issue?

    2. I need to check this data against a holiday table and find the actual time taken for each function (means i have exclude the holiday time) how can i ?

    thanks and regards
    Murali.
    TEAM (Together Everybody Achieve More)

  9. #9
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Quote Originally Posted by ipmurali
    i have exclude the holiday time
    Does that mean you never have functions during a holiday in the UAE i.e. a function can never start or end on a holiday (no overtime)?

  10. #10
    Join Date
    Oct 2003
    Location
    now in UAE, i am an INDIAN.
    Posts
    16
    Normally there will not be any functions on holidays, some exemptions may be there, in that case the holiday table will be updated.
    Last edited by ipmurali; 11-06-07 at 05:25.
    TEAM (Together Everybody Achieve More)

  11. #11
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    What does the holiday table look like
    And how about non working hours?
    e.g. before 9 AM and after 5 PM and lunch break
    Last edited by pdreyer; 11-06-07 at 06:53.

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by ipmurali
    1. The original table which I am going to use is huge, it is a log table, contains thousands of records and growing rapidly, Do you feel this code generate any performace issue?

    2. I need to check this data against a holiday table and find the actual time taken for each function (means i have exclude the holiday time) how can i ?
    1. Depending on your configuration and your performance requirements, this might or might not be a performance problem.

    2. Depending on how you need to account for time spent on holidays, and how you structured your holiday table, this could be as simple as a subtraction or as complex as a UDF (User Defined Function).

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •