Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

15 minutes Interval from Hours - Please Help Expand / Collapse
Author
Message
Posted Friday, February 14, 2014 11:15 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, August 15, 2014 11:40 AM
Points: 66, Visits: 261
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
Post #1541824
Posted Saturday, February 15, 2014 12:31 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 10:58 PM
Points: 21,755, Visits: 15,461
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1541826
Posted Sunday, February 16, 2014 6:30 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 3:25 AM
Points: 3,648, Visits: 5,328
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!
Post #1541953
Posted Sunday, February 16, 2014 6:35 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, August 15, 2014 11:40 AM
Points: 66, Visits: 261
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

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 10:58 PM
Points: 21,755, Visits: 15,461
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1541961
Posted Sunday, February 16, 2014 7:08 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:14 PM
Points: 37,107, Visits: 31,665
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1541966
Posted Sunday, February 16, 2014 7:12 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 3:25 AM
Points: 3,648, Visits: 5,328
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!
Post #1541967
Posted Sunday, February 16, 2014 7:16 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 10:58 PM
Points: 21,755, Visits: 15,461
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1541969
Posted Sunday, February 16, 2014 7:19 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:14 PM
Points: 37,107, Visits: 31,665
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1541970
Posted Sunday, February 16, 2014 9:32 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, August 15, 2014 11:40 AM
Points: 66, Visits: 261
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
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse