June 27, 2016 at 6:05 am
Please find the query
CREATE 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
June 27, 2016 at 6:17 am
Work out how many days there are from the end of the financial year to the end of the calendar year. Now add that number of days to each date and take the year part of the result to give you the financial year.
John
June 27, 2016 at 8:02 am
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.
June 28, 2016 at 12:07 am
Thanks Jeff for quickly reply. Great Job 🙂
July 11, 2016 at 8:46 pm
koti.raavi (6/28/2016)
Thanks Jeff for quickly reply. Great Job 🙂
You bet. Thank you for the feedback.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply