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 :

  1. Take the amount you are paying for the year
  2. Divide by the number of days in the year
  3. 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 ?"

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.

About these ads

2 thoughts on “Council Tax and calling functions from SQL in 11g

  1. Your leap year logic is wrong… according to that, 2100 will be a leap year, except it isn’t!

    Why not just grab the difference between the start and the end dates? Then you don’t have to worry about leap years!

    Eg.

    select ADD_MONTHS(TO_DATE(’0104′||’2011′, ‘DDMMYYYY’), 12) – TO_DATE(’0104′||’2011′, ‘DDMMYYYY’) num_days
    from dual;

    NUM_DAYS
    ———-
    366

    • 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 :)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s