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-Addicted
SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)

Group: General Forum Members
Points: 459 Visits: 434
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;
dwain.c
dwain.c
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18579 Visits: 6431
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!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)

Group: General Forum Members
Points: 223983 Visits: 42007
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.
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-Addicted
SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)

Group: General Forum Members
Points: 459 Visits: 434
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,
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)

Group: General Forum Members
Points: 69135 Visits: 18570
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

DiabloSlayer
DiabloSlayer
SSC-Addicted
SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)

Group: General Forum Members
Points: 459 Visits: 434
Thank you SQLRNNR.

By the way, what is FWIW? :-)
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)

Group: General Forum Members
Points: 69135 Visits: 18570
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

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

Group: General Forum Members
Points: 223983 Visits: 42007
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.
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
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18579 Visits: 6431
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!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)

Group: General Forum Members
Points: 223983 Visits: 42007
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.
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
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