Using Recursive query in function

Question

I have this query developed with the help of a guy here and I am not able to use this in function. There is some sort of Syntax issue.

Here is the query

WITH CTE AS (
    SELECT @STARTDATE AS STARTDATE
    UNION ALL
    SELECT DATEADD(D,1,STARTDATE) 
    FROM CTE
    WHERE STARTDATE <@ENDDATE
),
WORKINGDAYS AS (
    SELECT STARTDATE,
           DATENAME(DW,STARTDATE)WEEKDAYS,
           C1.CalanderDayName AS isweekend
    FROM CTE c
         LEFT JOIN HRM.tbl_Calendar C1 ON DATENAME(DW,STARTDATE) = C1.CalanderDayName
                                      AND C1.IsOffDay = 1
)

SELECT COUNT(WEEKDAYS)as WORKINGDAYS
FROM WORKINGDAYS
WHERE isweekend IS NULL;

I want to create a function named fnGetWorkingDays

ALTER FUNCTION [dbo].[fnGetWorkingDays] (@StartDate datetime, @EndDate datetime)
RETURNS int
AS
BEGIN
     DECLARE @dateFrom datetime 
     DECLARE @dateTo datetime 
     SET @dateFrom = @StartDate 
     SET @dateTo = @EndDate

     DECLARE @WORKDAYS INT
     SELECT @WORKDAYS =

;WITH CTE AS (
SELECT @STARTDATE  AS STARTDATE
UNION ALL
select DATEADD(D,1,STARTDATE) 
FROM CTE
WHERE STARTDATE <@ENDDATE
)
,WORKINGDAYS AS (
SELECT STARTDATE,DATENAME(DW,STARTDATE)WEEKDAYS, C1.CalanderDayName AS isweekend
FROM CTE c
LEFT JOIN HRM.tbl_Calendar C1 ON DATENAME(DW,STARTDATE)=C1.CalanderDayName AND C1.IsOffDay=1
)

SELECT COUNT(WEEKDAYS)as WORKINGDAYS FROM WORKINGDAYS WHERE isweekend is null

     RETURN @WORKDAYS
END

It's probably the ; at the start; but please post the full create function code & the error you get, thanks. — Gareth Lyons
– Gareth Lyons, Commented Feb 13, 2018 at 12:48
"Some sort of Syntax issue."? Could you elaborate? Are you getting an error? If so, what so? — Thom A
– Thom A ♦, Commented Feb 13, 2018 at 12:48
@Larnu Yes I'm editing a question and exactly I'm getting semicolon error — Doonie Darkoo
– Doonie Darkoo, Commented Feb 13, 2018 at 12:52
"a semicolon error"? You haven't put the error in your post. Please put it in. — Thom A
– Thom A ♦, Commented Feb 13, 2018 at 12:55
This now looks like you're trying to reinvent the wheel. There are a lot of examples out there on how to get the number of working days between 2 dates, and the good one's use a inline table-value function, not a scalar function (which perform poorly). — Thom A
– Thom A ♦, Commented Feb 13, 2018 at 13:00

Sandip - Frontend Developer · Accepted Answer · 2018-02-13 13:19:19Z

3

Try With this Below function .let me know back for any errors

CREATE FUNCTION [dbo].[fnGetWorkingDays] (@StartDate datetime, @EndDate datetime)
RETURNS int
AS
BEGIN 

     DECLARE @WORKDAYS INT


;WITH CTE AS (
    SELECT @STARTDATE AS STARTDATE
    UNION ALL
    SELECT DATEADD(D,1,STARTDATE) 
    FROM CTE
    WHERE STARTDATE <@ENDDATE
),
WORKINGDAYS AS (
    SELECT STARTDATE,
           DATENAME(DW,STARTDATE)WEEKDAYS,
           C1.CalanderDayName AS isweekend
    FROM CTE c
         LEFT JOIN HRM.tbl_Calendar C1 ON DATENAME(DW,STARTDATE) = C1.CalanderDayName
                                      AND C1.IsOffDay = 1
)

SELECT @WORKDAYS=COUNT(WEEKDAYS) FROM WORKINGDAYS WHERE isweekend is null

     RETURN @WORKDAYS
END

edited Feb 13, 2018 at 13:19

Sandip - Frontend Developer

14.7k5 gold badges38 silver badges62 bronze badges

answered Feb 13, 2018 at 13:00

Mahesh.K

9016 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Sandip - Frontend Developer Over a year ago

what is the diff between my answer and your one?

Doonie Darkoo Over a year ago

Yes it worked too. I got the idea now where I was going wrong. Thank you again

Mahesh.K Over a year ago

why down vote .i tried the query my own way without seeing yours or copy/pasting it back . As OP mentioned that query developed with the help of a guy is developed by me For your information check stackoverflow.com/a/48765379/6122941 .Syntax of creating a function will be same everywhere .Won't varies user to user right . So obviously it looks similar .just understand that .Blindly make downvotes doesn't makes any sense .Hope you understand .Thanks

Mahesh.K Over a year ago

@DoonieDarkoo If you reached your expectation accept the answer reward back us . Great to help you . Happy Coding -:)

Doonie Darkoo Over a year ago

Yes I did and thank you. Also I have commented on previous post please check that too

|

Jayasurya Satheesh · Accepted Answer · 2018-02-13 13:05:35Z

You are getting the Error because your Assignment operation for the Variable @WORKDAYS is Wrong. Change it Like This

ALTER FUNCTION [dbo].[fnGetWorkingDays]
(
    @StartDate DATETIME,
    @EndDate DATETIME
)
RETURNS INT
AS
BEGIN
    DECLARE @dateFrom DATETIME;
    DECLARE @dateTo DATETIME;
    SET @dateFrom = @StartDate;
    SET @dateTo = @EndDate;
    DECLARE @WORKDAYS INT;

    WITH CTE
    AS 
    (
       SELECT 
          @STARTDATE AS STARTDATE
       UNION ALL
       SELECT 
          DATEADD(D, 1, STARTDATE)
          FROM CTE
             WHERE STARTDATE < @ENDDATE
    ),WORKINGDAYS
    AS 
    (
       SELECT
          DATENAME(DW, STARTDATE) WEEKDAYS,
          C1.CalanderDayName AS isweekend
          FROM CTE c
             LEFT JOIN HRM.tbl_Calendar C1 
                ON DATENAME(DW, STARTDATE) = C1.CalanderDayName
                    AND C1.IsOffDay = 1
    )
    SELECT 
       @WORKDAYS = COUNT(WEEKDAYS)--Asign Variable Here
       FROM WORKINGDAYS
          WHERE isweekend IS NULL;

    RETURN @WORKDAYS;

END;

Sandip - Frontend Developer · Accepted Answer · 2018-02-13 13:13:46Z

0

You have written wrong syntax to set @WORKDAYS, try below syntax

CREATE FUNCTION [dbo].[fnGetWorkingDays] (@StartDate datetime, @EndDate datetime)
RETURNS int
AS
BEGIN        

    DECLARE @WORKDAYS INT   

    ;WITH CTE AS (
    SELECT @STARTDATE  AS STARTDATE
    UNION ALL
    select DATEADD(D,1,STARTDATE) 
    FROM CTE
    WHERE STARTDATE <@ENDDATE
    )
    ,WORKINGDAYS AS (
    SELECT STARTDATE,DATENAME(DW,STARTDATE)WEEKDAYS, C1.CalanderDayName AS isweekend
    FROM CTE c
    LEFT JOIN HRM.tbl_Calendar C1 ON DATENAME(DW,STARTDATE)=C1.CalanderDayName AND C1.IsOffDay=1
    )

    SELECT @WORKDAYS=COUNT(WEEKDAYS) FROM WORKINGDAYS WHERE isweekend is null

     RETURN @WORKDAYS
END

edited Feb 13, 2018 at 13:13

answered Feb 13, 2018 at 12:54

Sandip - Frontend Developer

14.7k5 gold badges38 silver badges62 bronze badges

2 Comments

Thom A Over a year ago

Semicolons are Terminators, not "beginninators". you should be ending every line with a semicolon, ;, not starting the expressions that require the previous statement to be terminated with one.

Doonie Darkoo Over a year ago

Yes I am aware with that but I guess CTE expression requires previous expression to be terminated which I guess I have implemented in pretty much messy way. Thanks for the help though

Collectives™ on Stack Overflow

Using Recursive query in function

3 Answers 3

6 Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related