Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2007
    Posts
    1

    Unanswered: Split one row into multiple rows based on time elements

    I'm dealing with a problem.

    The record information example

    DateTimeStart , DateTimeEnd , action , duration (seconds)
    2007-02-02 10:30:22 , 2007-02-02 11:30:22 action1 , 600

    what i want is for every half hour between start and end a record

    10.30 action1
    11.00 action1
    11.30 action1

    how can i create this, i'm a little stuck on this

  2. #2
    Join Date
    Feb 2007
    Posts
    38
    It all depends on datestart and dateend and action.
    If this is for only one day, then create a temp table as below:

    Create table tmpTime (
    stdTime datetime null,
    [action] varchar(100) null
    )

    Then insert the time for the day.
    For example:
    ...
    Insert tmpTime Values ('2007-02-02 09:00', '')
    Insert tmpTime Values ('2007-02-02 09:30', '')
    Insert tmpTime Values ('2007-02-02 10:00', '')
    Insert tmpTime Values ('2007-02-02 10:30', '')
    Insert tmpTime Values ('2007-02-02 11:00', '')
    Insert tmpTime Values ('2007-02-02 11:30', '')
    Insert tmpTime Values ('2007-02-02 12:00', '')
    Insert tmpTime Values ('2007-02-02 12:30', '')
    ..

    And I am assuming that your data table as below:
    Create table tmpSourceTime (
    DateTimeStart datetime null,
    DateTimeEnd datetime null,
    [action] varchar(100) null,
    duration Int null
    )

    I am populating data into the table:
    Insert tmpSourceTime
    Values (
    '2007-02-02 09:30:21', '2007-02-02 10:30:21','action2' , 600)
    Insert tmpSourceTime
    Values (
    '2007-02-02 10:30:22', '2007-02-02 11:30:22','action1' , 600)

    Now use the following UPDATE command:
    update a
    Set a.[action]=b.[action]
    FROM tmpTime a Join tmpSourceTime b
    On a.stdTime between b.DateTimeStart and b.DateTimeEnd

    You will see the result:
    select * from tmpTime

  3. #3
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Code:
    select a.halfhour ,b.action 
    from 
    (select halfhour=convert(char(8),dateadd(mi,(a.colid-1)*30,0),8) 
     from master..syscomments a
     where a.id=object_id('master..sp_instdist') and a.colid<=48
    ) a -- any table with sequential numbers 1..48
    ,YourTable b
    where a.halfhour  
          between convert(char(8),dateadd(mi,-29,b.DateTimeStart),8)
              and convert(char(8),b.DateTimeEnd,8)

Posting Permissions

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