December 20, 2018 at 8:12 am
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.
December 20, 2018 at 8:56 am
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];
December 20, 2018 at 8:56 am
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
December 20, 2018 at 8:59 am
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