CODEWITHSUNDEEP
sqlsql-server
MswatiLomnyama
MswatiLomnyama

Reputation: 1386

How to use multiple CTEs on separate SQL statements in SQL server?

I'm getting an error: Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

declare @StartDate as date = '20160301';        -- Date from which the calendar table will be created.
declare @EndDate as date = '20160331';      -- Calendar table will be created up to this date (not including).

declare @PayDateDay int = 7; --Made 7 because sql uses sunday as 1 and our enum uses 0

    WITH months(MonthNumber) AS
    (
            SELECT 0
            UNION ALL
            SELECT MonthNumber+1 
            FROM months
            WHERE MonthNumber < DATEDIFF(WEEK, @StartDate, @EndDate)
    )

    SELECT d.Saturday
        FROM (SELECT DATEADD(day,  @PayDateDay-DATEPART(WeekDay, @StartDate), DATEADD(week, MonthNumber, @StartDate)) As Saturday 
                FROM months
        ) d 
    WHERE Saturday < @EndDate


        WITH months(MonthNumber) AS
    (
            SELECT 0
            UNION ALL
            SELECT MonthNumber+1 
            FROM months
            WHERE MonthNumber < DATEDIFF(WEEK, @StartDate, @EndDate)
    )

    SELECT d.Saturday
        FROM (SELECT DATEADD(day,  @PayDateDay-DATEPART(WeekDay, @StartDate), DATEADD(week, MonthNumber, @StartDate)) As Saturday 
                FROM months
        ) d 
    WHERE Saturday < @EndDate

I intentionally want the same two sets of results.

Upvotes: 1

Views: 596

Answers (2)

Chris Pickford
Chris Pickford

Reputation: 8991

From T-SQL BOL: WITH common_table_expression (Transact-SQL)

When a CTE is used in a statement that is part of a batch, the statement before it must be followed by a semicolon.

You have multiple statements (your two SELECT queries) in a batch so therefore will need to terminate them properly with a semicolon ;. E.g.

...
WHERE Saturday < @EndDate;

WITH months(MonthNumber) AS
...

Upvotes: 2

Ruslan K.
Ruslan K.

Reputation: 1981

declare @StartDate as date = '20160301';        -- Date from which the calendar table will be created.
declare @EndDate as date = '20160331';      -- Calendar table will be created up to this date (not including).

declare @PayDateDay int = 7; --Made 7 because sql uses sunday as 1 and our enum uses 0

WITH months(MonthNumber) AS
(
        SELECT 0
        UNION ALL
        SELECT MonthNumber+1 
        FROM months
        WHERE MonthNumber < DATEDIFF(WEEK, @StartDate, @EndDate)
)
SELECT d.Saturday
    FROM (SELECT DATEADD(day,  @PayDateDay-DATEPART(WeekDay, @StartDate), DATEADD(week, MonthNumber, @StartDate)) As Saturday 
            FROM months
    ) d 
WHERE Saturday < @EndDate;


WITH months(MonthNumber) AS
(
        SELECT 0
        UNION ALL
        SELECT MonthNumber+1 
        FROM months
        WHERE MonthNumber < DATEDIFF(WEEK, @StartDate, @EndDate)
)
SELECT d.Saturday
    FROM (SELECT DATEADD(day,  @PayDateDay-DATEPART(WeekDay, @StartDate), DATEADD(week, MonthNumber, @StartDate)) As Saturday 
            FROM months
    ) d 
WHERE Saturday < @EndDate;

Upvotes: 1

Related Questions