Converting Existing Table Date Counter from DAYS to WEEKDAYS
Hi there! this is my first post so hopefully I get it right.
Firsty this is work related so I cannot upload any dataset as it pertains to sensitive material. The case is that in the data base there is an application date, completion date and sanctioning date for each application and at the moment these dates are used to compute
1) Time to completion (Completion Date - Application Date)
2)Time to Sanction (Sanction Date - Application Date)
They however must now be converted to be in terms of weekdays (working days)
The first thing I did is create a table which contains the period dates of all bank holidays which I sourced on the calender
Create table Working_holidays ( calendar_date date ) primary index (calendar_date)
collect stats on Working_holidays index (day_of_calendar);
insert into ddewp03p.dw28226_Working_holidays
Select calendar_date from sys_calendar.calendar where (day_of_week-1=0 or day_of_week/7=1) and calendar_date BETWEEN 1120101 AND 1141231
; Insert into ddewp03p.dw28226_Working_holidays
Select calendar_date from sys_calendar.calendar where calendar_date in ( 1120102 or 1120319 or 1120409 or 1120507 or 1120604 or 1120806 or 1121029 or 1121225 or 1121226 or 1121227 or 1130101 or 1130318 or 1130401 or 1130506 or 1130603 or 1130805 or 1131028 or 1131225 or 1131226 or 1131227 or 1140101 or 1140317 or 1140421 or 1140505 or 1140602 or 1140804 or 1141027 or 1141225 or 1141226 or 1141229
When I try implement this code for specific dates it works a treat! The problem however arises when I try and select dates from the existing database as Teradata does not seem to like selects within selects. Lets say for example the application information is coming from the application table..
I feel like the line I'm looking for is something along the lines of this
select appl_dte - sanction_dte -(count(calendar_date)) FROM Working_holidays where calendar_date between appl_dte and sanction_dte
But obviously I'm having difficulty joining the two tables. Ok, Thanks in advance!