split single row into four rows.

  • Hi All,

    I am having a table which contains 4 field as below.

    stuid,studname,startdate,starttime,endtime.

    now if starttime is 7am and endtime is 8am then i want to display data in the same table in the interval of 15 mins.

    example if the user select 7am as starttime and 9am as endtime then 8 rows wolud be populated as below.

    Studentid Studentname effectivedatestarttimeendtime

    1 john 2012/23/037am7.15am

    1 john 2012/23/037.15am7.30am

    1 john 2012/23/037.30am7.45am

    1 john 2012/23/037.45am8am

    1 john 2012/23/038am8.15am

    1 john 2012/23/038.15am8.30am

    1 john 2012/23/038.30am8.45am

    1 john 2012/23/038.45am9am

    Thanks

    Abhas.

  • Can you post ddl and dml for your source table please? If you're not sure how to do this, have a read of the link in my sig.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • Something like this ?

    IF OBJECT_ID('TempDB..#test','U') IS NOT NULL

    drop table #test

    create table #test

    (studentid int, studentname varchar(20), startdate datetime, starttime datetime, endtime datetime

    )

    insert into #test

    select 1, 'john', '2012-01-03 00:00:00', '2012-01-03 07:00:00', '2012-01-03 09:00:00'

    select studentid, studentname, startdate, DATEADD(mi, (N-1)*15, starttime), DATEADD(mi, N*15, starttime)

    from #test cross join Tally

    where n >= 1 and n <= datediff(mi, starttime, endtime)/15

  • Thanks matak.

    I will try this.But my actual requirement is to insert this data into table after selecting from front end dropdown. for example if user selects date like 2012/03/23 and select start time as 7am and end time as 8am then i want to insert this by splitting into four rows.

    Thanks

    Abhas.

  • abhas (4/9/2013)


    Thanks matak.

    I will try this.But my actual requirement is to insert this data into table after selecting from front end dropdown. for example if user selects date like 2012/03/23 and select start time as 7am and end time as 8am then i want to insert this by splitting into four rows.

    Thanks

    Abhas.

    Assuming you want to pass the date and the two times into SQL Server, what datatype are these three variables?

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • That should be fine.

    Try this. Just uncomment the insert into statement and replace with your actual table

    declare @studentid as datetime = '2012-01-03 00:00:00'

    declare @studentname as varchar(20) = 'john'

    declare @startdate as datetime = '2012-01-03 00:00:00'

    declare @starttime as datetime = '2012-01-03 07:00:00'

    declare @endtime as datetime = '2012-01-03 09:00:00'

    --insert into tablename

    select @studentid, @studentname, @startdate, DATEADD(mi, (N-1)*15, @starttime), DATEADD(mi, N*15, @starttime)

    from Tally

    where n >= 1 and n <= datediff(mi, @starttime, @endtime)/15

  • Keep in mind what Chris says - im assuming they are all datetime.

    The code will need to change if they are different.

  • Hi Matak/Chris,

    I want to use starttime and endtime as varchar. is it possible?

    Thanks

    Abhas.

  • Hi Matak,

    Could you please let me know the purpose of DATEADD(mi, (N-1)*15 as i am getting below error in your script.

    Msg 207, Level 16, State 1, Line 11

    Invalid column name 'n'.

    Msg 207, Level 16, State 1, Line 11

    Invalid column name 'n'.

    Msg 207, Level 16, State 1, Line 9

    Invalid column name 'N'.

    Msg 207, Level 16, State 1, Line 9

    Invalid column name 'N'.

    Thanks

    Abhas.

  • Do a search for tally table on the forums - you need one.

    N is the column name in mine

  • abhas (4/9/2013)


    Hi Matak/Chris,

    I want to use starttime and endtime as varchar. is it possible?

    Thanks

    Abhas.

    Yes:

    DECLARE @STARTDATE DATETIME, @StartTimeChar CHAR(5), @EndTimeChar CHAR(5)

    SELECT

    @STARTDATE = CAST(GETDATE() AS DATE),

    @StartTimeChar = '08:00',

    @EndTimeChar = '10:00'

    SELECT TOP(1+DATEDIFF(MINUTE,@StartTimeChar,@EndTimeChar)/15) -- number of rows to collect

    DATEADD(minute,((ROW_NUMBER() OVER(ORDER BY (SELECT NULL))-1)*15),StartDateTime)

    FROM sys.columns -- row source; could use tally table

    CROSS APPLY (

    SELECT StartDateTime =

    DATEADD(hour,CAST(LEFT(@StartTimeChar,2) AS INT),@STARTDATE)

    ) x

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • Hi,

    I tried but not able to insert data. 🙁

    getting the same error. Do i need to create tally table?

    Please help.

    thanks

    Abhas.

  • Personally i wouldnt live without one anymore.

    It has many more uses than what i showed.

    The solution Chris provided doesnt use a tally table so if you dont want one then its not required for this problem.

  • Hi,

    Still facing an issue while inserting records. I have created Tally table with N. If i use select by applying join then data is showing properly but while inserting giving an error. Could you pleas help?

    Thanks

    Abhas.

  • abhas (4/9/2013)


    Hi,

    Still facing an issue while inserting records. I have created Tally table with N. If i use select by applying join then data is showing properly but while inserting giving an error. Could you pleas help?

    Thanks

    Abhas.

    Can you post your code? It's impossible to tell what may be going wrong without seeing the code and the error message.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Viewing 15 posts - 1 through 15 (of 21 total)

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