General
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 Date11409   9/20/2014    45  11/4/201411410   10/20/2014    45  12/4/201411411   11/20/2014    45  1/4/201511412   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,  BillPeriodFROM 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)   ENDORDER 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 BillPeriod11/18/2014  10/29/2014   1141011/19/2014  10/30/2014   1141011/20/2014  10/31/2014   1141011/21/2014  11/1/2014   1141111/22/2014  11/2/2014   1141111/23/2014  11/3/2014   1141111/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  BillPeriodA123   3/1/2012  1/1/9999  11410B123   1/1/2012  1/1/9999  11410C123   4/1/2012  1/1/9999  11410A123   3/1/2012  1/1/9999  11409B123   1/1/2012  1/1/9999  11409C123   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

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 monthCAST(BillPeriod * 100 + 20) AS DATE)      -- 20th of monthCAST(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.