Distinct periodical

Database

Distinct periodical

Hi,

I am trying to create a fact table, i have to run it monthly and quarterly, when I run it monthly I have no problem, the problem is when I run it quarterly I have select distinct for the whole 3 months, but I am doing a left outer join to bring customer info, and I have to use the last month customer table. 

select
a.card_num,
b.acct_num,
cod_tran,
row_number() over(partition by card_num order by acct_num,cod_tran) as card_row_cnt_monthly,
row_number() over(partition by acct_num order by card_num,cod_tran) as acct_row_cnt_monthly
row_number() over(partition by card_num order by acct_num,cod_tran) as card_row_cnt_quarterly,
row_number() over(partition by acct_num order by card_num,cod_tran) as acct_row_cnt_quarterly
from
(
select
card_num,
cod_tran
from
trans_table
where
proc_date between 20140701 and 20140930
-- this will be changing monthly and quarterly
) a
left outer join
(
select
card_num,
acct_num
from
acct_table
where
proc_date between 20140901 and 20140930
-- this will be changing monthly
)
on a.num_tarjeta = b.num_tarjeta

As you can see in the example, but I must keep the monthly metrics, how can I use to like update only the quarterly columns

Thanks

1 REPLY
Senior Supporter

Re: Distinct periodical

I guess this somehow pseudo-code as the join column is missing in both derived tables. And giving that you don’t give the ddls and some test data it is a bit of guessing.

You might need to calculate the monthly and quarterly figucard_numres in two derived tables and join the result.

What you can try – where you might need to play around with the derived table a to get the right rows - is:

select
a.card_num,
b.acct_num,
cod_tran,
sum(last_month_flag) over(partition by card_num order by acct_num,cod_tran) as card_row_cnt_monthly,
sum(last_month_flag) over(partition by acct_num order by card_num,cod_tran) as acct_row_cnt_monthly
row_number() over(partition by card_num order by acct_num,cod_tran) as card_row_cnt_quarterly,
row_number() over(partition by acct_num order by card_num,cod_tran) as acct_row_cnt_quarterly
from
(
select
card_num,
cod_tran,
case when max(proc_date) over (partition by card_num) between 20140901 and 20140930 then 1 else 0 end as last_month_flag

from
trans_table
where
proc_date between 20140701 and 20140930
-- this will be changing monthly and quarterly
) a
left outer join
(
select
card_num,
acct_num
from
acct_table
where
proc_date between 20140901 and 20140930
-- this will be changing monthly
)
on a.num_tarjeta = b.num_tarjeta

This means the derived table a gives a flag 1 for all the card_num present in the last month and 0 for all others...