Query for UK Financial period

D

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.
 
J

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.
Click to expand...

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.

--
 
Top