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…
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 ?”
The Good, The Bad, and The Ugly
First off, let’s try the traditional approach :
SQL> ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY'; Session altered. SQL> SELECT calc_liability_fn( '12-MAY-2012', 1382.06) to_pay 2 FROM dual; TO_PAY ---------- 159.031562 SQL>
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…
SQL> SELECT calc_liability_fn( 2 i_move_date => '12-FEB-2012', 3 i_charge => 1382.06, 4 i_to_move => 'N') to_pay 5 FROM dual; TO_PAY ---------- 181.25377 SQL>
I can just hear the sharpening of pencils as a myriad of Standards documents are being sized up for a quick edit.
Hang on though, there is another way…
SQL> SELECT calc_liability_fn( '09-AUG-2012', 1382.06, i_to_move => 'N') to_pay 2 FROM dual; TO_PAY ---------- 886.032986 SQL>
Yes, it is now possible to use a mixture of pass by reference and pass by position notation in the same call.
I’m not entirely sure why you would ever want to do this, but the fact is, you can if you are so inclined…except when you can’t…
SQL> SELECT calc_liability_fn( '09-AUG-2012', i_charge => 1382.06, 'N') to_pay 2 FROM dual; SELECT calc_liability_fn('09-AUG-2012', i_charge => 1382.06, 'N') to_pay * ERROR at line 1: ORA-06553: PLS-312: a positional parameter association may not follow a named association SQL>
After that little diversion, I need to return to working on the algorithm to calculate the number of Brownie Points required to let me watch the play-off final before I have to pack up the TV.