Converting Existing Table Date Counter from DAYS to WEEKDAYS

Database

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!

Tags (3)