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:
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.
INNER JOIN STAGE.AC20Mnthly ON 1=1
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)
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)
ELSE '1' || SUBSTRING(CURRENT_DATE - 20 FROM 3 FOR 2) || SUBSTRING(CURRENT_DATE - 20 FROM 6 FOR 2)
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,
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.
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
WHERE (begin due, end due)
OVERLAPS (bgn_dte, case when end_dte < current_date then end_date else current_date end)
might also work.