Calculating for a 45-Day Grace Period

General
TDW
Enthusiast

Calculating for a 45-Day Grace Period

I adopted the following code from a former employee in my department.

What I need to do is create a table with only the valid BillPeriods in it.

BizCode is CHAR(8)

Bgn_Dte and End_Dte , which are based on the contract for that business, are both dates in the following format. YYYY-MM-DD.

BillPeriod is an Integer as follows:

11408

11409

11410

11411

11412

11501

11502

The Payment Due Date is always the 20th of the month; therefore the previous programmer used - 20 in their SQL.

Below is a table displaying how the 45-day grace period works displaying the BillPeriod, its Payment Due Date, and adding the 45-day Grace Period to come up with its End Due Date.

BillPeriod  Payment Due Date  Grace Period  End Due Date
11409  9/20/2014  45  11/4/2014
11410  10/20/2014  45  12/4/2014
11411  11/20/2014  45  1/4/2015
11412  12/20/2014  45  2/3/2015

BillPeriod 11410 has a End Due Date of 12/4/2014

The below SQL is used to calculate which BizCode will incur surcharges, which are handled in a later process.

SELECT 
 BizCode,
 Bgn_Dte,
 End_Dte,
 BillPeriod
FROM STAGE.ACElig 
 INNER JOIN STAGE.AC20Mnthly ON 1=1
 WHERE
  BillPeriod>=
   CASE WHEN EXTRACT (YEAR FROM Bgn_Dte) > 1999
    THEN '1' || SUBSTRING(Bgn_Dte FROM 3 FOR 2) || SUBSTRING(Bgn_Dte FROM 6 FOR 2)
    ELSE '0' || SUBSTRING(Bgn_Dte FROM 3 FOR 2) || SUBSTRING(Bgn_Dte FROM 6 FOR 2)
   END
 AND
  BillPeriod<=
   CASE WHEN End_Dte< CURRENT_DATE
    THEN CASE WHEN EXTRACT (YEAR FROM End_Dte) > 1999
      THEN '1' || SUBSTRING(End_Dte FROM 3 FOR 2) || SUBSTRING(End_Dte FROM 6 FOR 2)
      ELSE '0' || SUBSTRING(End_Dte FROM 3 FOR 2) || SUBSTRING(End_Dte FROM 6 FOR 2)
     END
    ELSE '1' || SUBSTRING(CURRENT_DATE - 20 FROM 3 FOR 2) || SUBSTRING(CURRENT_DATE - 20 FROM 6 FOR 2)
  
 END
ORDER BY 8 DESC
;

Using the "- 20" in the code - representing the 20th of the month - causes a problem as seen below.

Current_Date  New Date  New BillPeriod
11/18/2014  10/29/2014  11410
11/19/2014  10/30/2014  11410
11/20/2014  10/31/2014  11410
11/21/2014  11/1/2014  11411
11/22/2014  11/2/2014  11411
11/23/2014  11/3/2014  11411
11/24/2014  11/4/2014  11411

This is a problem because when the Current_Date changes from from November 20th to the 21st the BillPeriod changes from 11410 to 11411. Going back to the first table above, BillPeriod 11410 has an End Due Date of 12/4/2014, and not 11/21/2014.

Below are the results of running the SQL.

BizCode  Eff_Dte  End_Dte  BillPeriod
A123  3/1/2012  1/1/9999  11410
B123  1/1/2012  1/1/9999  11410
C123  4/1/2012  1/1/9999  11410
A123  3/1/2012  1/1/9999  11409
B123  1/1/2012  1/1/9999  11409
C123  4/1/2012  1/1/9999  11409

The first 3 records should hace been excluded from these results.

I am under very strict deadline by my director, so thanks to anyone who can give me some direction.

Thanks and God Bless,

Genesius

1 REPLY
Junior Contributor

Re: Calculating for a 45-Day Grace Period

Hi Genesius,

I don't fully understand your problem :)

What data already exists and what do you need to calculate?

Do you want to get all bill periods for the given begin/end date of a customer?

Then EXPAND ON might do this without joining to the bill periods table.

Some remarks:

The calculation to get the BillPeriod for a given date is overly complex, casting a date to a string and then casting it back to an integer. Actually your BillPeriod seems to be based on the internal storage of a date in Teradata, so 

where BillPeriod>= Bgn_Dte / 100 

is exactly the same.

Instead of casting every begin and end date to a BillPeriod you better cast the BillPeriod to a date using 

CAST(BillPeriod * 100 +  1) AS DATE)      -- first of month
CAST(BillPeriod * 100 + 20) AS DATE) -- 20th of month
CAST(BillPeriod * 100 + 20) AS DATE) + 45 -- end of grace period

A simple

WHERE (begin due, end due)
OVERLAPS (bgn_dte, case when end_dte < current_date then end_date else current_date end)

might also work.