Fiscal year to date in SQL

Team - I have a date column which has data like "2015-05-01 00:00:00". This is a small date time type.

I need to populate fiscal year to date in a query (my fiscal starts from July to June). so typically for the above date, fiscal year query should populate date like below:

2015-05-01 00:00:00
2015-04-01 00:00:00
2015-03-01 00:00:00
2015-02-01 00:00:00
2015-01-01 00:00:00
2014-12-01 00:00:00
2014-11-01 00:00:00
2014-10-01 00:00:00
2014-09-01 00:00:00
2014-08-01 00:00:00
2014-07-01 00:00:00

Kindly help.
LVL 7
ManjuIT - Project ManagerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dsackerContract ERP Admin/ConsultantCommented:
Not knowing your field names, a query could go as follows:
DECLARE @FiscalYearEnd  datetime
SET @FiscalYearEnd = '2015-06-30'

SELECT  CONVERT(varchar(7), t.MyDate, 23)   AS FiscalMonth,  -- reduces yyyy-mm-dd to yyyy-mm
        SUM(t.MyAmount)                     AS FiscalAmount
FROM    MyTable t WITH (NOLOCK)
WHERE   CONVERT(date, t.MyDate) BETWEEN DATEADD(day, 1, DATEADD(year, -1, @FiscalYearEnd)) AND @FiscalYearEnd
GROUP BY CONVERT(varchar(7), t.MyDate, 23)

Open in new window

This makes a lot of assumptions. Seeing your dates were listed month by month, I'm assuming you want to SUM some amounts by fiscal month. The variable @FiscalYearEnd is one example of how to pass your fiscal year end value.
0
ManjuIT - Project ManagerAuthor Commented:
Sorry,

I am not doing any calculation in this table. Ex:

I have a table called, "Input_5929" where a specific column called NewTimeDateFormat which has the above date values.

What i need is, select distinct top 1 NewTimeDateformat from Input_5929 order by NewTimeDateFormat desc

which will give me one value, lets say, "2015-05-01 00:00:00"

Then my fiscal year to date query should give me the below value:

2015-05-01 00:00:00
2015-04-01 00:00:00
2015-03-01 00:00:00
2015-02-01 00:00:00
2015-01-01 00:00:00
2014-12-01 00:00:00
2014-11-01 00:00:00
2014-10-01 00:00:00
2014-09-01 00:00:00
2014-08-01 00:00:00
2014-07-01 00:00:00

but if my top 1 distinct value gives me "2015-07-01" Then fiscal year to date will give me "2015-07-01" only.
0
ManjuIT - Project ManagerAuthor Commented:
dsacker - Based on your query, I managed to get what i need. But i need the @FiscalyearEnd date should be dynamic based on my top 1 distinct query. possible?

DECLARE @FiscalYearEnd  datetime
SET @FiscalYearEnd = '2015-06-30'

SELECT  CONVERT(datetime, t.NewTimeDateFormat, 23)   AS FiscalMonth,
(Left(DATENAME(month, CONVERT(datetime, t.NewTimeDateFormat, 23)),3)+'-'+Right(Year(CONVERT(datetime, t.NewTimeDateFormat, 23)),2)) 
AS 'NewTimeCheck' FROM    Input_5929 t WITH (NOLOCK)
WHERE   CONVERT(date, t.NewTimeDateFormat) BETWEEN 
DATEADD(day, 1, DATEADD(year, -1, @FiscalYearEnd)) AND @FiscalYearEnd
GROUP BY CONVERT(datetime, t.NewTimeDateFormat, 23) order by FiscalMonth desc

Open in new window

0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

dsackerContract ERP Admin/ConsultantCommented:
Gotcha. I'm working out the query now. One moment.
0
dsackerContract ERP Admin/ConsultantCommented:
This should be along the right lines:
DECLARE @FiscalDateBeg  datetime,
        @FiscalDateEnd  datetime

SELECT TOP 1 @FiscalDateEnd = NewTimeDateFormat
FROM   Input_5929 WITH (NOLOCK)
ORDER BY NewTimeDateFormat DESC

SET @FiscalDateBeg = CONVERT(datetime, CONVERT(varchar(5), YEAR(@FiscalDateEnd) - 1) + '-07-01')
IF @FiscalDateEnd >= CONVERT(varchar(5), YEAR(GETDATE())) + '-07-01'
    SET @FiscalDateBeg = CONVERT(varchar(5), YEAR(GETDATE())) + '-07-01'

SELECT  YourFields,
       MoreFields,
        ...
FROM    YourTables
WHERE   YourDate BETWEEN @FiscalDateBeg AND @FiscalDateEnd

Open in new window

I assume you're querying some other tables, but using the maximum date from Input_5929 to set your boundaries. As you can see, I determine the @FiscalDateBeg based on the @FiscalDateEnd, which I get from your Input_5929 table.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dsackerContract ERP Admin/ConsultantCommented:
I had to make a small correction to my code above.
0
ManjuIT - Project ManagerAuthor Commented:
it says 'Date' is not a recognized built-in function name.
0
dsackerContract ERP Admin/ConsultantCommented:
I had to make a small correction.

Replace just these lines:

SET @FiscalDateBeg = CONVERT(datetime, CONVERT(varchar(5), YEAR(@FiscalDateEnd) - 1) + '-07-01')
IF @FiscalDateEnd >= CONVERT(varchar(5), YEAR(GETDATE())) + '-07-01'
    SET @FiscalDateBeg = CONVERT(varchar(5), YEAR(@FiscalDateEnd)) + '-07-01'
0
ManjuIT - Project ManagerAuthor Commented:
I updated your code with my query, but it gives me full months(12 months). doesnt stop at July.

DECLARE @FiscalDateBeg  datetime,
        @FiscalDateEnd  datetime

SELECT TOP 1 @FiscalDateEnd = NewTimeDateFormat
FROM   Input_5929 WITH (NOLOCK)
ORDER BY NewTimeDateFormat DESC

SET @FiscalDateBeg = CONVERT(datetime, CONVERT(varchar(5), YEAR(@FiscalDateEnd) - 1) + '-07-01')
IF @FiscalDateEnd >= CONVERT(varchar(5), YEAR(GETDATE())) + '-07-01'
    SET @FiscalDateBeg = CONVERT(varchar(5), YEAR(@FiscalDateEnd)) + '-07-01'

SELECT  CONVERT(datetime, t.NewTimeDateFormat, 23)   AS FiscalMonth,
(Left(DATENAME(month, CONVERT(datetime, t.NewTimeDateFormat, 23)),3)+'-'+Right(Year(CONVERT(datetime, t.NewTimeDateFormat, 23)),2)) 
AS 'NewTimeCheck' FROM    Input_5929 t WITH (NOLOCK)
WHERE   CONVERT(date, t.NewTimeDateFormat) BETWEEN 
DATEADD(day, 1, DATEADD(year, -1, @FiscalDateEnd)) AND @FiscalDateEnd
GROUP BY CONVERT(datetime, t.NewTimeDateFormat, 23) order by FiscalMonth desc

Open in new window



Ouput

FiscalMonth	NewTimeCheck
2015-03-01 00:00:00.000	Mar-15
2015-02-01 00:00:00.000	Feb-15
2015-01-01 00:00:00.000	Jan-15
2014-12-01 00:00:00.000	Dec-14
2014-11-01 00:00:00.000	Nov-14
2014-10-01 00:00:00.000	Oct-14
2014-09-01 00:00:00.000	Sep-14
2014-08-01 00:00:00.000	Aug-14
2014-07-01 00:00:00.000	Jul-14
2014-06-01 00:00:00.000	Jun-14
2014-05-01 00:00:00.000	May-14
2014-04-01 00:00:00.000	Apr-14

Open in new window

0
ManjuIT - Project ManagerAuthor Commented:
Gotcha.., changed the between date's. Its working like a charm.


DECLARE @FiscalDateBeg  datetime,
        @FiscalDateEnd  datetime

SELECT TOP 1 @FiscalDateEnd = NewTimeDateFormat
FROM   Input_5929 WITH (NOLOCK)
ORDER BY NewTimeDateFormat DESC

SET @FiscalDateBeg = CONVERT(datetime, CONVERT(varchar(5), YEAR(@FiscalDateEnd) - 1) + '-07-01')


IF @FiscalDateEnd >= CONVERT(varchar(5), YEAR(GETDATE())) + '-07-01'
    SET @FiscalDateBeg = CONVERT(varchar(5), YEAR(@FiscalDateEnd)) + '-07-01'

SELECT  CONVERT(datetime, t.NewTimeDateFormat, 23)   AS FiscalMonth,
(Left(DATENAME(month, CONVERT(datetime, t.NewTimeDateFormat, 23)),3)+'-'+Right(Year(CONVERT(datetime, t.NewTimeDateFormat, 23)),2)) 
AS 'NewTimeCheck' FROM    Input_5929 t WITH (NOLOCK)
WHERE   CONVERT(date, t.NewTimeDateFormat) BETWEEN @FiscalDateBeg and 
--DATEADD(day, 1, DATEADD(year, -1, @FiscalDateEnd)) AND 
@FiscalDateEnd
GROUP BY CONVERT(datetime, t.NewTimeDateFormat, 23) order by FiscalMonth desc

Open in new window

0
dsackerContract ERP Admin/ConsultantCommented:
I was typing that fix when I saw the notification of a new email from you, and thought, "Bet he figured it out." :)
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Manju - I just kick out an article that deals specifically with Fiscal calendar planning --> SQL Server Calendar Table:  Fiscal Years.  Since you've already accepted an answer here, if the article helps you out then click on the 'Good Article' button and provide some feedback.  Thanks.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.