SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


split single row into four rows.


split single row into four rows.

Author
Message
ChrisM@Work
ChrisM@Work
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39731 Visits: 19995
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
Exploring Recursive CTEs by Example Dwain Camps
abhas
abhas
SSC Eights!
SSC Eights! (920 reputation)SSC Eights! (920 reputation)SSC Eights! (920 reputation)SSC Eights! (920 reputation)SSC Eights! (920 reputation)SSC Eights! (920 reputation)SSC Eights! (920 reputation)SSC Eights! (920 reputation)

Group: General Forum Members
Points: 920 Visits: 664
Hi,

I tried but not able to insert data. Sad

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

Please help.

thanks
Abhas.
matak
matak
Right there with Babe
Right there with Babe (769 reputation)Right there with Babe (769 reputation)Right there with Babe (769 reputation)Right there with Babe (769 reputation)Right there with Babe (769 reputation)Right there with Babe (769 reputation)Right there with Babe (769 reputation)Right there with Babe (769 reputation)

Group: General Forum Members
Points: 769 Visits: 4263
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
abhas
SSC Eights!
SSC Eights! (920 reputation)SSC Eights! (920 reputation)SSC Eights! (920 reputation)SSC Eights! (920 reputation)SSC Eights! (920 reputation)SSC Eights! (920 reputation)SSC Eights! (920 reputation)SSC Eights! (920 reputation)

Group: General Forum Members
Points: 920 Visits: 664
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
ChrisM@Work
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39731 Visits: 19995
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
Exploring Recursive CTEs by Example Dwain Camps
ChrisM@Work
ChrisM@Work
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39731 Visits: 19995
This might help:


IF object_id('TempDB..#Temp') IS NOT NULL
DROP TABLE #Temp

CREATE TABLE #Temp (DateTimeRow DATETIME)

DECLARE @STARTDATE DATETIME, @StartTimeChar CHAR(5), @EndTimeChar CHAR(5);
SELECT
@STARTDATE = CAST(GETDATE() AS DATE),
@StartTimeChar = '08:00', -- note left-padded digit
@EndTimeChar = '10:00';

-- inline tally table CTE
WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1), -- 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), -- 10*10 = 100 rows
iTally(N) AS (SELECT 1 FROM E2 a, E2 b) -- 100*100 = 10000 rows max

INSERT INTO #Temp
(DateTimeRow)
SELECT TOP(1+DATEDIFF(minute,@StartTimeChar,@EndTimeChar)/15) -- number of rows to collect
DateTimeRow = DATEADD(minute,((ROW_NUMBER() OVER(ORDER BY (SELECT NULL))-1)*15),StartDateTime)
FROM iTally -- row source - inline tally table
CROSS APPLY (
SELECT StartDateTime =
DATEADD(hour,CAST(LEFT(@StartTimeChar,2) AS INT),@STARTDATE)
) x;

SELECT * FROM #Temp;



“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
Exploring Recursive CTEs by Example Dwain Camps
abhas
abhas
SSC Eights!
SSC Eights! (920 reputation)SSC Eights! (920 reputation)SSC Eights! (920 reputation)SSC Eights! (920 reputation)SSC Eights! (920 reputation)SSC Eights! (920 reputation)SSC Eights! (920 reputation)SSC Eights! (920 reputation)

Group: General Forum Members
Points: 920 Visits: 664
Thank you Chris.

I found below code some where else which is resemble to my requirement. Now i want to insert whole result set into my database table. Smile




DECLARE @t TABLE
(OfficeId INT, WeekdayId INT, StartTime TIME, EndTime TIME)

INSERT INTO @t
SELECT 1, 1001, '14:30', '16:30'


;WITH Tally (n) AS (
SELECT TOP 100 15*(ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1)
FROM sys.all_columns)
SELECT OfficeID, WeekdayID
,TSStart=DATEADD(minute, n, StartTime)
,TSEnd=DATEADD(minute, n + 15, StartTime)
,Timeslot=CONVERT(VARCHAR(100), DATEADD(minute, n, StartTime), 0) + ' - ' +
CONVERT(VARCHAR(100), DATEADD(minute, n + 15, StartTime), 0)
FROM @t
CROSS APPLY (
SELECT n
FROM Tally
WHERE n BETWEEN 0 AND DATEDIFF(minute, StartTime, DATEADD(minute, -15, EndTime))) a
ORDER BY OfficeID, WeekdayID, TSStart


Thanks
Abhas
matak
matak
Right there with Babe
Right there with Babe (769 reputation)Right there with Babe (769 reputation)Right there with Babe (769 reputation)Right there with Babe (769 reputation)Right there with Babe (769 reputation)Right there with Babe (769 reputation)Right there with Babe (769 reputation)Right there with Babe (769 reputation)

Group: General Forum Members
Points: 769 Visits: 4263
Im not sure why you went searching for other code when you had some that worked as you wanted.
Unfortunately its very hard to help since you still have not given us any DDL.
We cant see what you see.
abhas
abhas
SSC Eights!
SSC Eights! (920 reputation)SSC Eights! (920 reputation)SSC Eights! (920 reputation)SSC Eights! (920 reputation)SSC Eights! (920 reputation)SSC Eights! (920 reputation)SSC Eights! (920 reputation)SSC Eights! (920 reputation)

Group: General Forum Members
Points: 920 Visits: 664
Thanks Matak,

PFB ddl. the requirement is resemble with the code which i posted. Starttime and endtime will select by front end and i want to add in the back end in 15 mins slot that is four slot per hour. thats it.

USE [ReportsDev]
GO

/****** Object: Table [dbo].[tblPlaner] Script Date: 04/09/2013 21:56:21 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[tblSuperviosr](
[PlanerID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
[AdvisorID] [int] NOT NULL,
[StartDate] [date] NULL,
[StartTime] [time](7) NULL,
[EndTime] [time](7) NULL,
[Flag] [bit] NULL,
CONSTRAINT [PK_tblSuperviosr] PRIMARY KEY CLUSTERED
(
[PlanerID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
matak
matak
Right there with Babe
Right there with Babe (769 reputation)Right there with Babe (769 reputation)Right there with Babe (769 reputation)Right there with Babe (769 reputation)Right there with Babe (769 reputation)Right there with Babe (769 reputation)Right there with Babe (769 reputation)Right there with Babe (769 reputation)

Group: General Forum Members
Points: 769 Visits: 4263
Unless there are some other surprises this should do it.
Ive changed the definition for your table variable and im assuming that information is readily available to you.

I would still recommend you to do a little reading on Tally tables - look for an article by Jeff Moden on this site.
I find it a bit easier to work with an actual tally table rather than a dynamic one.

IF object_id('TempDB..#tblSuperviosr') IS NOT NULL
DROP TABLE #tblSuperviosr

CREATE TABLE #tblSuperviosr(
[PlanerID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
[AdvisorID] [int] NOT NULL,
[StartDate] [date] NULL,
[StartTime] [time](7) NULL,
[EndTime] [time](7) NULL,
[Flag] [bit] NULL,
CONSTRAINT [PK_tblSuperviosr] PRIMARY KEY CLUSTERED
(
[PlanerID] ASC
))

DECLARE @t TABLE
(AdvisorId INT, StartDate datetime, StartTime TIME, EndTime TIME, flag bit)

INSERT INTO @t
SELECT 1, '2012-01-01 00:00:00', '14:30', '16:30', 0

;WITH Tally (n) AS (
SELECT TOP 100 15*(ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1)
FROM sys.all_columns)
insert into #tblSuperviosr
SELECT AdvisorId, startdate,
TSStart=DATEADD(minute, n, StartTime)
,TSEnd=DATEADD(minute, n + 15, StartTime)
,flag
FROM @t
CROSS APPLY (
SELECT n
FROM Tally
WHERE n BETWEEN 0 AND DATEDIFF(minute, StartTime, DATEADD(minute, -15, EndTime))) a
ORDER BY AdvisorId, StartDate, TSStart

select *
from #tblSuperviosr


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search