camnott Posted June 4, 2018 Share Posted June 4, 2018 Hello,I created a new report in JasperSoft Studio (Which runs perfectly fine in preview mode, and in SQL Server Management Studio)..However, I get an error when trying to run from Clarity Advanced Reporting.Here is part of the Query that I believe is causing the issue (Removed rest due to containing secured information) : SET NOCOUNT ONDECLARE @start_date date = 'Jan 1, 2011';DECLARE @end_date date = dateadd(year,40,@start_date);DECLARE @Nwkdy as Table (dtday DATE);WITH AllDays AS ( SELECT @start_date AS DtDay , MONTH(@start_date) as mnth , DAY(@start_date) as dayofM , DATENAME(weekday, @start_date) as dtname UNION ALL SELECT DATEADD(DAY, 1, DtDay) , MONTH(DATEADD(DAY, 1, DtDay)) as mnth , DAY(DATEADD(DAY, 1, DtDay)) as dayofM , DATENAME(weekday,DATEADD(DAY, 1, DtDay)) as dtname FROM AllDays WHERE DtDay < @end_date )INSERT INTO @Nwkdy SELECT DtDay FROM AllDays where ( (mnth =1 and dayofM = 1) -- new years or (mnth =5 and dayofM >= 25 and dtname = 'Monday') -- memorial day or (mnth =7 and dayofM = 4) -- Indendence day or (mnth =9 and dayofM <= 7 and dtname = 'Monday') -- Labour day or (mnth =11 and dayofM between 22 and 28 and dtname = 'Thursday') -- Thanksgiving or (mnth =12 and dayofM = 25) -- Xmas ) OPTION (MAXRECURSION 0)[/code]Any help would be appreciated.AGAIN : This runs perfectly fine in JasperSoft Studio Preview AND SQL Server Management Studio. Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now