Oracle – for when it was like that when you got there

Main menu

Post navigation

Council Tax and calling functions from SQL in 11g

There is a frisson of excitement in our household at the moment. It’s not simply because the mighty Hammers on their way to Wembley. Oh no. It’s not even that Luton will be making the same journey with the same aim (i.e. promotion).
In fact, it has little or nothing to do with the end-of-season fun and games in the football world. The truth is, that we are finally moving from sunny Milton Keynes to …er… somewhere else in sunny Milton Keynes.
To mark this momentous occasion, Deb has busied herself with organizing the packing, working out where all the furniture is going in our new house and eyeing a whole range of utensils that will go with our new kitchen.
For my part, I’ve written a PL/SQL routine to apportion Council Tax Liability.
It should be noted that Deb has now applied to change her name to “The Long Suffering Deb” by deed pole.
Apart from exploring the algorithm required to apportion Council Tax ( for those outside the UK, it’s a sort of local tax on all domestic properties), I’ll also have a look at how 11g now allows at least three different ways of calling a database function from SQL.

The Apportionment Algorithm

This algorithm applies equally to Water Rates, Business Rates and Council Tax.
The “tax” year, in this context, runs from 1st April to 31st March inclusive.
To work out how much you actually need to pay up to a given date you need to :

Take the amount you are paying for the year

Divide by the number of days in the year

Multiply by the number of days up to and including the day you move

Well that all seems fairly straightforward…

The function

Here’s the function. We’re passing in three parameters :

i_move_date – the date that we are moving house

i_charge – the amount that we are being charged for the year

i_to_move – Y if we want to work out our bill up until the date we move, N to work out the bill from the date we move to the end of the year

Yes, the only way you’ll get out of paying the Council Tax is if you move somewhere they don’t have it (otherwise known as abroad).
Anyway, the code looks like this :

CREATE OR REPLACE FUNCTION calc_liability_fn(
i_move_date IN DATE,
i_charge IN NUMBER,
i_to_move IN VARCHAR2 DEFAULT 'Y')
RETURN NUMBER
IS
l_start_year PLS_INTEGER;
l_year_start_dt DATE;
l_year_end_dt DATE;
l_days_in_year PLS_INTEGER;
l_daily_rate NUMBER;
l_days_to_charge PLS_INTEGER;
e_invalid_params EXCEPTION;
BEGIN
--
-- Check all the parameters have been passed in and are valid
--
IF i_move_date IS NULL OR i_charge IS NULL OR i_to_move NOT IN ('Y', 'N')
THEN
RAISE e_invalid_params;
END IF;
--
-- Work out the start and end dates for the Council Tax/Rates Year
--
IF EXTRACT( MONTH FROM i_move_date) < 4 THEN
--
-- The move date is between January and March so the start of
-- the year is 1st April in the previous calendar year
--
l_start_year := EXTRACT ( YEAR FROM i_move_date) - 1;
ELSE
l_start_year := EXTRACT( YEAR FROM i_move_date);
END IF;
l_year_start_dt := TO_DATE('0104'||TO_CHAR( l_start_year), 'DDMMYYYY');
l_year_end_dt := ADD_MONTHS(l_year_start_dt, 12);
--
-- Get the number of days in the year ( thanks to Boneist for the tip 🙂
--
l_days_in_year := l_year_end_dt - l_year_start_dt;
--
-- Next we need to work out the daily charge
--
l_daily_rate := i_charge / l_days_in_year;
--
-- Now check to see if we're returning the cost of the charge from the start of the
-- year until the move date, or from the move date to the end of the year
--
IF i_to_move = 'Y' THEN
--
-- Calculate the total charge up to and including the date of the move
--
l_days_to_charge := i_move_date + 1 - l_year_start_dt;
ELSE
l_days_to_charge := l_year_end_dt - (i_move_date + 1);
END IF;
RETURN l_daily_rate * l_days_to_charge;
EXCEPTION
WHEN e_invalid_params THEN
RAISE_APPLICATION_ERROR(20000, 'Parameters missing or invalid.');
END;
/

“Well that’s nice”, I hear you say, “but what’s this three different ways of calling a function you mentioned earlier ?”

OK, so we’re passing in a date, followed by the charge for the year. We’re not bothering with the i_to_move parameter as we want to use the default value anyway.
Now, remember what you’re Mum told you about calling stored program units – always pass by reference, not by position.
Well, up to now, if you tried this in SQL, you would just get a rude message. With 11g however…

Boneist,
So, the comment about this program not being around in 2100 wasn’t enough for you ?
Remember the Millenium bug. Honestly, what do our grandchildren have to look forward to if we don’t through the odd time-bomb into our code every now and then ?
Roughly translated : Yep, you’re right. Thanks. Wish I’d thought of that.
The code has now been amended with appropriate comments.
Thanks for the tip 🙂