Query for UK Financial period

[DaveTheRave.NET]

Does anyone have any SQL statement(s) that can return the UK financial
period for a date? Dates are in the form dd/mm/yyyy hh:nn:ss (where
"nn" is minutes). I am happy to create nested SQL with parameter
passing if necessary.
I will be using it to populate a "Period Number" field in a table
where there is already a data field from which the period will be
calculated. The period number will take the form "2007\01, 2007\02,
2007\03, 2007\04" e.t.c. I know I can use a table with the Start and
End dates along with the corresponding Period Number but this is slow
and I have to split dates down and rebuild them to get the calculation
to come out right.

Thanks.
David Rocke
Scotland.

 

[Jamie Collins]

Does anyone have any SQL statement(s) that can return the UK financial
period for a date? Dates are in the form dd/mm/yyyy hh:nn:ss (where
"nn" is minutes). I am happy to create nested SQL with parameter
passing if necessary.
I will be using it to populate a "Period Number" field in a table
where there is already a data field from which the period will be
calculated. The period number will take the form "2007\01, 2007\02,
2007\03, 2007\04" e.t.c. I know I can use a table with the Start and
End dates along with the corresponding Period Number but this is slow
and I have to split dates down and rebuild them to get the calculation
to come out right.

Do you mean you've tried something like this and it's too slow?:

SELECT T1.test_date, T2.period_number
FROM MyTable AS T1
INNER JOIN UKTaxPeriods AS T2
ON (T1.test_date BETWEEN T2.start_date AND T2.end_date)

Agreed, that will run slow on Jet.

Consider using a Calendar table, comprising one row for every day
within a large range of dates, then rounding your dates to the nearest
day (or week or month) to be able to do an equi-join to the Calendar
e.g. more like:

SELECT T1.test_date, C1.period_number
FROM MyTable AS T1
INNER JOIN Calendar AS C1
ON C1.dt = DATEADD('D', DATEDIFF('D', #1990-01-01 00:00:00#,
T1.test_date), #1990-01-01 00:00:00#);

The above executes very fast, even on Jet, and the Calendar table will
probably prove useful for other things.

Jamie.

--