locked
Generate Dates List RRS feed

  • Question

  • Hi Friends,

    I have a requirement where I need to generate a table with number of dates into it.

    example: I have DISTINCT Value dates with :

    2017-08-29

    2017-09-08

    2017-09-11

    Now, I need to generate a list where for each above date I need something like:

    2017-08-29        2016-08-30

    2017-8-29         2016-08-31

    2017-8-29         2016-09-01

    ....

    2017-8-29         2017-08-29

    (total 365 records, basically from 2017-08-29 go 1 year back and publish all dates. This we need to do for all above 3 dates or list of dates that we will get using distinct)

    I am trying to use CTE but not able to get the gist of it. Any help / clue will be highly appreciated.

    Regards


    Regards Gursethi Blog: http://gursethi.blogspot.com/ ++++ Please mark "Propose As Answer" if my answer helped ++++


    • Edited by GURSETHI Monday, February 5, 2018 4:03 AM update
    Monday, February 5, 2018 4:02 AM

Answers

  • Thanks Will,

    I worked on another theory

    ;WITH 
    	SourceDateRange AS (SELECT DISTINCT value_date,DATEADD(DAY,+1,DATEADD(YEAR,-1,Value_Date)) AS TradeStartDate FROM fact.trade_details_murex 
    
    )
    ,	CTEDate AS
          (
    			SELECT value_date, DATEADD(DAY,+1,DATEADD(YEAR,-1,value_date)) AS TradeStartDate From SourceDateRange
    			UNION ALL
    			SELECT value_date, DATEADD(DAY,+1,TradeStartDate)  FROM CTEDate  WHERE value_date > TradeStartDate
    		) 
    SELECT * FROM CTEDate ORDER BY value_date, TradeStartDate
    option (maxrecursion 0)
    

    This is working like charm.

    Thanks


    Regards Gursethi Blog: http://gursethi.blogspot.com/ ++++ Please mark "Propose As Answer" if my answer helped ++++

    • Marked as answer by GURSETHI Monday, February 5, 2018 5:13 AM
    Monday, February 5, 2018 5:13 AM

All replies

  • Hi All,

    I guess I found how to do this.

    CREATE TABLE #T (ValueDate DATE)
    INSERT INTO #T Values
    (
    '2017-08-28',
    '2017-09-08',
    '2017-09-11',
    '2017-09-12',
    '2018-01-30'
    )
    
    SELECT * FROM #T 
    GO
    
    DECLARE @ValueDate DATE, @TradeStartDate DATE
    
    DECLARE DateCursor CURSOR FOR SELECT ValueDate FROM #T --WHERE ValueDate='2017-08-28'
    OPEN DateCursor
    FETCH NEXT FROM DateCursor INTO @ValueDate
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
    ;WITH ctedaterange (ValueDate, TradeStartDate)
         AS (
    			SELECT ValueDate, DATEADD(DAY,+1,DATEADD(YEAR,-1,ValueDate)) AS TradeStartDate From #T Where ValueDate=  @ValueDate
    			UNION ALL
    			SELECT A.ValueDate, DATEADD(DAY,+1,A.TradeStartDate)  FROM #T B  CROSS JOIN ctedaterange A WHERE A.TradeStartDate<A.ValueDate AND B.ValueDate = @ValueDate AND A.ValueDate = @ValueDate
    		) 
    SELECT * FROM ctedaterange
    option (maxrecursion 0)
    FETCH NEXT FROM DateCursor INTO @ValueDate
    END
    CLOSE DateCursor
    DEALLOCATE DateCursor
    

    Let me know if there is there is any other more optimised way of doing this.

    Regards



    Regards Gursethi Blog: http://gursethi.blogspot.com/ ++++ Please mark "Propose As Answer" if my answer helped ++++

    Monday, February 5, 2018 4:34 AM
  • Hi GURSETHI,

    Please try this.

    CREATE TABLE TestA1 (ValueDate DATE)
    INSERT INTO TestA1 Values
    ('2017-08-28'),
    ('2017-09-08'),
    ('2017-09-11'),
    ('2017-09-12'),
    ('2018-01-30')
    
    declare @v_min_date date
    declare @v_max_date date
    
    select @v_min_date=min(ValueDate),@v_max_date=max(ValueDate) from TestA1
    
    ;with cte_dates as
    (
      select dateadd(DAY,1,DATEADD(Year,-1,@v_min_date)) as v_date
      union all
      select DATEADD(day,1,v_date)  from cte_dates where v_date<@v_max_date
    )
    select 
    T.ValueDate,T1.v_date
    from TestA1 T
    CROSS APPLY (SELECT v_date  FROM cte_dates where v_date between dateadd(DAY,1,DATEADD(Year,-1,T.ValueDate)) and T.ValueDate ) T1
    order by T.ValueDate,T1.v_date
    option (maxrecursion 0)
    

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, February 5, 2018 5:11 AM
  • Thanks Will,

    I worked on another theory

    ;WITH 
    	SourceDateRange AS (SELECT DISTINCT value_date,DATEADD(DAY,+1,DATEADD(YEAR,-1,Value_Date)) AS TradeStartDate FROM fact.trade_details_murex 
    
    )
    ,	CTEDate AS
          (
    			SELECT value_date, DATEADD(DAY,+1,DATEADD(YEAR,-1,value_date)) AS TradeStartDate From SourceDateRange
    			UNION ALL
    			SELECT value_date, DATEADD(DAY,+1,TradeStartDate)  FROM CTEDate  WHERE value_date > TradeStartDate
    		) 
    SELECT * FROM CTEDate ORDER BY value_date, TradeStartDate
    option (maxrecursion 0)
    

    This is working like charm.

    Thanks


    Regards Gursethi Blog: http://gursethi.blogspot.com/ ++++ Please mark "Propose As Answer" if my answer helped ++++

    • Marked as answer by GURSETHI Monday, February 5, 2018 5:13 AM
    Monday, February 5, 2018 5:13 AM
  • if you want you can also use a number table approach for this

    like this

    SELECT t.DateColumn,
    DATEADD(dd,-1 * (v.number-1),t.DateColumn) AS NewDate
    FROM YourTable t
    CROSS JOIN master..spt_values v
    WHERE v.type = 'p'
    AND v.number <= 366
    ORDER BY DateColumn,NewDate

    I've used the system table spt_values to act as number table here. If you want you can generate a number table of your own if you dont want to rely on spt_values

    see this article to understand what a Number table is and its uses to avoid loops in many scenarios

    http://www.sqlservercentral.com/articles/T-SQL/62867/


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Monday, February 5, 2018 5:53 AM