How can i take this recursive CTE and convert it to a inline CTE for better performance?

How can i take this recursive  CTE and convert it to a inline CTE for better performance?


			
	;WITH cte (StartDateUtc, EndDateUtc, OffhireDaysPerCalendarDay) AS (

					SELECT 
						@StartDateUtc,	
						CONVERT(DATETIME, DATEADD(DAY, 1, CONVERT(DATE, @StartDateUtc))),			
						CONVERT(NUMERIC(28,17), CONVERT(NUMERIC(28,17), (DATEDIFF(SECOND, @StartDateUtc , DATEADD(DAY, 1, CONVERT(DATE, @StartDateUtc)))) / CONVERT(NUMERIC(28,17), 86400)) * @OffhireDaysPerCalendarDay)

					UNION ALL

					SELECT 
						CONVERT(DATETIME, DATEADD(DAY, 1, CONVERT(DATE, StartDateUtc))),
						CASE WHEN DATEADD(DAY, 1, CONVERT(DATE, StartDateUtc)) = CONVERT(DATE, @EndDateUtc) THEN @EndDateUtc ELSE CONVERT(DATETIME, DATEADD(DAY, 2, CONVERT(DATE, StartDateUtc))) END,
						CASE WHEN DATEADD(DAY, 1, CONVERT(DATE, StartDateUtc)) = CONVERT(DATE, @EndDateUtc) THEN CONVERT(NUMERIC(28,17), CONVERT(NUMERIC(28,17), (DATEDIFF(SECOND, DATEADD(DAY, 1, CONVERT(DATE, StartDateUtc)), @EndDateUtc)) / CONVERT(NUMERIC(28,17), 86400)) * @OffhireDaysPerCalendarDay) ELSE @OffhireDaysPerCalendarDay END						
					FROM cte
					WHERE DATEADD(DAY, 1, CONVERT(DATE, StartDateUtc)) <= CONVERT(DATE, @EndDateUtc)
				)

Open in new window

Paul MaurielloSoftware Programmer Developer Analyst EngineerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott PletcherSenior DBACommented:
I'm certain this is a follow-up to an earlier q:
https://www.experts-exchange.com/questions/29093677/How-can-I-improve-the-performance-of-this-SQL-Table-function-by-eliminating-the-While-Loop.html

Here's the general format of the improved function (as in the original q).  As I noted before, it's very straightforward, nothing really "slick".  
I couldn't test it because I don't have any sample data or the table type definition, but hopefully it's close enough you can debug any issues.  If not, provide sample data or specific details and I can help.


ALTER FUNCTION [dbo].[udtfTcVoyageOffhireDaily](
      @paramTcVoyageOffhire typeTcVoyageOffhire READONLY
)
RETURNS TABLE
AS
RETURN (
WITH
cteTally10 AS (
    SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
),
cteTally100 AS (
    SELECT 0 AS number
    FROM cteTally10 c1
    CROSS JOIN cteTally10 c2
),
cteTally10K AS (
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1 AS number
    FROM cteTally100 c1
    CROSS JOIN cteTally100 c2
)
SELECT p.VoyageID, p.TcInID, p.TcOutID,
    CalcStartDate AS StartDateUtc,
    CalcEndDate AS EndDateUct,
      (DATEDIFF(SECOND, CalcStartDate , CalcEndDate) / CONVERT(NUMERIC(28,17), 86400)) *
          p.OffhireDaysPerCalendarDay AS OffhireDaysPerCalendarDay
FROM @paramTcVoyageOffhire p
INNER JOIN cteTally10K t ON t.number BETWEEN 0 AND DATEDIFF(DAY, p.StartDateUtc, p.EndDateUtc)
CROSS APPLY (
    SELECT
    CASE WHEN t.number = 0 THEN StartDateUtc
          ELSE DATEADD(DAY, t.number, CAST(p.StartDateUtc AS date)) END AS CalcStartDate,
      CASE WHEN t.number = DATEDIFF(DAY, p.StartDateUtc, p.EndDateUtc) THEN EndDateUtc
          ELSE DATEADD(DAY, t.number + 1, CAST(p.StartDateUtc AS date)) END AS CalcEndDate
) AS aliases1
)
GO
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Paul MaurielloSoftware Programmer Developer Analyst EngineerAuthor Commented:
Thank you Scott You rock!
0
Scott PletcherSenior DBACommented:
Thanks!

This should definitely be mucho-faster than a multi-line function version.

Sorry about earlier, I was really swamped then at work (as now!), so I figured I'd just let Mark do it, since he started the q with generalities, I figured he'd follow up with specifics, but that didn't happen.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.