koti.raavi (6/27/2016)
Please find the queryCREATE TABLE #NEXTMONTHS (
Months DATE
,[Month No] INT
,[year] INT
,FYYear CHAR(10)
)
DECLARE @StartDate AS DATE
DECLARE @EndDate AS DATE
DECLARE @CurrentDate AS DATE
DECLARE @ID INT = 0
SET @StartDate = GETDATE()
SET @EndDate = DATEADD(YY, 2, GETDATE())
Declare
WHILE (@StartDate < @EndDate)
BEGIN
INSERT INTO #NEXTMONTHS
SELECT DATEADD(MM, 0 + @ID, DATEADD(MM, DATEDIFF(MM, 0, CURRENT_TIMESTAMP), 0))
,month(DATEADD(MM, 0 + @ID, DATEADD(MM, DATEDIFF(MM, 0, CURRENT_TIMESTAMP), 0)))
,year(DATEADD(MM, 0 + @ID, DATEADD(MM, DATEDIFF(MM, 0, CURRENT_TIMESTAMP), 0)))
,
--'FY' + cast(right(year (DATEADD(MM, 0 + @ID, DATEADD(MM, DATEDIFF(MM, 0, CURRENT_TIMESTAMP), 0))),2)as char(2)),
CASE
WHEN DATEPART(MM, DATEADD(MM, 0 + 1, DATEADD(MM, DATEDIFF(MM, 0, CURRENT_TIMESTAMP), 0))) >= 9
THEN 'FY' + CONVERT(NVARCHAR(10), CONVERT(INT, SUBSTRING(CONVERT(NVARCHAR(10), DATEPART(YY, GETDATE())), 3, 2)) + 1)
WHEN DATEPART(MM, DATEADD(MM, 0 + @ID, DATEADD(MM, DATEDIFF(MM, 0, CURRENT_TIMESTAMP), 0))) < 9
THEN 'FY' + CONVERT(NVARCHAR(10), CONVERT(INT, SUBSTRING(CONVERT(NVARCHAR(10), DATEPART(YY, GETDATE())), 3, 2)))
WHEN DATEPART(MM, DATEADD(MM, 0 + @ID, DATEADD(MM, DATEDIFF(MM, 0, CURRENT_TIMESTAMP), 0))) >= 9
THEN 'FY' + CONVERT(NVARCHAR(10), CONVERT(INT, SUBSTRING(CONVERT(NVARCHAR(10), DATEPART(YY, GETDATE())), 3, 2)) + 2)
WHEN DATEPART(MM, DATEADD(MM, 0 + @ID, DATEADD(MM, DATEDIFF(MM, 0, CURRENT_TIMESTAMP), 0))) < 9
THEN 'FY' + CONVERT(NVARCHAR(10), CONVERT(INT, SUBSTRING(CONVERT(NVARCHAR(10), DATEPART(YY, GETDATE())), 3, 2)) + 1)
END AS FYYEAR
SET @ID = @ID + 1
SET @StartDate = CONVERT(VARCHAR(30), DATEADD(MM, 1, @StartDate), 101);
END
I need to get a output below
My Company Financial year will start from sep to aug
Suppose now month is 06 and year is 2016
6Month 2016-08th Month 2016 -- FY16
09Month 2016-08th Month 2017 -- FY17
09Month 2017-08th Month 2018 -- FY18
I'm looking for financial year color, any help greatly appreciated..Thanks
Oh my. None of it needs to be so complex and you certainly don't need a WHILE loop here.
First, build the following function. It has a great many uses and can be used to replace just about any WHILE loop that "increments" something...
CREATE FUNCTION [dbo].[fnTally]
/**********************************************************************************************************************
Purpose:
Return a column of BIGINTs from @ZeroOrOne up to and including @MaxN with a max value of 1 Trillion.
As a performance note, it takes about 00:02:10 (hh:mm:ss) to generate 1 Billion numbers to a throw-away variable.
Usage:
--===== Syntax example (Returns BIGINT)
SELECT t.N
FROM dbo.fnTally(@ZeroOrOne,@MaxN) t
;
Notes:
1. Based on Itzik Ben-Gan's cascading CTE (cCTE) method for creating a "readless" Tally Table source of BIGINTs.
Refer to the following URLs for how it works and introduction for how it replaces certain loops.
http://www.sqlservercentral.com/articles/T-SQL/62867/
http://sqlmag.com/sql-server/virtual-auxiliary-table-numbers
2. To start a sequence at 0, @ZeroOrOne must be 0 or NULL. Any other value that's convertable to the BIT data-type
will cause the sequence to start at 1.
3. If @ZeroOrOne = 1 and @MaxN = 0, no rows will be returned.
5. If @MaxN is negative or NULL, a "TOP" error will be returned.
6. @MaxN must be a positive number from >= the value of @ZeroOrOne up to and including 1 Billion. If a larger
number is used, the function will silently truncate after 1 Billion. If you actually need a sequence with
that many values, you should consider using a different tool. ;-)
7. There will be a substantial reduction in performance if "N" is sorted in descending order. If a descending
sort is required, use code similar to the following. Performance will decrease by about 27% but it's still
very fast especially compared with just doing a simple descending sort on "N", which is about 20 times slower.
If @ZeroOrOne is a 0, in this case, remove the "+1" from the code.
DECLARE @MaxN BIGINT;
SELECT @MaxN = 1000;
SELECT DescendingN = @MaxN-N+1
FROM dbo.fnTally(1,@MaxN);
8. There is no performance penalty for sorting "N" in ascending order because the output is explicity sorted by
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
Revision History:
Rev 00 - Unknown - Jeff Moden
- Initial creation with error handling for @MaxN.
Rev 01 - 09 Feb 2013 - Jeff Moden
- Modified to start at 0 or 1.
Rev 02 - 16 May 2013 - Jeff Moden
- Removed error handling for @MaxN because of exceptional cases.
Rev 03 - 22 Apr 2015 - Jeff Moden
- Modify to handle 1 Trillion rows for experimental purposes.
**********************************************************************************************************************/
(@ZeroOrOne BIT, @MaxN BIGINT)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN WITH
E1(N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1) --10E1 or 10 rows
, E4(N) AS (SELECT 1 FROM E1 a, E1 b, E1 c, E1 d) --10E4 or 10 Thousand rows
,E12(N) AS (SELECT 1 FROM E4 a, E4 b, E4 c) --10E12 or 1 Trillion rows
SELECT N = 0 WHERE ISNULL(@ZeroOrOne,0)= 0 --Conditionally start at 0.
UNION ALL
SELECT TOP(@MaxN) N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E12 -- Values from 1 to @MaxN
;
With that function in place, this task (and more) becomes child's play.
WITH cteMonths AS
(
SELECT Months = CAST(DATEADD(mm,DATEDIFF(mm,0,CURRENT_TIMESTAMP)+t.N,0) AS DATE)
FROM dbo.fnTally(0,24) t
)
SELECT Months
,MonthNo = DATEPART(mm,Months)
,Year = DATEPART(yy,Months)
,FYYear = 'FY'+DATENAME(yy,DATEADD(mm,4,Months))
,FYMonth = DATEPART(mm,DATEADD(mm,4,Months))
FROM cteMonths
;
As to the particular out you posted, you'll have to explain that a bit. I see no reasonable pattern there. Once we know that, we can easily use the calendar generator above to easily produce the desired output.
--Jeff Moden
Change is inevitable... Change for the better is not.