SQL option in a BAQ

Epicor ERP 10
1

Hello,

I built a SQL query that returns the first and last day of every week between @start_date and @end_date (max range : 1 year) using recursive CTE. I made a BAQ based from that SQL query. It works for short date range, but I couldn’t find a way to specify the "option (maxrecursion 385)" that allows me to use a 1 year range. As a result, I get the error “The statement terminated. The maximum recursion 100 has been exhausted before statement completion.”

Any idea?

Here my original SQL Query :

declare @start_date date
declare @end_date date
declare @end_date2 date

set @start_date=‘2015-01-01’
set @end_date=‘2015-12-31’

– Permet d’aller chercher le samedi suivant le @end_date (sauf si c’est déjà un samedi)
set @end_date2 = dateadd(d,7-DATEPART(dw,@end_date), @end_date)

;

WITH alldays AS (

-- Liste de tous les jours entre start_date et end_date2
SELECT @start_date AS dt

UNION ALL

SELECT DATEADD(dd, 1, dt)
FROM alldays s
WHERE DATEADD(dd, 1, dt) <= @end_date2

)
, dayofWeekTemp as (
SELECT Cast(DATEPART(dw,s.dt) as int) dayOfWeekValue , s.dt dateValue
FROM alldays s
)

– Liste toutes les semaines à partir du dimanche précédant start_date jusqu’au samedi suivant end_date (à l’aide de end_date2)

select isnull(debutsemaine.dateValue,DATEADD(d,-6,finsemaine.dateValue)) AS debut, finsemaine.dateValue AS fin
from dayofWeekTemp debutsemaine RIGHT JOIN dayofWeekTemp finsemaine
ON finsemaine.dateValue = dateadd(d, 6, debutsemaine.dateValue)
where finsemaine.dayOfWeekValue = 7
or debutsemaine.dayOfWeekValue = 1

option (maxrecursion 385)

2

Instead of getting every day, why not get the first day of the week on the start date, then add 7 until the end date? For the last day, get the first day, add 6, then add 7 until the end date. Then you only have to cycle through 52 times instead of 365.

I don’t know if I am understanding the intent of what you are trying to do though.

3 Likes
3

That makes sense. My original query comes from a search on the internet. I reworked it, and I successfully brought it below 100 recursive using your idea.

Thanks!

Here the new query :

declare @start_date date
declare @end_date date

set @start_date=‘2015-01-01’
set @end_date=‘2015-12-31’

;

WITH week AS (
SELECT DATEADD(d, -CAST(DATEPART(dw, @start_date) as int) + 1, @start_date) AS WeekStart,
DATEADD(d, 7 - CAST(DATEPART(dw, @start_date) as int), @start_date) AS WeekEnd

UNION ALL

SELECT DATEADD(dd, 7, WeekStart), DATEADD(dd, 7, WeekEnd)
FROM week
WHERE DATEADD(dd, 7, WeekStart) <= dateadd(d,7-DATEPART(dw,@end_date), @end_date)
)

SELECT *
FROM week

3 Likes