Author Message DiabloSlayer SSC-Addicted Group: General Forum Members Points: 449 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 SQLRNNR SSC Guru Group: General Forum Members Points: 66717 Visits: 18570 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 dwain.c SSCoach Group: General Forum Members Points: 17839 Visits: 6431 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 DiabloSlayer SSC-Addicted Group: General Forum Members Points: 449 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.. SQLRNNR SSC Guru Group: General Forum Members Points: 66717 Visits: 18570 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 Jeff Moden SSC Guru Group: General Forum Members Points: 215382 Visits: 41979 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 ModenRBAR 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.If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair Helpful Links:How to post code problemsHow to post performance problemsForum FAQs dwain.c SSCoach Group: General Forum Members Points: 17839 Visits: 6431 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 SQLRNNR SSC Guru Group: General Forum Members Points: 66717 Visits: 18570 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 :-D Jason AKA CirqueDeSQLeilI have given a name to my pain...MCM SQL Server, MVPSQL RNNRPosting Performance Based Questions - Gail Shaw Jeff Moden SSC Guru Group: General Forum Members Points: 215382 Visits: 41979 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 ModenRBAR 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.If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair Helpful Links:How to post code problemsHow to post performance problemsForum FAQs DiabloSlayer SSC-Addicted Group: General Forum Members Points: 449 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,