split single row into four rows.

Question

split single row into four rows.

abhas

SSCarpal Tunnel

Points: 4681
More actions
April 9, 2013 at 12:29 am

#276184

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.

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

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

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

matak SSCrazy Points: 2674 More actions · Answer 2

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

abhas SSCarpal Tunnel Points: 4681 More actions · Answer 3

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.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

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

matak SSCrazy Points: 2674 More actions · Answer 5

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

matak SSCrazy Points: 2674 More actions · Answer 6

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

The code will need to change if they are different.

abhas SSCarpal Tunnel Points: 4681 More actions · Answer 7

Hi Matak/Chris,

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

Thanks

Abhas.

abhas SSCarpal Tunnel Points: 4681 More actions · Answer 8

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.

matak SSCrazy Points: 2674 More actions · Answer 9

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

N is the column name in mine

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

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

abhas SSCarpal Tunnel Points: 4681 More actions · Answer 11

Hi,

I tried but not able to insert data. 🙁

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

Please help.

thanks

Abhas.

matak SSCrazy Points: 2674 More actions · Answer 12

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.

abhas SSCarpal Tunnel Points: 4681 More actions · Answer 13

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.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 14

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