Home 2018 May SQL Puzzle: Use Recursive CTE, and list out the Years from Dates

SQL Puzzle: Use Recursive CTE, and list out the Years from Dates

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

Check the below input data and expected output for getting the list of years between given dates. Use recursive CTE to achieve this.

Input Date Range: 2010-01-01 to 2018-01-01
Expected Output:

Solution:

Please try the different solution for this puzzle and share it via comment...

Anvesh Patel
SQL Server: Set the default value to IMAGE Column or Binary DataSQL Puzzle: Generate Calendar Data for 19th Century
Comments: 3
  1. Dinesh
    May 26, 2018 at 8:08 am

    Declare @StartDate DateTime,@EndDate DateTime;
    Set @StartDate=’20100101′
    Set @EndDate=’20180101′

    ;
    WITH CTE
    As
    (Select Year(Convert(DateTime,@StartDate,112)) As YearOfBIll
    Union All
    Select YearOfBIll+1
    From CTE
    Where YearOfBill<Year(Convert(DateTime,@EndDate,112))
    )
    Select * From CTE

    ReplyCancel
  2. Prakash Annadurai
    January 14, 2019 at 12:15 am

    Here is my solution

    Drop table if exists #Records
    Create table #Records
    (YearColumn date)

    INSERT INTO #Records
    VALUES
    (‘2004-01-01’)
    ,(‘2009-01-01’)
    ,(‘2012-01-01’)
    ,(‘2013-01-01’)
    ,(‘2016-01-01’)
    ,(‘2017-01-01’)

    With Res as
    (
    SELECT yearColumn, lead(YearColumn) over(Order by YearColumn) as NextVal
    From #Records
    )
    select Year(Dateadd(year,1,YearColumn)),Year(Dateadd(year,-1,NextVal))
    from Res
    Where datediff(year,YearColumn,NextVal)>1
    Order by YearColum

    ReplyCancel
  3. HIC
    April 17, 2019 at 9:59 am

    pl/sql version:

    with years(fyear) as (
    select extract(year from to_date(‘01.01.2010’, ‘dd.mm.yyyy’)) as fyear from dual
    union all
    select fyear + 1 from years where fyear < extract(year from to_date('01.01.2018', 'dd.mm.yyyy'))
    )
    select * from years;

    ReplyCancel

Leave a Reply to Dinesh Cancel reply

CAPTCHA

*

Anvesh Patel
Anvesh Patel

Database Engineer

May 14, 2018 3 Comments SQL Puzzle, , , , , , , , , ,