Month-Year Difference Determination UDF
Description
These UDFs implement difference determination in months/years using smaller units of granularity, days/months, to accurately calculate the difference. According to the ANSI SQL standard, units smaller than the unit of the result are to be ignored in an SQL calculation, but this can lead to results not consistent with business practices (Refer to NTA 1553). This distribution contains full source code, REPLACE FUNCTION SQL, a test suite and documentation.
months_on_books
/*
* months_on_books UDF - months_on_books.c
*
* Syntax:
*
* months_on_books(start_date, eval_date)
*
* Purpose:
*
* Returns the number of months between dates start_date and eval_date.
* If eval_date is later than start_date, the result is positive;
* if earlier, the result is negative.
*
* If a start_date of 1900-01-01 is encountered, a NULL value without an
* error will be returned. This allows for a default date value.
*
* If either start_date or eval_date is NULL, a user defined error will
* be returned.
*
* Example:
*
* SELECT months_on_books(DATE '2007-05-07', DATE '2008-05-06') "Months";
*
* Returns the following result:
*
* Months
* ------
* 11
*
REPLACE FUNCTION months_on_books(
start_date DATE,
eval_date DATE
)
RETURNS INTEGER
LANGUAGE C
NO SQL
PARAMETER STYLE SQL
DETERMINISTIC
CALLED ON NULL INPUT
EXTERNAL NAME 'CS!months_on_books!months_on_books.c';
*
* Test Cases:
*
CREATE SET TABLE months_on_books_test
(
test_id INTEGER,
start_date DATE,
eval_date DATE,
result_value INTEGER
)
PRIMARY INDEX (test_id);
INSERT INTO months_on_books_test (1, DATE '2007-05-07', DATE '2008-05-06', 11);
INSERT INTO months_on_books_test (2, DATE '2007-05-07', DATE '2008-05-07', 12);
INSERT INTO months_on_books_test (3, DATE '2007-05-07', DATE '2008-05-08', 12);
INSERT INTO months_on_books_test (4, DATE '2008-05-07', DATE '2007-05-06', -12);
INSERT INTO months_on_books_test (5, DATE '2008-05-07', DATE '2007-05-07', -12);
INSERT INTO months_on_books_test (6, DATE '2008-05-07', DATE '2007-05-08', -11);
INSERT INTO months_on_books_test (7, DATE '2008-05-07', DATE '2008-05-06', 0);
INSERT INTO months_on_books_test (8, DATE '2008-05-07', DATE '2008-05-07', 0);
INSERT INTO months_on_books_test (9, DATE '2008-05-07', DATE '2008-05-08', 0);
INSERT INTO months_on_books_test (10, DATE '2008-02-06', DATE '2008-04-01', 1);
INSERT INTO months_on_books_test (11, DATE '2008-02-06', DATE '2008-05-01', 2);
INSERT INTO months_on_books_test (12, DATE '2008-02-06', DATE '2008-03-20', 1);
INSERT INTO months_on_books_test (13, DATE '2008-02-06', DATE '2008-04-21', 2);
INSERT INTO months_on_books_test (14, DATE '1900-01-08', DATE '2008-05-08', 1300);
SELECT test_id (TITLE 'Test ID'),
CASE WHEN months_on_books(start_date, eval_date) = result_value
THEN 'OK'
ELSE 'FAILED'
END (TITLE 'Result')
FROM months_on_books_test
ORDER BY 1;
DROP TABLE months_on_books_test;
*
* The following test case should return NULL
*
SELECT months_on_books(DATE '1900-01-01', DATE '2008-04-21') "Months";
*
* Created by Nick Muller (nicholas.muller@teradata.com)
*
* Current Revision: 1.0 / 2008-05-27
*/
years_on_books
/*
* years_on_books UDF - years_on_books.c
*
* Syntax:
*
* years_on_books(start_date, eval_date)
*
* Purpose:
*
* Returns the number of years between dates start_date and eval_date.
* If eval_date is later than start_date, the result is positive;
* if earlier, the result is negative.
*
* Given the return value is an integer, proper rounding will be used
* (less than 6 months, round down, and 6 months or more, round up).
*
* If a start_date of 1900-01-01 is encountered, a NULL value without an
* error will be returned. This allows for a default date value.
*
* If either start_date or eval_date is NULL, a user defined error will
* be returned.
*
* Example:
*
* SELECT years_on_books(DATE '2007-05-07', DATE '2008-05-06') "Years";
*
* Returns the following result:
*
* Years
* ------
* 1
*
REPLACE FUNCTION years_on_books(
start_date DATE,
eval_date DATE
)
RETURNS INTEGER
LANGUAGE C
NO SQL
PARAMETER STYLE SQL
DETERMINISTIC
CALLED ON NULL INPUT
EXTERNAL NAME 'CS!years_on_books!years_on_books.c';
*
* Test Cases:
*
CREATE SET TABLE years_on_books_test
(
test_id INTEGER,
start_date DATE,
eval_date DATE,
result_value INTEGER
)
PRIMARY INDEX (test_id);
INSERT INTO years_on_books_test (1, DATE '2007-05-07', DATE '2007-05-08', 0);
INSERT INTO years_on_books_test (2, DATE '2007-05-07', DATE '2007-05-14', 0);
INSERT INTO years_on_books_test (3, DATE '2007-05-07', DATE '2007-06-07', 0);
INSERT INTO years_on_books_test (4, DATE '2007-05-07', DATE '2007-08-07', 0);
INSERT INTO years_on_books_test (5, DATE '2007-05-07', DATE '2007-11-06', 0);
INSERT INTO years_on_books_test (6, DATE '2007-05-07', DATE '2007-11-07', 1);
INSERT INTO years_on_books_test (7, DATE '2007-05-07', DATE '2007-11-08', 1);
INSERT INTO years_on_books_test (8, DATE '2007-05-07', DATE '2008-02-07', 1);
INSERT INTO years_on_books_test (9, DATE '2007-05-07', DATE '2008-05-07', 1);
INSERT INTO years_on_books_test (10, DATE '2007-05-07', DATE '2008-11-06', 1);
INSERT INTO years_on_books_test (11, DATE '2007-05-07', DATE '2008-11-07', 2);
INSERT INTO years_on_books_test (12, DATE '2007-05-07', DATE '2008-11-08', 2);
INSERT INTO years_on_books_test (13, DATE '2007-05-07', DATE '2012-04-07', 5);
INSERT INTO years_on_books_test (14, DATE '1900-01-08', DATE '2008-05-08', 108);
INSERT INTO years_on_books_test (15, DATE '1932-01-01', DATE '2008-05-28', 76);
INSERT INTO years_on_books_test (16, DATE '1947-05-07', DATE '2008-05-28', 61);
INSERT INTO years_on_books_test (17, DATE '1954-11-29', DATE '2008-05-28', 53);
INSERT INTO years_on_books_test (18, DATE '1961-11-28', DATE '2008-05-28', 47);
INSERT INTO years_on_books_test (19, DATE '1973-11-27', DATE '2008-05-28', 35);
INSERT INTO years_on_books_test (20, DATE '1985-11-07', DATE '2008-05-28', 23);
INSERT INTO years_on_books_test (21, DATE '1996-10-30', DATE '2008-05-28', 12);
INSERT INTO years_on_books_test (22, DATE '2007-05-08', DATE '2007-05-07', 0);
INSERT INTO years_on_books_test (23, DATE '2007-05-14', DATE '2007-05-07', 0);
INSERT INTO years_on_books_test (24, DATE '2007-06-07', DATE '2007-05-07', 0);
INSERT INTO years_on_books_test (25, DATE '2007-08-07', DATE '2007-05-07', 0);
INSERT INTO years_on_books_test (26, DATE '2007-11-06', DATE '2007-05-07', 0);
INSERT INTO years_on_books_test (27, DATE '2007-11-07', DATE '2007-05-07', -1);
INSERT INTO years_on_books_test (28, DATE '2007-11-08', DATE '2007-05-07', -1);
INSERT INTO years_on_books_test (29, DATE '2008-02-07', DATE '2007-05-07', -1);
INSERT INTO years_on_books_test (30, DATE '2008-05-07', DATE '2007-05-07', -1);
INSERT INTO years_on_books_test (31, DATE '2008-11-06', DATE '2007-05-07', -1);
INSERT INTO years_on_books_test (32, DATE '2008-11-07', DATE '2007-05-07', -2);
INSERT INTO years_on_books_test (33, DATE '2008-11-08', DATE '2007-05-07', -2);
INSERT INTO years_on_books_test (34, DATE '2012-04-07', DATE '2007-05-07', -5);
INSERT INTO years_on_books_test (35, DATE '2008-05-08', DATE '1900-01-08', -108);
INSERT INTO years_on_books_test (36, DATE '2008-05-28', DATE '1932-01-01', -76);
INSERT INTO years_on_books_test (37, DATE '2008-05-28', DATE '1947-05-07', -61);
INSERT INTO years_on_books_test (38, DATE '2008-05-28', DATE '1954-11-29', -53);
INSERT INTO years_on_books_test (39, DATE '2008-05-28', DATE '1961-11-28', -47);
INSERT INTO years_on_books_test (40, DATE '2008-05-28', DATE '1973-11-27', -35);
INSERT INTO years_on_books_test (41, DATE '2008-05-28', DATE '1985-11-07', -23);
INSERT INTO years_on_books_test (42, DATE '2008-05-28', DATE '1996-10-30', -12);
INSERT INTO years_on_books_test (43, DATE '2008-05-28', DATE '2008-05-28', 0);
SELECT test_id (TITLE 'Test ID'),
CASE WHEN years_on_books(start_date, eval_date) = result_value
THEN 'OK'
ELSE 'FAILED'
END (TITLE 'Result')
FROM years_on_books_test
ORDER BY 1;
DROP TABLE years_on_books_test;
*
* The following test case should return NULL
*
SELECT years_on_books(DATE '1900-01-01', DATE '2008-04-21') "Years";
*
* Created by Nick Muller (nick.muller@teradata.com)
*
* Current Revision: 1.0 / 2008-05-28
*/
Discussion
You must sign in to leave a comment.