May 2, 2014 at 2:57 pm
I have log files that I am loading into a table with duration data in the format "xx hrs xx min xx sec". Only the parts that are required will be there so if duration is only 2 seconds , the column will show "2 sec".
I am trying to get the duration into in to do some analysis on it and I have come up with this query so far which returns the correct data but i am wondering if there is a way to do what I am trying in a more readable format.
CREATE TABLE #tmp(duration VARCHAR(20))
INSERT INTO #tmp
VALUES ('1 hrs 20 min 12 sec'), --4812 sec
('48 sec'), --48 sec
('39 min 1 sec'), --2341 sec
('11 hrs 1 min 1 sec'), --39661 sec
('59 min 0 sec'), --3540 sec
('2 min 50 sec') --170 sec
and this is what I have so far
SELECT CASE
WHEN CHARINDEX('hrs', duration, 1) <> 0 THEN CAST(SUBSTRING(duration, 1, CHARINDEX(' hrs', duration, 1) - 1)AS int) * 3600 + CAST(SUBSTRING(duration, CHARINDEX(' hrs', duration, 1) + 5, CHARINDEX(' min', duration, 1) - 7)AS int) * 60 + CAST(SUBSTRING(duration, CHARINDEX(' min', duration, 1) + 5, CHARINDEX(' sec', duration, 1) - 14)AS int)
WHEN CHARINDEX('hrs', duration, 1) = 0
AND CHARINDEX(' min', duration, 1) <> 0 THEN CAST(SUBSTRING(duration, 1, CHARINDEX(' min', duration, 1) - 1)AS int) * 60 + CAST(SUBSTRING(duration, CHARINDEX(' min', duration, 1) + 5, CHARINDEX(' sec', duration, 1) - 7)AS int)
WHEN CHARINDEX('hrs', duration, 1) = 0
AND CHARINDEX(' min', duration, 1) = 0 THEN SUBSTRING(duration, 1, CHARINDEX(' sec', duration, 1) - 1)
END AS duration from #tmp
I ultimately plan on converting this to a SSIS expression so that is why I am looking to simplify it.
May 2, 2014 at 4:12 pm
I'm not sure if this is considered simpler. 😉
SELECT duration, SUM(iduration)
FROM (
SELECT duration,
Item * CASE ROW_NUMBER() OVER( PARTITION BY duration ORDER BY ItemNumber DESC)
WHEN 3 THEN 3600
WHEN 2 THEN 60
ELSE 1 END iduration
FROM #tmp x
CROSS APPLY dbo.PatternSplitCM( duration, '%[0-9]%')
WHERE Matched = 1) x
GROUP BY duration
ORDER BY duration
You can read about dbo.PatternSplitCM in here: http://qa.sqlservercentral.com/articles/String+Manipulation/94365/
May 2, 2014 at 5:05 pm
Another way....
select datediff(second,0,cast(replace(replace(replace(fix.duration,' hrs ',':'),' min ',':'),' sec','') as datetime))
from #tmp t
cross apply (
select
case
when len(t.duration)<8 then '0 hrs 0 min '
when len(t.duration)<15 then '0 hrs '
else ''
end + t.duration
) as fix(duration)
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);
May 5, 2014 at 9:01 am
Thank you both for your suggestions.
Luis, The pattern splitter is going to be very useful for me, thanks for introducing me to it, but in this case I dont think I can use it because even though in SQL it works great, I dont think will be able to convert it to an SSIS expression. I would probably have to convert the logic in the function to a script task which is beyond my expertise at the moment. I will definitely get some use out of this function in other places.
MM, this looks like an approach that I should be able to translate to an SSIS express(not sure if I can do anything with cross apply) and there is less logic to convert than my original.
Thanks again.
-edit: final SSIS expression using MM example
DATEDIFF("ss",(DT_DBTIMEstamp)"1899/12/30",(DT_DBTIMEstamp)(REPLACE(REPLACE(REPLACE((LEN(SessionDuration) < 8 ? "0 hrs 0 min " : LEN(SessionDuration) < 15 ? "0 hrs " : "") + SessionDuration," hrs ",":")," min ",":")," sec","")))
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply