Query records between business years

Question

Business starts on first October of every year. I need two queries, one which returns all records for current business year and the other returns all records for previous business year. Leap year is not important in this case.

I have written:

strSql1 = "SELECT * FROM Orders WHERE DateDiff('d', 
[OrderDate], [YearStart])<=365 ORDER BY [OrderDate] ASC"

Me.frmCurrentYear.Form.RecordSource = strSql1

strSql2 = "SELECT * FROM Orders WHERE DateDiff('d', [OrderDate], 
[YearStart])>365 AND DateDiff('d', [PurchaseDate],[YearStart]<=730) ORDER BY 
[OrderDate] ASC"

Me.frmPreviousYear.Form.RecordSource = strSql2

Questions:

From my code, I will have to add a YearStart field to my table. Is there a way to avoid this?
Would using BETWEEN be a more efficient way?

With thanks.

Gustav · Accepted Answer · 2020-05-04T16:58:55.780

1

Use DateSerial for such tasks: For the year starting yyyy/10/01, the current business year records would be:

strSql1 = "SELECT * FROM Orders " & _
"WHERE DateDiff('m', [OrderDate], DateSerial(Year(Date()), 1, 1)) Between 3 And -9 " & _
"ORDER BY [OrderDate] ASC"

Records for previous business year would then be:

strSql2 = "SELECT * FROM Orders " & _
"WHERE DateDiff('m', [OrderDate], DateSerial(Year(Date()) - 1, 1, 1)) Between 3 And -9 " & _
"ORDER BY [OrderDate] ASC"

edited May 04 '20 at 16:58

answered May 03 '20 at 10:15

Gustav

53,498
7
29
55

can you explain a bit please? On w3schools `DateDiff('m', '1/10/2019', DateSerial(Year(Date()), 1, 1));` gives me 12 while `DateDiff('m', '1/10/2017', DateSerial(Year(Date()) - 1, 1, 1));` gives 24 and have no idea where the Between -3 and 9 comes in. – Fil May 03 '20 at 11:38
It specifies the possible difference in calendar months between OrderDate and the latest New Year (or that before). – Gustav May 03 '20 at 12:44
1

I see, I have added a parenthesis to finish up the `DateDiff()` function. Sometimes the business starts in September would this also be valid? – Fil May 03 '20 at 13:27
Yes. That would be `Between -4 And 8`. – Gustav May 03 '20 at 14:50

score 0 · Answer 2 · answered May 03 '20 at 07:42

1- You can always calculate something like if month is oct ,nov or dec then current year else previous year.

case when Month(GetDate()) >=10 then Cast('10-01'+Cast(Year(GetDate() as Varchar(5)) as date)
     when Month(GetDate()) <10 then Cast('10-01'+Cast(Year(GetDate())-1 as varcher(5)) as date) 
END as YearStart

2- SQL : BETWEEN vs <= and >=

score 0 · Answer 3 · answered May 03 '20 at 12:37

The current business year is defined as the year when you add three months:

SELECT *
FROM Orders
WHERE YEAR(DATEADD("m", 3, YEAR(OrderDate))) = YEAR(DATE())

And for the previous year:

SELECT *
FROM Orders
WHERE YEAR(DATEADD("m", 3, YEAR(OrderDate))) = YEAR(DATE()) - 1

This assumes that Jan - Dep 2020 are in year "2020" and the last three months are in year "2021".

Query records between business years

3 Answers3