Help Needed in Date logic

  • Hi,

    Below is my sample script

    DECLARE @AppDate DATETIME = '2014-01-01'
        DECLARE @Months TABLE (
            Month VARCHAR(10)
            ,startdate DATETIME
            ,enddate DATETIME
            );
    INSERT INTO @Months (
                Month
                ,startdate
                ,enddate
                )
            SELECT UPPER(convert(VARCHAR(3), datename(month, DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) - N, 0)))) Month
                ,DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) - N, 0) startdate
                ,DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) - N + 1, 0) - 1 enddate
            FROM (
                VALUES (0)
                    ,(1)
                    ,(2)
                    ,(3)
                    ,(4)
                    ,(5)
                    ,(6)
                    ,(7)
                    ,(8)
                    ,(9)
                    ,(10)
                    ,(11)
                    ,(12)
                    ,(13)
                    ,(14)
                    ,(15)
                    ,(16)
                    ,(17)
                    ,(18)
                    ,(19)
                    ,(20)
                    ,(21)
                    ,(22)
                    ,(23)
                    ,(24)
                ) x(N)
                WHERE N <= DATEDIFF(MONTH, @AppDate, GETDATE());
            

            select * from @Months
    the issue what am facing is, it just only give 24 months of data. how can i make this to show based on AppDate. fro example, if the AppDate is 2014-01-01 then it has to show the month,startdate of the month, enddate of the month from 2014-01-01 to current date. Any correction in my query please.

  • You need to use a tally table.  In the code below I create one as part of the query.

    declare @AppDate datetime = '2014-01-01';

    with e1(n) as (select 1 from (values (0),(0),(0),(0),(0),(0),(0),(0),(0),(0))dt(n))
      ,e2(n) as (select 1 from e1 a cross join e1 b)
      ,e4(n) as (select 1 from e2 a cross join e2 b)
      ,eTally(n) as (select 0 union select top (datediff(month, @AppDate,getdate())) row_number() over (order by (select null)) from e2 a cross join e4 b)
    select [Month]  = upper(convert(varchar(3), datename(month, dateadd(MM, datediff(MM, 0, getdate()) - [eTally].[n], 0))))
      , [startdate] = dateadd(MM, datediff(MM, 0, getdate()) - [eTally].[n], 0)
      , [enddate] = dateadd(MM, datediff(MM, 0, getdate()) - [eTally].[n] + 1, 0) - 1
    from [eTally]
    order by [startdate];

  • DECLARE @AppDate DATETIME = '2014-01-01'
    SELECT
     [Month] = UPPER(LEFT(DATENAME(month,StartDate),3)),
     StartDate,
     [enddate] = CAST(EOMONTH(StartDate) AS DATETIME)
    FROM (
     SELECT TOP(DATEDIFF(MONTH, @AppDate, GETDATE())+1)
      StartDate = DATEADD(MONTH, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) -1, @AppDate)
     FROM
      (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d1 (n),
      (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d2 (n)
    ) d
    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • Thanks a lot lynn and Chris. much appreciated

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply