Custom dates for archive providers.

[pricerc]

There an outstanding request (https://service.jiwa.com.au/browse/DEV-8612) for 'other' dates for the archiving tool.

Having just been testing this for a client, adding new date filters for a provider is quite easy, except that the values are coded in SQL, which gets added into some other SQL before being executed.

The 'default' "Two year" filter looks like this:

Code: Select all

SELECT DATEADD(year,-2,GETDATE())


Which gives you two years prior to the date of execution. But what if you want to archive whole financial years, and you're already months into the year?

Well, since Jiwa kindly has a table of financial years - the GL_Config table, it should be easy enough to use that.

Code: Select all

SELECT MAX([YearStartingDate])
  FROM [dbo].[GL_Config]
  WHERE DATEDIFF(year, [YearStartingDate], GETDATE()) > 5


[DannyC]

I use variation of this SQL to get the financial year start date from nn years ago.

Code: Select all

SELECT DATEADD(year,-7,DATEADD(mm,DATEDIFF(mm,0,GETDATE()) + 7 - MONTH(GETDATE())-CASE WHEN 7 > MONTH(GETDATE()) THEN 12 ELSE 0 END,0))

[pricerc]

I use variation of this SQL to get the financial year start date from nn years ago.

Code: Select all

SELECT DATEADD(year,-7,DATEADD(mm,DATEDIFF(mm,0,GETDATE()) + 7 - MONTH(GETDATE())-CASE WHEN 7 > MONTH(GETDATE()) THEN 12 ELSE 0 END,0))

I've got a whole SQL function for doing those, which is great if you don't have ready access to a table with the data, or don't want to hit the table for some reason.

But for the archiving, if you take a look at the stored procedure, it's just using the query to populate a cutoff variable prior to running the queries to do the actual work, so hitting the table isn't an issue, and so using it makes for nice, simple query with no date maths, and it doesn't matter what month you start your financial year (we have customers who start in April and January), it will still work.

The only bit I haven't confirmed is whether I should be using the YearEndingDate for the archiving. Depends on whether it's using a < or a <=.