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 Sunday, February 16, 2014 9:38 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 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;
Post #1541983
Posted Sunday, February 16, 2014 10:36 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 @ 9:53 PM
Points: 3,438, Visits: 5,390
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 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 #1541990
Posted Monday, February 17, 2014 12:42 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 7:27 AM
Points: 35,769, Visits: 32,437
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
"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 #1541997
Posted Monday, February 17, 2014 5:47 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, 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,
Post #1542319
Posted Monday, February 17, 2014 6:23 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 3:20 PM
Points: 18,064, Visits: 16,099
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
Post #1542325
Posted Monday, February 17, 2014 8:27 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 SQLRNNR.

By the way, what is FWIW?
Post #1542340
Posted Monday, February 17, 2014 8:30 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 3:20 PM
Points: 18,064, Visits: 16,099
FWIW = "For What it's Worth"



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #1542342
Posted Monday, February 17, 2014 8:40 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 7:27 AM
Points: 35,769, Visits: 32,437
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,


Thanks for the great feedback. As Red Green would say, "We're all in this together and I'm pullin' for ya."

I need to double-check, though. You made sure to include the clustered index on the Tally Table, right? It's a critical piece of the performance. It'll run relatively dog slow without it.

Also, as Jason (SQLRNNR) said, his "Numbers" table is the same as what I call a "Tally" Table. Dwain used the classic method of using a "pseudo-cursor" (use the presence of rows to act as a "loop") to build his on the fly. The article I pointed you to also contains a "cCTE" (Cascading CTE, not to be confused with a "Recursive" CTE which is horribly slow) method by Itzik Ben-Gan which is a tiny bit slower than a physical Tally Table but produces exactly zero reads.

Both Dwain and Jason used CROSS APPLY while I used a CROSS JOIN. All of that just shows the flexibility of the tool. It can be used for a whole lot more in a whole lot of different ways.

Hat's off to both Dwain and Jason for pumping out the code.


--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 #1542346
Posted Monday, February 17, 2014 9:44 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 @ 9:53 PM
Points: 3,438, Visits: 5,390
Jeff Moden (2/17/2014)
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,


Thanks for the great feedback. As Red Green would say, "We're all in this together and I'm pullin' for ya."

I need to double-check, though. You made sure to include the clustered index on the Tally Table, right? It's a critical piece of the performance. It'll run relatively dog slow without it.

Also, as Jason (SQLRNNR) said, his "Numbers" table is the same as what I call a "Tally" Table. Dwain used the classic method of using a "pseudo-cursor" (use the presence of rows to act as a "loop") to build his on the fly. The article I pointed you to also contains a "cCTE" (Cascading CTE, not to be confused with a "Recursive" CTE which is horribly slow) method by Itzik Ben-Gan which is a tiny bit slower than a physical Tally Table but produces exactly zero reads.

Both Dwain and Jason used CROSS APPLY while I used a CROSS JOIN. All of that just shows the flexibility of the tool. It can be used for a whole lot more in a whole lot of different ways.

Hat's off to both Dwain and Jason for pumping out the code.


Who is Red Green? Any relation to Opal Azure?



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 #1542355
Posted Monday, February 17, 2014 10:16 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 7:27 AM
Points: 35,769, Visits: 32,437
dwain.c (2/17/2014)
Jeff Moden (2/17/2014)
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,


Thanks for the great feedback. As Red Green would say, "We're all in this together and I'm pullin' for ya."

I need to double-check, though. You made sure to include the clustered index on the Tally Table, right? It's a critical piece of the performance. It'll run relatively dog slow without it.

Also, as Jason (SQLRNNR) said, his "Numbers" table is the same as what I call a "Tally" Table. Dwain used the classic method of using a "pseudo-cursor" (use the presence of rows to act as a "loop") to build his on the fly. The article I pointed you to also contains a "cCTE" (Cascading CTE, not to be confused with a "Recursive" CTE which is horribly slow) method by Itzik Ben-Gan which is a tiny bit slower than a physical Tally Table but produces exactly zero reads.

Both Dwain and Jason used CROSS APPLY while I used a CROSS JOIN. All of that just shows the flexibility of the tool. It can be used for a whole lot more in a whole lot of different ways.

Hat's off to both Dwain and Jason for pumping out the code.


Who is Red Green? Any relation to Opal Azure?


I didn't get to see it very often (I never did watch TV much), but I laughed my hinny off when I did get to see it.
http://en.wikipedia.org/wiki/The_Red_Green_Show


--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 #1542363
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse