RE: screenshot on view – SQLServerCentral

SSC Guru

Points: 1003863

September 22, 2013 at 11:36 pm

Erland Sommarskog (9/22/2013)
Then again, if all you have is half hours for 24 hours, with no regards to date, all you need is something that generates the 48 half hours for you, and that could be done in the view with a recursive CTE:
WITH halfhours AS (
SELECT cast('00:00' AS time(0)) AS halfhour
UNION ALL
SELECT dateadd(minute, 30, halfhour)
FROM halfhours
WHERE halfhour < cast('23:30' AS time(0))
)

I know you can probably do such rCTEs in your sleep 😉 but that's an rCTE that counts and, even for small numbers of rows, it's unnecessarily resource intensive. Please see the following article for why you might want to avoid such a thing.

[font="Arial Black"]Hidden RBAR: Counting with Recursive CTE's[/font][/url]

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)