SQL Select date

Question

Post reply

SQL Select date

Dee Dee-422077

Ten Centuries

Points: 1082
More actions
March 21, 2022 at 8:39 pm
Go to Answer
#4003710

Hi all ,
I have fiscal year date is from Oct 1 to Sept 31 every year.
I would like to have a SQL select statement to load 2 fiscal years of history + current fiscal year. For example, if i run now it will return all records with date Oct 1 2019 -present fiscal year. Please advised how to do this dynamically.
Thanks in advanced.
Best,
Ddee.
Jeff Moden

SSC Guru

Points: 1004323
More actions
March 22, 2022 at 2:03 am
Answer
#4003819
With apologies to Calendar Table Lovers, I've grown to hate Calendar tables unless they're absolutely necessary (which is not the case here) because of their tendency to kill performance. I'd tell you all the reasons why that happens but here's an article from Brent Ozar, instead.
https://www.brentozar.com/archive/2020/08/date-tables-are-great-for-users-but-not-so-great-for-performance/
With that being said, here's the code that auto-magically figures out the start date for such a thing based on whatever today's date is. In the formula below, the +2 identifies how many years prior to the current fiscal year you want to go back. It would be pretty easy to put this in a function with a given date and number of previous years.
```
 SELECT DATEFROMPARTS(YEAR(GETDATE())-(IIF(MONTH(GETDATE())<10,1,0)+2),10,1);
```
If you want to test it for every month in the current century, here's the code. You can get the fnTally "psuedo-cursor" function from a similarly named link in my signature line below.
```
--===== Create and populate the test table with 
     -- every month for the current century.
   DROP TABLE IF EXISTS #MyHead
;
 SELECT SomeDT = DATEADD(mm,t.N,'2000')
   INTO #MyHead
   FROM dbo.fnTally(0,DATEDIFF(mm,'2000','2100'))t
;
--===== Test the formulate next to the month dates.
 SELECT SomeDT
        ,Fy2yrBack = DATEFROMPARTS(YEAR(SomeDT)-(IIF(MONTH(SomeDT)<10,1,0)+2),10,1)
   FROM #MyHead ORDER BY SomeDT
;
```
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

homebrew01 SSC Guru Points: 55548 More actions · Answer 1

We have a Lookup table containing fiscal year dates and fiscal period dates.

We then use that table to determine what dates to use in programs.

This reply was modified 2 years, 8 months ago by homebrew01.

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 2

I agree with Homebrew01 here - I would do this in a table. That way the logic is EASY to update in the event your fiscal year changes. You MAY not think that can happen, but if your company gets bought out by some other company, you will be wishing you had done it as Homebrew01 suggested!

Now, if you don't want to do that, you are going to need to use some date math to mark things as "start of fiscal" and "end of fiscal". So I'd likely make a function that calculates out the fiscal year based on a given date and use that in all of your stored procedures. Something along the lines of if the datepart month is less than 10, then return the current year, if datepart is greater than or equal to 10, then return the next year. That is assuming that October 1st, 2021 (for example) is the fiscal year 2022. If it would be 2021, then I'd adjust that calculation so <10 returns previous year, and >=10 returns current year. Then use that function in your stored procedures to get the fiscal years and filter things as needed.

NOTE - functions in WHERE clauses are usually inefficient, so you may end up with performance problems. Using the table approach you can just join it and use a WHERE clause on the columns which will be properly indexed and have much better performance.

I suggested the function approach so you can have a single place where you update it if you ever need to. My last (and worst) suggestion would be to use the function logic wherever you need to calculate that, and if applicable, store the value in a calculated column in the table(s) where the data is coming from. The reason I don't like that approach is that if your company ever needs to change fiscal years, you now have multiple places that need updating and it'll be a huge pain in the behind.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.