Help Needed in Date logic

• KGJ-Dev

  SSCertifiable

  Points: 5780

  More actions

  December 20, 2018 at 8:12 am

  #416574

  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.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  December 20, 2018 at 8:56 am

  #2016960

  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];


• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  December 20, 2018 at 8:56 am

  #2016962

  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

• KGJ-Dev

  SSCertifiable

  Points: 5780

  More actions

  December 20, 2018 at 8:59 am

  #2016965

  Thanks a lot lynn and Chris. much appreciated