SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


15 minutes Interval from Hours - Please Help


15 minutes Interval from Hours - Please Help

Author
Message
DiabloSlayer
DiabloSlayer
SSC-Enthusiastic
SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)

Group: General Forum Members
Points: 167 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 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
SQLRNNR
SQLRNNR
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32645 Visits: 18558
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

dwain.c
dwain.c
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7381 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 a
CROSS APPLY
(
SELECT n
FROM Tally
WHERE n <= 4.*[Hours]
) b
ORDER 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
DiabloSlayer
SSC-Enthusiastic
SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)

Group: General Forum Members
Points: 167 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
SQLRNNR
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32645 Visits: 18558
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

Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87048 Visits: 41112
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

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.
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 problems
How to post performance problems
Forum FAQs
dwain.c
dwain.c
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7381 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
SQLRNNR
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32645 Visits: 18558
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 CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87048 Visits: 41112
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

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.
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 problems
How to post performance problems
Forum FAQs
DiabloSlayer
DiabloSlayer
SSC-Enthusiastic
SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)

Group: General Forum Members
Points: 167 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,
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search