Query Help

Database
Enthusiast

Query Help

Hi I have the following query with me.
Can one help in formatting this query as single query.
Thanks for help.

/* x days old : Creating Table for building the working days between given two dates*/

/*Drop Table working_days;*/

Create Table working_days As(
Select
cast(cast(N516AA_REC_DATE_CCYYMMDD As date format 'yyyymmdd') As date format 'yyyy-mm-dd') start_date, CURRENT_DATE end_date, (CURRENT_DATE - cast(cast(N516AA_REC_DATE_CCYYMMDD As date format 'yyyymmdd') As date format 'yyyy-mm-dd') )+1 no_of_days, no_of_days / 7 * 5 working_days /*excluding sat and sun*/, no_of_days mod 7 remain_days,
Case
When remain_days=0
Then CURRENT_DATE-remain_days
When remain_days In (1,2,3,4,5,6,7)
Then CURRENT_DATE-remain_days+1
End As check_date
From krishna
)with data primary index(start_date)
;

/*drop table test ;*/

Create Table test (

start_date date,remain_days integer, check_date date, dow integer,working_days integer
) primary index(start_date)

;

select * from test;

Insert Into test
Select start_date,remain_days, check_date, dayofweek(check_date),working_days
From working_days
;

/*Drop Table test1;*/
Create Table test1 As (
Select
start_date,remain_days, check_date, dow,
Case

When remain_days=0
And dow In (1,7)
Then working_days+remain_days-1
When remain_days=0
And dow In (2,3,4,5,6)
Then working_days+remain_days

When remain_days=1
And dow In (1,7)
Then working_days+remain_days-1
When remain_days=1
And dow In (2,3,4,5,6)
Then working_days+remain_days

When remain_days=2
And dow In (1,6)
Then working_days+remain_days-1
When remain_days=2
And dow=7
Then working_days+remain_days-2
When remain_days=2
And dow In (2,3,4,5)
Then working_days+remain_days
When remain_days=3
And dow In (1,5)
Then working_days+remain_days-1
When remain_days=3
And dow In (7 ,6)
Then working_days+remain_days-2
When remain_days=3
And dow In (2,3,4)
Then working_days+remain_days

When remain_days=4
And dow In (1,4)
Then working_days+remain_days-1
When remain_days=4
And dow In (5,7 ,6)
Then working_days+remain_days-2
When remain_days=4
And dow In (2,3)
Then working_days+remain_days

When remain_days=5
And dow In (1,3)
Then working_days+remain_days-1
When remain_days=5
And dow In (4,5,7 ,6)
Then working_days+remain_days-2
When remain_days=5
And dow=2
Then working_days+remain_days

When remain_days=6
And dow In (1,2)
Then working_days+remain_days-1
When remain_days=6
And dow In (3,4,5,7 ,6)
Then working_days+remain_days-2

End As wd
From test)
with data
Primary Index(start_date)
;

Select a.start_date,a.end_date,c.wd
From working_days a Left Join test b
On a.start_date=b.start_date
Left Join test1 c
On b.start_date=c.start_date

;

Regards,
Krishna