asked on

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

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Paul Mauriello

ASKER

Thank you Scott You rock!

Scott Pletcher

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.