Getting extra dates while using cte

Question

;WITH cte(Start_Date, End_Date) AS
(  
    SELECT 
        CAST(StartDateUtc AS DATE),
        CAST(CASE 
                WHEN BusinessGoal.EndDateUtc IS NULL 
                   THEN GETDATE() 
                ELSE CAST(BusinessGoal.EndDateUtc AS DATE) 
             END AS DATE) 
    FROM 
        BusinessGoal
    WHERE 
        BusinessGoal.Period = 'Year'

    UNION ALL

    SELECT 
        DATEADD(yy, 1, Start_Date), End_Date 
    FROM 
        cte 
    WHERE 
        Start_Date < End_Date
)
SELECT * FROM cte

I'm getting this result.

Start_Date  End_Date
-----------------------
2019-06-07  2019-08-16
2019-07-25  2019-08-16
2020-07-25  2019-08-16
2020-06-07  2019-08-16

I need this.

Start_Date  End_Date
----------------------
2019-06-07  2019-08-16
2019-07-25  2019-08-16

Show us sample data too, the one producing the above result. — jarlh, Aug 16 '19 at 13:56
I think the issue is with your UNION ALL SELECT DATEADD(yy, 1,Start_Date), End_Date FROM cte WHERE Start_Date < End_Date statement - it's what's generating your 2020 start dates... — Chris Hackett, Aug 16 '19 at 13:56
Why not adding another where clause, AND YEAR(Start_Date) = CURRENT_YEAR, based upon your product. Still I don't think the answer is possible without sample data. — Ankit Bajpai, Aug 16 '19 at 14:02
SQL tag is not meant to indicate SQL Server (MSSQL) on this website, hover your mouse on the tag then you know the meaning of the tag.. I assume you are using SQL Server because i notice the function `GETDATE()` — Raymond Nijland, Aug 16 '19 at 14:05
@AnkitBajpai . . . Please explain the logic you want to implement. A recursive CTE is not needed for the result you want. — Gordon Linoff, Aug 16 '19 at 14:07
to add to @GordonLinoff 's comment a CTE seams not even to be required unless i mis something very obvious here .. But see [Why should I provide a Minimal Reproducible Example for a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query) to read how to provide example data and expected results.. — Raymond Nijland, Aug 16 '19 at 14:09
@GordonLinoff, As i have already commented, The exactly correct answer is not possible without sample data. — Ankit Bajpai, Aug 16 '19 at 14:09
Unrelated, but the semi-colon belongs _after_ `...FROM cte`, not before `WITH`. It's a statement terminator, and, to quote several who have come before me, not a statement begininator. (I know Aaron Bertrand does it, but he's an outlier and has a [good reason](https://sqlblog.org/2009/09/03/ladies-and-gentlemen-start-your-semi-colons)) — Eric Brandt, Aug 16 '19 at 14:21

GarethD · Accepted Answer · 2019-08-16T14:21:30.790

The problem is your WHERE clause:

SELECT DATEADD(yy, 1,Start_Date), End_Date FROM cte 
WHERE Start_Date < End_Date   -- PROBLEM IS HERE

It looks like the two rows that come out of your anchor query are:

Start_Date  End_Date
--------------------------
2019-06-07  2019-08-16
2019-07-25  2019-08-16

Then you plug these two rows into the recursive member, and since the condition start_date < End_date is met, you generate two more rows, with a year added to the start date. I think you need to check whether the start date plus one year is after the end date, e.g.

;WITH cte(Start_Date, End_Date)
AS
  (  
    SELECT CAST(StartDateUtc as Date),
    CAST(CASE WHEN BusinessGoal.EndDateUtc is NULL THEN GETDATE() else cast(BusinessGoal.EndDateUtc as Date) end as Date) FROM BusinessGoal
    WHERE BusinessGoal.Period = 'Year'
    UNION ALL
    SELECT DATEADD(yy, 1,Start_Date), End_Date FROM cte 
    WHERE DATEADD(yy, 1,Start_Date) < End_Date -- check the start date plus one year
  )

SELECT * FROM cte

As an aside, although it won't make much of a difference for a small data set, using a recursive CTE to generate series in this way is one of the worst ways of doing it. There are much better ways described here: Generate a set or series without loops - part 1. Also, this query would become much easier if you had a Calendar table.

SELECT  StartDate = c.Date,
        EndDate = CAST(ISNULL(bg.EndDateUtc, GETDATE()) AS DATE)
FROM    BusinessGoal AS bg
        INNER JOIN dbo.Calendar AS c
            ON c.Date >= bg.StartDateUtc
            AND c.CalendarDay = DATEPART(DAY, bg.StartDateUtc)
            AND c.CalendarMonth = DATEPART(MONTH, bg.StartDateUtc)
            AND c.Date <= ISNULL(bg.EndDateUtc, GETDATE())
WHERE   bg.Period = 'Year';

If you don't have a calendar table, you can also do this with a numbers table created on the fly. e.g.

-- Generate a list of numbers from 0-999
WITH Numbers (Number) AS
(   SELECT  ROW_NUMBER() OVER(ORDER BY n1.N) - 1
    FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n1 (N)
    CROSS JOIN (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n2 (N)
    CROSS JOIN (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n3 (N)
), 
-- Dummy Data
BusinessGoal AS
(   SELECT  x.ID,
            StartDateUtc = CONVERT(DATE, x.StartDateUtc), 
            EndDateUtc = CONVERT(DATE, x.EndDateUtc),
            Period = 'Year'
    FROM    (VALUES 
                (1, '20190607', '20190816'), 
                (2, '20190725', '20190816'), 
                (3, '20180725', NULL)
            ) X (ID, StartDateUtc, EndDateUtc)
)
SELECT  ID,
        StartDate = DATEADD(YEAR, n.Number, bg.StartDateUtc),
        EndDate = CAST(ISNULL(bg.EndDateUtc, GETDATE()) AS DATE)
FROM    BusinessGoal AS bg
        INNER JOIN Numbers AS n
            ON DATEADD(YEAR, n.Number, bg.StartDateUtc) < ISNULL(bg.EndDateUtc, GETDATE());

While this looks much more complicated, most of the query is in generating sample data, if you had a permanent numbers table (or view) the query is as simple as:

SELECT  StartDate = DATEADD(YEAR, n.Number, bg.StartDateUtc),
        EndDate = CAST(ISNULL(bg.EndDateUtc, GETDATE()) AS DATE)
FROM    BusinessGoal AS bg
        INNER JOIN Numbers AS n
            ON DATEADD(YEAR, n.Number, bg.StartDateUtc) < ISNULL(bg.EndDateUtc, GETDATE());

And will perform much better than a recursive CTE on larger data sets, or as you get dates that are further apart.

Gordon Linoff · Answer 2 · 2019-08-16T14:03:25.353

1

You seem to simply want:

select convert(date, Start_DateUTC) as start_date, 
       convert(date, coalesce(End_DateUTC, getdate()) as end_date
from BusinessGoal bg;

I don't see what the recursive CTE has to do with this problem.

edited Aug 16 '19 at 14:03

answered Aug 16 '19 at 14:02

Gordon Linoff

1,242,037
58
646
786

1

Gordon Linoff, Distinct will not solve the issue as the Year are different. Start_date is already unique. – Ankit Bajpai Aug 16 '19 at 14:03

score 1 · Answer 3 · answered Aug 16 '19 at 14:04

1

Try to change the condition of the recursive part of the cte:

SELECT DATEADD(yy, 1,Start_Date), End_Date FROM cte 
WHERE DATEADD(yy, 1,Start_Date) < End_Date

This way, you validate the result, not the base data.

answered Aug 16 '19 at 14:04

lakta

278
1
9

Getting extra dates while using cte

3 Answers3