CODEWITHSUNDEEP
sql-server
fafnir1990
fafnir1990

Reputation: 185

How to declare variable from WITH ... SELECT statement

I have a problem with variable definition in MS SQL SERVER. Could you give me any hint, how can I assign the result of count function to variable @WorkingDays ?

DECLARE @StartDate DATE = '2017-03-29';
DECLARE @EndDate DATE = '2017-05-05';
DECLARE @WorkingDays INT;

    WITH DateRange(DateData) AS 
    (
        SELECT @StartDate as Date
        UNION ALL
        SELECT DATEADD(d,1,DateData)
        FROM DateRange 
        WHERE DateData < @EndDate
    )
    SELECT COUNT(DateData)
    FROM DateRange
    WHERE DATEPART(dw, DateData) not in (1, 7)
    OPTION (MAXRECURSION 0)

I`ve tried to use 

set @WorkingDays = (WITH.... OPTION (MAXRECURSION 0) )

but it returns an error (incorrect syntax)

I will be grateful for any hint

Best regards

Upvotes: 0

Views: 257

Answers (2)

Mr.Bhanushali
Mr.Bhanushali

Reputation: 126

you can follow this method:

set @workingDays = (SELECT COUNT(DateData) FROM DateRange
                   WHERE DATEPART(dw, DateData) not in (1, 7)
                   OPTION (MAXRECURSION 0))

Upvotes: 1

Arvo
Arvo

Reputation: 10580

SELECT @WorkingDays = COUNT(DateData) ...

Upvotes: 4

Related Questions