Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

Author
 Message
 Posted Friday, February 14, 2014 11:15 PM
 SSC-Enthusiastic Group: General Forum Members Last Login: Friday, August 5, 2016 1:52 AM Points: 109, Visits: 434
 Hello SQL Gurus,I have a table with three columns, Person, Date, Hours. I need to turn hours into 15 minutes interval. Can someone please help?Thank you,Here is what I havePerson Date Hours101 02/01/2014 1.00101 02/02/2014 1.30It should be likePerson Date Hours101 02/01/2014 0.15101 02/01/2014 0.30101 02/01/2014 0.45101 02/01/2014 1.00101 02/02/2014 0.15101 02/02/2014 0.30101 02/02/2014 0.45101 02/02/2014 1.00101 02/02/2014 1.15101 02/02/2014 1.30
Post #1541824
 Posted Saturday, February 15, 2014 12:31 AM
 SSC-Insane Group: General Forum Members Last Login: Monday, November 21, 2016 11:03 AM Points: 20,009, Visits: 18,255
 Here is something that will work. One significant difference is that when converting time to decimal, you usually use .25 instead of 15 increments. Based on that, here is the script.`DECLARE @sometab TABLE (Person INT, [Date] DATE, [Hours] DECIMAL(12,2))DECLARE @somenums TABLE (Numbers DECIMAL(12,2))INSERT INTO @sometab ( Person, Date, Hours )VALUES ( 101,'02/01/2014',1.00),(101,'02/02/2014',1.5);INSERT INTO @somenums ( Numbers )SELECT (N *.25) FROM AdminDB.dbo.Numbers WHERE N < 100; SELECT t.Person,t.Date,s.Numbers AS Hours FROM @sometab t CROSS APPLY @somenums s WHERE t.[Hours] >= s.Numbers ORDER BY t.[Date],s.Numbers` Jason AKA CirqueDeSQLeilI have given a name to my pain...MCM SQL Server, MVPSQL RNNRPosting Performance Based Questions - Gail Shaw
Post #1541826
 Posted Sunday, February 16, 2014 6:30 PM
 Hall of Fame Group: General Forum Members Last Login: Wednesday, February 24, 2016 6:28 AM Points: 3,977, Visits: 6,431
 If you don't have a numbers table at your disposal like Jason does, this will also work.`DECLARE @sometab TABLE (Person INT, [Date] DATE, [Hours] DECIMAL(12,2))INSERT INTO @sometab ( Person, Date, Hours )VALUES ( 101,'02/01/2014',1.00),(101,'02/02/2014',1.5);WITH Tally (n) AS( SELECT TOP ((SELECT 1+CAST(4*MAX([Hours]) AS INT) FROM @sometab)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM sys.all_columns)SELECT Person, [Date], [Hours]=DATEADD(minute, 15*n, CAST('00:00' AS TIME))FROM @sometab aCROSS APPLY( SELECT n FROM Tally WHERE n <= 4.*[Hours]) bORDER BY Person, [Date], n;` My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!My thought question: Have you ever been told that your query runs too fast?My advice:INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?Since random numbers are too important to be left to chance, let's generate some!Learn to understand recursive CTEs by example.Splitting strings based on patterns can be fast!My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Post #1541953
 Posted Sunday, February 16, 2014 6:35 PM
 SSC-Enthusiastic Group: General Forum Members Last Login: Friday, August 5, 2016 1:52 AM Points: 109, Visits: 434
 Thank you VERY MUCH for the quick solution, I will test this as soon as I get back in the office on Monday..
Post #1541955
 Posted Sunday, February 16, 2014 6:48 PM
 SSC-Insane Group: General Forum Members Last Login: Monday, November 21, 2016 11:03 AM Points: 20,009, Visits: 18,255
 dwain.c (2/16/2014)If you don't have a numbers table at your disposal like Jason does, this will also work.`DECLARE @sometab TABLE (Person INT, [Date] DATE, [Hours] DECIMAL(12,2))INSERT INTO @sometab ( Person, Date, Hours )VALUES ( 101,'02/01/2014',1.00),(101,'02/02/2014',1.5);WITH Tally (n) AS( SELECT TOP ((SELECT 1+CAST(4*MAX([Hours]) AS INT) FROM @sometab)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM sys.all_columns)SELECT Person, [Date], [Hours]=DATEADD(minute, 15*n, CAST('00:00' AS TIME))FROM @sometab aCROSS APPLY( SELECT n FROM Tally WHERE n <= 4.*[Hours]) bORDER BY Person, [Date], n;`Shhh... I was waiting for somebody to ask about that Jason AKA CirqueDeSQLeilI have given a name to my pain...MCM SQL Server, MVPSQL RNNRPosting Performance Based Questions - Gail Shaw
Post #1541961
 Posted Sunday, February 16, 2014 7:08 PM
 SSC-Forever Group: General Forum Members Last Login: Today @ 7:14 AM Points: 42,072, Visits: 39,455
 SQLRNNR (2/16/2014)dwain.c (2/16/2014)If you don't have a numbers table at your disposal like Jason does, this will also work.`DECLARE @sometab TABLE (Person INT, [Date] DATE, [Hours] DECIMAL(12,2))INSERT INTO @sometab ( Person, Date, Hours )VALUES ( 101,'02/01/2014',1.00),(101,'02/02/2014',1.5);WITH Tally (n) AS( SELECT TOP ((SELECT 1+CAST(4*MAX([Hours]) AS INT) FROM @sometab)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM sys.all_columns)SELECT Person, [Date], [Hours]=DATEADD(minute, 15*n, CAST('00:00' AS TIME))FROM @sometab aCROSS APPLY( SELECT n FROM Tally WHERE n <= 4.*[Hours]) bORDER BY Person, [Date], n;`Shhh... I was waiting for somebody to ask about that Since the secret is out , please see the following article for what a Tally Table or similar structure is an how it can be used to replace certain loops in a very high performance manner. It'll change the way you think in T-SQL.http://www.sqlservercentral.com/articles/T-SQL/62867/ --Jeff Moden"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column." Helpful Links:How to post code problemsHow to post performance problems
Post #1541966
 Posted Sunday, February 16, 2014 7:12 PM
 Hall of Fame Group: General Forum Members Last Login: Wednesday, February 24, 2016 6:28 AM Points: 3,977, Visits: 6,431
 zulfansari (2/16/2014)Thank you VERY MUCH for the quick solution, I will test this as soon as I get back in the office on Monday..OK I'll bite. What's a Numbers table? My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!My thought question: Have you ever been told that your query runs too fast?My advice:INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?Since random numbers are too important to be left to chance, let's generate some!Learn to understand recursive CTEs by example.Splitting strings based on patterns can be fast!My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Post #1541967
 Posted Sunday, February 16, 2014 7:16 PM
 SSC-Insane Group: General Forum Members Last Login: Monday, November 21, 2016 11:03 AM Points: 20,009, Visits: 18,255
 dwain.c (2/16/2014)zulfansari (2/16/2014)Thank you VERY MUCH for the quick solution, I will test this as soon as I get back in the office on Monday..OK I'll bite. What's a Numbers table?Jeff gave up that link already too Jason AKA CirqueDeSQLeilI have given a name to my pain...MCM SQL Server, MVPSQL RNNRPosting Performance Based Questions - Gail Shaw
Post #1541969
 Posted Sunday, February 16, 2014 7:19 PM
 SSC-Forever Group: General Forum Members Last Login: Today @ 7:14 AM Points: 42,072, Visits: 39,455
 zulfansari (2/14/2014)Hello SQL Gurus,I have a table with three columns, Person, Date, Hours. I need to turn hours into 15 minutes interval. Can someone please help?Thank you,Here is what I havePerson Date Hours101 02/01/2014 1.00101 02/02/2014 1.30It should be likePerson Date Hours101 02/01/2014 0.15101 02/01/2014 0.30101 02/01/2014 0.45101 02/01/2014 1.00101 02/02/2014 0.15101 02/02/2014 0.30101 02/02/2014 0.45101 02/02/2014 1.00101 02/02/2014 1.15101 02/02/2014 1.30There's a bit of confusion in your original data. Are the decimal places minutes or decimal hours? You seem to indicate in your desired output that they're hours but I wanted to make sure. --Jeff Moden"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column." Helpful Links:How to post code problemsHow to post performance problems
Post #1541970
 Posted Sunday, February 16, 2014 9:32 PM
 SSC-Enthusiastic Group: General Forum Members Last Login: Friday, August 5, 2016 1:52 AM Points: 109, Visits: 434
 Hi Jeff,Actually it's 0.25,0.50, etc. format, I made up the data for the post and used the wrong format.Thank you,
Post #1541982

 Permissions