September 18, 2013 at 5:57 am
i have a table with three columns as
col1 col2 col3
11:30 13:30 15
00:10 01:40 5
the out put should be as follows
11.30,11.45,12.00,12.15,12.30,12.45,13.00,13.15,13.30
00.10,00.15,00.20,00.25.....01.40
earlier help is highly appreciated
September 18, 2013 at 8:03 am
That was a little bit of a stretch for me, some problems are just awkward for me when looking for set oriented solutions. I picked a datemath / substring direction, maybe it would have been easier with a modulo sort of direction. I didn't do the csv part, thats specific enough for a separate subject, maybe you could split the problem into two questions and see if you can combine the two.
Anyways, this is what I got just to get the values in column 'RESULT_COLUMN', but I think my method sort of makes me wish tsql loops were more acceptable and not so darn slow. In this case, I would have definitely preferred a loop LOL, plus I picked the direction before my 2nd cup of coffee, not a wise move!
In any case, I think this is an interesting problem and something I'm going to play with some. If you can, let me know what you think, even if its "quit cluttering up my thread with crap code." I focused on just getting the values into RESULT_COLUMN, not the csv layout, and the values are keyed by col1, col2 and col3.
DROP TABLE #THREECOLS
CREATE TABLE #THREECOLS
(
COL1 VARCHAR(10),
COL2 VARCHAR(10),
COL3 INT
)
INSERT INTO #THREECOLS
SELECT '11:30','13:30',15
UNION
SELECT '00:10','01:40',5
SELECT * FROM
(
SELECT COL1, COL2, COL3, C, SUBSTRING(CONVERT(VARCHAR(15),DATEADD(mi,C * COL3, CONVERT(DATETIME,'2012-01-01')),8),1,5) RESULT_COLUMN
FROM
(
SELECT A * 125 + B * 25 + C * 5 + D AS C FROM
(SELECT 0 A UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) T1
CROSS JOIN
(SELECT 0 B UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) T2
CROSS JOIN
(SELECT 0 C UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) T3
CROSS JOIN
(SELECT 0 D UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) T4
) TALLY
CROSS JOIN
#THREECOLS
) T1
WHERE RESULT_COLUMN >= COL1 AND RESULT_COLUMN <= COL2 AND C * COL3 < 24 * 60
ORDER BY COL1, COL2, RESULT_COLUMN
September 18, 2013 at 8:49 am
Using the correct datatypes might help to simplify the solution.
CREATE TABLE #THREECOLS
(
COL1 TIME,
COL2 TIME,
COL3 INT
)
INSERT INTO #THREECOLS
SELECT '11:30','13:30',15
UNION ALL
SELECT '00:10','01:40',5;
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
), --10E+1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
cteTally(N) AS (
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 FROM E4
)
SELECT STUFF( (SELECT ',' + CONVERT( char(5), DATEADD( MINUTE, N * COL3, COL1), 8)
FROM cteTally t
JOIN #THREECOLS c ON t.N <= (DATEDIFF( MINUTE, COL1, COL2) / COL3)
WHERE x.COL1 = c.COL1
ORDER BY COL1 DESC
FOR XML PATH('')), 1,1, '')
FROM #THREECOLS x
GROUP BY COL1
DROP TABLE #THREECOLS
September 18, 2013 at 8:59 am
Nice job on the code Luis!
September 18, 2013 at 9:07 am
patrickmcginnis59 10839 (9/18/2013)
Nice job on the code Luis!
Thank you. Now let's hope that kalikoi can understand it or post any questions he/she has. 🙂
September 19, 2013 at 12:27 am
gentleman thx for the code but here is my approach
composed a udf that returns the csv
DECLARE @StartTime TIME(0) = '00:10'
DECLARE @EndTime TIME(0) = '01:40'
DECLARE @MinutesToAdd INT = 5
DECLARE @RunningTime TIME(0)='00:10'
Declare @intervals nvarchar(max)='00:10,'
while(@RunningTime<@EndTime)
begin
set @RunningTime=DATEADD(MINUTE, @MinutesToAdd, @RunningTime)
set @intervals=@intervals + SUBSTRING( convert(varchar, @RunningTime,108),1,5) + ','
end
return LEFT(@intervals, LEN(@intervals) - 1)
September 19, 2013 at 12:35 am
kalikoi (9/19/2013)
gentleman thx for the code but here is my approachcomposed a udf that returns the csv
DECLARE @StartTime TIME(0) = '00:10'
DECLARE @EndTime TIME(0) = '01:40'
DECLARE @MinutesToAdd INT = 5
DECLARE @RunningTime TIME(0)='00:10'
Declare @intervals nvarchar(max)='00:10,'
while(@RunningTime<@EndTime)
begin
set @RunningTime=DATEADD(MINUTE, @MinutesToAdd, @RunningTime)
set @intervals=@intervals + SUBSTRING( convert(varchar, @RunningTime,108),1,5) + ','
end
return LEFT(@intervals, LEN(@intervals) - 1)
My guess is the performance of Luis his query will blow the socks off your udf.
You are using a WHILE loop, while Luis is using a set-based approach which is much much faster.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
September 19, 2013 at 10:49 pm
Koen Verbeeck (9/19/2013)
kalikoi (9/19/2013)
gentleman thx for the code but here is my approachcomposed a udf that returns the csv
DECLARE @StartTime TIME(0) = '00:10'
DECLARE @EndTime TIME(0) = '01:40'
DECLARE @MinutesToAdd INT = 5
DECLARE @RunningTime TIME(0)='00:10'
Declare @intervals nvarchar(max)='00:10,'
while(@RunningTime<@EndTime)
begin
set @RunningTime=DATEADD(MINUTE, @MinutesToAdd, @RunningTime)
set @intervals=@intervals + SUBSTRING( convert(varchar, @RunningTime,108),1,5) + ','
end
return LEFT(@intervals, LEN(@intervals) - 1)
My guess is the performance of Luis his query will blow the socks off your udf.
You are using a WHILE loop, while Luis is using a set-based approach which is much much faster.
+1000
Not only a WHILE loop, but that's also a scalar UDF which will typically be 6 or 7 times slower than a properly formed iTVF (inline Table Valued Function).
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply