April 9, 2013 at 9:05 am
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;
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
April 9, 2013 at 10:25 pm
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
April 9, 2013 at 10:53 pm
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.
April 9, 2013 at 11:01 pm
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
April 9, 2013 at 11:18 pm
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
April 10, 2013 at 1:11 am
abhas (4/9/2013)
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
That's functionally equivalent to the code I wrote for you - the execution plans may even be the same.
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
April 10, 2013 at 1:33 am
Thank you Matak and Chris.
🙂 🙂 🙂
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply