February 14, 2014 at 11:15 pm
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 have
Person Date Hours
101 02/01/2014 1.00
101 02/02/2014 1.30
It should be like
Person Date Hours
101 02/01/2014 0.15
101 02/01/2014 0.30
101 02/01/2014 0.45
101 02/01/2014 1.00
101 02/02/2014 0.15
101 02/02/2014 0.30
101 02/02/2014 0.45
101 02/02/2014 1.00
101 02/02/2014 1.15
101 02/02/2014 1.30
February 15, 2014 at 12:31 am
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 CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 16, 2014 at 6:30 pm
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 a
CROSS APPLY
(
SELECT n
FROM Tally
WHERE n <= 4.*[Hours]
) b
ORDER BY Person, [Date], n;
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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
February 16, 2014 at 6:35 pm
Thank you VERY MUCH for the quick solution, I will test this as soon as I get back in the office on Monday..
February 16, 2014 at 6:48 pm
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 a
CROSS APPLY
(
SELECT n
FROM Tally
WHERE n <= 4.*[Hours]
) b
ORDER BY Person, [Date], n;
Shhh... I was waiting for somebody to ask about that 😉
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 16, 2014 at 7:08 pm
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 a
CROSS APPLY
(
SELECT n
FROM Tally
WHERE n <= 4.*[Hours]
) b
ORDER 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
Change is inevitable... Change for the better is not.
February 16, 2014 at 7:12 pm
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 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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
February 16, 2014 at 7:16 pm
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 CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 16, 2014 at 7:19 pm
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 have
Person Date Hours
101 02/01/2014 1.00
101 02/02/2014 1.30
It should be like
Person Date Hours
101 02/01/2014 0.15
101 02/01/2014 0.30
101 02/01/2014 0.45
101 02/01/2014 1.00
101 02/02/2014 0.15
101 02/02/2014 0.30
101 02/02/2014 0.45
101 02/02/2014 1.00
101 02/02/2014 1.15
101 02/02/2014 1.30
There'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
Change is inevitable... Change for the better is not.
February 16, 2014 at 9:32 pm
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,
February 16, 2014 at 9:38 pm
Hello Dwayne,
I tried the script but it just keeps on running.
Here is the actual script:
WITH Tally (n) AS
(
SELECT TOP ((SELECT 1+CAST(4*MAX([hours]) AS INT) FROM TOTALS
where PERSON = '1010' and DATE between '02/05/2014' and '02/11/2014'
))
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 TOTALS a
CROSS APPLY
(
SELECT n
FROM Tally
WHERE n <= 4.*[hours]
) b
ORDER BY PERSON, [DATE], n;
February 16, 2014 at 10:36 pm
You might try putting the WHERE clause before ORDER BY instead of where you have it (the subquery that determines the TOP n rows in your Tally table).
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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
February 17, 2014 at 12:42 am
zulfansari (2/16/2014)
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,
Then a Tally Table will make the solution to this problem incredibly easy. I left the "Hours" mix in the output just so you can verify the output. You can remove that column whenever you're ready.
--===== Build a test table and populate it on-the-fly.
-- THIS IS NOT A PART OF THE SOLUTION!!!
-- WE JUST NEEDED SOMETHING TO DEMO THE SOLUTION WITH!!!
SET DATEFORMAT MDY;
SELECT d.Person
,[Date] = CAST(d.[Date] AS DATETIME)
,d.Hours
INTO #TestTable
FROM (
SELECT 101,'02/01/2014', 1.00 UNION ALL
SELECT 101,'02/02/2014', 1.25 UNION ALL
SELECT 101,'02/03/2014',11.75
)d(Person,[Date],Hours)
;
--===== This is the solution made incredibly easy
-- by the use of a Tally Table.
SELECT Person,[Date],Hours,IntervalHours = t.N*.25
FROM #TestTable
CROSS JOIN dbo.Tally t
WHERE t.N <= Hours*4
ORDER BY Person, Date, IntervalHours
;
Here's the output from the code above. Like I said, you can simply remove the "Hours" column if you don't really want it.
Person Date Hours IntervalHours
----------- ----------------------- ----- -------------
101 2014-02-01 00:00:00.000 1.00 0.25
101 2014-02-01 00:00:00.000 1.00 0.50
101 2014-02-01 00:00:00.000 1.00 0.75
101 2014-02-01 00:00:00.000 1.00 1.00
101 2014-02-02 00:00:00.000 1.25 0.25
101 2014-02-02 00:00:00.000 1.25 0.50
101 2014-02-02 00:00:00.000 1.25 0.75
101 2014-02-02 00:00:00.000 1.25 1.00
101 2014-02-02 00:00:00.000 1.25 1.25
101 2014-02-03 00:00:00.000 11.75 0.25
101 2014-02-03 00:00:00.000 11.75 0.50
101 2014-02-03 00:00:00.000 11.75 0.75
101 2014-02-03 00:00:00.000 11.75 1.00
101 2014-02-03 00:00:00.000 11.75 1.25
101 2014-02-03 00:00:00.000 11.75 1.50
101 2014-02-03 00:00:00.000 11.75 1.75
101 2014-02-03 00:00:00.000 11.75 2.00
101 2014-02-03 00:00:00.000 11.75 2.25
101 2014-02-03 00:00:00.000 11.75 2.50
101 2014-02-03 00:00:00.000 11.75 2.75
101 2014-02-03 00:00:00.000 11.75 3.00
101 2014-02-03 00:00:00.000 11.75 3.25
101 2014-02-03 00:00:00.000 11.75 3.50
101 2014-02-03 00:00:00.000 11.75 3.75
101 2014-02-03 00:00:00.000 11.75 4.00
101 2014-02-03 00:00:00.000 11.75 4.25
101 2014-02-03 00:00:00.000 11.75 4.50
101 2014-02-03 00:00:00.000 11.75 4.75
101 2014-02-03 00:00:00.000 11.75 5.00
101 2014-02-03 00:00:00.000 11.75 5.25
101 2014-02-03 00:00:00.000 11.75 5.50
101 2014-02-03 00:00:00.000 11.75 5.75
101 2014-02-03 00:00:00.000 11.75 6.00
101 2014-02-03 00:00:00.000 11.75 6.25
101 2014-02-03 00:00:00.000 11.75 6.50
101 2014-02-03 00:00:00.000 11.75 6.75
101 2014-02-03 00:00:00.000 11.75 7.00
101 2014-02-03 00:00:00.000 11.75 7.25
101 2014-02-03 00:00:00.000 11.75 7.50
101 2014-02-03 00:00:00.000 11.75 7.75
101 2014-02-03 00:00:00.000 11.75 8.00
101 2014-02-03 00:00:00.000 11.75 8.25
101 2014-02-03 00:00:00.000 11.75 8.50
101 2014-02-03 00:00:00.000 11.75 8.75
101 2014-02-03 00:00:00.000 11.75 9.00
101 2014-02-03 00:00:00.000 11.75 9.25
101 2014-02-03 00:00:00.000 11.75 9.50
101 2014-02-03 00:00:00.000 11.75 9.75
101 2014-02-03 00:00:00.000 11.75 10.00
101 2014-02-03 00:00:00.000 11.75 10.25
101 2014-02-03 00:00:00.000 11.75 10.50
101 2014-02-03 00:00:00.000 11.75 10.75
101 2014-02-03 00:00:00.000 11.75 11.00
101 2014-02-03 00:00:00.000 11.75 11.25
101 2014-02-03 00:00:00.000 11.75 11.50
101 2014-02-03 00:00:00.000 11.75 11.75
As previously posted, here's where you can learn more about the Tally Table, how to build it, and how it works to avoid loops and cursors.
http://www.sqlservercentral.com/articles/T-SQL/62867/
Once you've mastered using a physical Tally Table, then you can get into what Jason and Dwain where showing... on the fly creation of Tally-Table-like structures, which is also introduced in the article I provided a link to.
The reason why I don't just cough up the very simple method of building a Tally Table is because I really want you to understand how the Tally Table works. My personal belief is that it will improve your career as someone using T-SQL as it has done for nearly everyone that's ever used such a thing. It changes your mind from thinking in rows to thinking in columns and that's the main paradigm shift required to write some really nasty fast code in T-SQL.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 17, 2014 at 5:47 pm
Hi Jeff, Dwain.c and SQLRNNR,
You guys are AWESOME..
I really appreciate your help and learned new tips and tricks from all of you..
God bless you all!
Just an FYI, I ended up creating a Tally table and used the last Script provided by Jeff..
Best Regards,
February 17, 2014 at 6:23 pm
zulfansari (2/17/2014)
Hi Jeff, Dwain.c and SQLRNNR,You guys are AWESOME..
I really appreciate your help and learned new tips and tricks from all of you..
God bless you all!
Just an FYI, I ended up creating a Tally table and used the last Script provided by Jeff..
Best Regards,
Good to hear. FWIW, the Numbers table in the script I shared and the Tally table in Jeff's script are the same thing - just different names.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 15 posts - 1 through 15 (of 53 total)
You must be logged in to reply to this topic. Login to reply