derived table

UDA
Enthusiast

derived table

Create volatile Table CE1393_acctid_spend
As

(
Select z.acct_id
,da1.last_month
,da2.two_mths_ago
,da3.three_mths_ago

From CE1393_Join_AC z
Left Join
(

Select acct_id
,Sum(bsh.bill_stmt_tot_chrg_am) As last_month

FROM ikviews_Hist.BILL_STMNT_HIST bsh

Inner Join
ikviews_hist.sys_calendar calendar
On bill_stmt_bill_cycle_end_dt = calendar.calendar_date
Inner Join
ikviews.sys_calendar today
On (calendar.month_of_calendar = today.month_of_calendar - 1)
Group By 1
)
da1
On z.acct_id = da1.acct_id
Left Join

(
Select acct_id
,Sum(bsh.bill_stmt_tot_chrg_am) As two_mths_ago

FROM ikviews_Hist.BILL_STMNT_HIST bsh

Inner Join
ikviews_hist.sys_calendar calendar
On bill_stmt_bill_cycle_end_dt = calendar.calendar_date
Inner Join
ikviews.sys_calendar today
On (calendar.month_of_calendar = today.month_of_calendar - 2)
Group By 1
)
da2
On z.acct_id = da2.acct_id
Left Join
(

Select acct_id
,Sum(bsh.bill_stmt_tot_chrg_am) As three_mths_ago

FROM ikviews_Hist.BILL_STMNT_HIST bsh

Inner Join
ikviews_hist.sys_calendar calendar
On bill_stmt_bill_cycle_end_dt = calendar.calendar_date
Inner Join
ikviews.sys_calendar today
On (calendar.month_of_calendar = today.month_of_calendar - 3)
Group By 1
)
da3
On z.acct_id = da3.acct_id
)
With data
Primary Index (acct_id)
On
Commit preserve rows;
Can I increase the performance of the quiery by not using Derived tables?
2 REPLIES
Junior Contributor

Re: derived table

Instead of three Derived Tables you can rewrite it using a single DT:

Select z.acct_id
,da1.last_month
,da1.two_mths_ago
,da1.three_mths_ago

From CE1393_Join_AC z
Left Join
(

Select acct_id
,Sum(case when calendar.month_of_calendar = today.month_of_calendar - 1
then bsh.bill_stmt_tot_chrg_am end) As last_month
,Sum(case when calendar.month_of_calendar = today.month_of_calendar - 2
then bsh.bill_stmt_tot_chrg_am end) As two_mths_ago
,Sum(case when calendar.month_of_calendar = today.month_of_calendar - 3
then bsh.bill_stmt_tot_chrg_am end) As three_mths_ago
FROM ikviews_Hist.BILL_STMNT_HIST bsh
Inner Join
ikviews_hist.sys_calendar calendar
On bill_stmt_bill_cycle_end_dt = calendar.calendar_date
Inner Join
ikviews.sys_calendar today
On (calendar.month_of_calendar between today.month_of_calendar - 1
and today.month_of_calendar - 3)
Group By 1
)
da1
On z.acct_id = da1.acct_id

Dieter
Highlighted
Enthusiast

Re: derived table

Thank u