All Forums General
TDW 36 posts Joined 11/12
18 Nov 2014
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
 
 

Thanks and God Bless,
Genesius

dnoeth 4628 posts Joined 11/04
19 Nov 2014

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.

Dieter

You must sign in to leave a comment.