Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

split single row into four rows. Expand / Collapse
Author
Message
Posted Tuesday, April 9, 2013 4:29 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, July 24, 2014 9:56 AM
Points: 7,120, Visits: 13,491
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
Post #1440251
Posted Tuesday, April 9, 2013 7:46 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 9:10 AM
Points: 181, Visits: 467
Hi,

I tried but not able to insert data.

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

Please help.

thanks
Abhas.
Post #1440327
Posted Tuesday, April 9, 2013 7:52 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 12:40 AM
Points: 218, Visits: 1,847
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.
Post #1440335
Posted Tuesday, April 9, 2013 8:00 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 9:10 AM
Points: 181, Visits: 467
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.
Post #1440340
Posted Tuesday, April 9, 2013 8:08 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, July 24, 2014 9:56 AM
Points: 7,120, Visits: 13,491
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
Post #1440348
Posted Tuesday, April 9, 2013 9:05 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, July 24, 2014 9:56 AM
Points: 7,120, Visits: 13,491
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
Post #1440404
Posted Tuesday, April 9, 2013 10:25 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 9:10 AM
Points: 181, Visits: 467
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. :)




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
Post #1440636
Posted Tuesday, April 9, 2013 10:53 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 12:40 AM
Points: 218, Visits: 1,847
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.
Post #1440644
Posted Tuesday, April 9, 2013 11:01 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 9:10 AM
Points: 181, Visits: 467
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


Post #1440646
Posted Tuesday, April 9, 2013 11:18 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 12:40 AM
Points: 218, Visits: 1,847
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

Post #1440650
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse