Create formula to query automatically based on dates

1

I have several sql reports that are based on date ranges. I would like to create a formula that will allow these to run without me having to change the date range each year. Below is an example of how I'm currently running. As they are now I don't have to do anything until the start of each fiscal year, then I just have to change the year.

Select
a.company
,a.code
,a.contract
,amount

from
Allocated_amounts as a

where
a.start_date >= '20230701'
and
a.end_date <= '20240630'

I do have a table available that has built in dates and it looks like this:

Select
FiscalYearKey
,FiscalYearName
,StartDate
,EndDate

2

You can use the DATEFROMPARTS(YEAR(GETDATE(),7,1) for example

Or u can use something like this

DECLARE @StartDate Date
SET @StartDate = (SELECT MAX(StartDate) FROM [I do have a table available that has built in dates and it looks like this] WHERE StartDate <= GETDATE())

....
where
a.start_date >= @StartDate

I assume start_date is a date field.

3

I think this could work but my sql skills are still pretty minimal so not sure I understand fully what these formulas are doing. My main goal is that no matter what day I run the report on it will only bring in results for that fiscal year (July 1 to June 30 of following year) the report is run on. So for example, if I run the sql on 7/1/2023, or 12/15/23, or 6/30/24 it will only bring in results for Fiscal Year 2024. And the same if I run the sql on 7/1/26 or 12/15/26 or 6/30/26 it will only bring in results for Fiscal Year 2026. And I wouldn't have to manually change anything in the sql.

4

@RogierPronk gave you 2 options - let's take a look at each one:

DATEFROMPARTS({year}, {month}, {day})

For today - 2024-03-12 - using this DATEFROMPARTS(year(getdate()), 7, 1) returns 2024-07-01 which would be the start of fiscal year 2025 (not correct). Instead - we want DATEFROMPARTS(year(getdate()) - 1, 7, 1) which would return 2023-07-01 (start of fiscal year 2024).

For the end of the range we want DATEFROMPARTS({year}, 6, 30) - and this DATEFROMPARTS(year(getdate()), 6, 3) returns 2024-06-30.

However, if the date is 2023-11-15 - we get 2023-06-30 which is not correct.

So - we need to calculate for either calculation when to add or subtract 1 from the year. To do that, we just need to check the month:

SELECT DATEFROMPARTS(year(getdate()) - iif(month(getdate()) < 7, 1, 0), 7, 1);
SELECT DATEFROMPARTS(year(getdate()) + iif(month(getdate()) >= 7, 1, 0), 6, 30);

If the date is 2023-11-15 - we get DATEFROMPARTS(2023 - 0, 7, 1) = 2023-07-01 and DATEFROMPARTS(2023 + 1, 6, 30) = 2024-06-30.

If the date is 2024-03-12 we get DATEFROMPARTS(2024 - 1, 7, 1) = 2023-07-01 and DATEFROMPARTS(2024 + 0, 6, 30) = 2024-06-30.

The other solution is to lookup the start/end dates of the fiscal year based on the date. Given a table of fiscal years - where we have the start and end dates of the fiscal year - we can lookup the start and end dates.

SELECT TOP (1)
       *
  FROM fiscal_calendar
 WHERE StartDate <= getdate()
 ORDER BY
       StartDate DESC;

The above gets the latest row from the fiscal calender that has a start date less than or equal to today. So now all we need to do is return the actual start and end dates in variables:

DECLARE @StartDate date
      , @EndDate   date;

SELECT TOP (1)
       @StartDate = StartDate
     , @EndDate   = EndDate
  FROM fiscal_calendar
 WHERE StartDate <= getdate()
 ORDER BY
       StartDate DESC;

SELECT a.company
     , a.code
     , a.contract
     , a.amount
  FROM Allocated_amounts as a
 WHERE a.start_date >= @StartDate
   AND a.end_date   <= @EndDate;

One caveat - if your date columns in the table are actually datetime then you need to account for the times. If that is the case, then you would add one day to the end date and change the check for that end date to less than: AND a.end_date < DATEADD(DAY, 1, @EndDate)

1 Like
5

I think I'm getting closer to understanding. on the second option, I entered it just as you show but in my Where statement i'm getting an error stating the a.start_date and a.end_date are invalid column names. I know I'm missing something probably super easy but I'm not seeing it.

6

Disregard my last message, I found what I did wrong. I believe this resolves all my questions. Thank you, I can use this on several other queries I have so that I will no longer have to manually update with new dates. Thank you!!

7

Happy to help - thanks for the feedback.