getting additions and removals

Moderator: NorbertKrupa

Post Reply
dazz
Newbie
Newbie
Posts: 12
Joined: Sat May 26, 2012 3:26 pm

getting additions and removals

Post by dazz » Tue Aug 04, 2015 5:47 pm

Hello!

I am trying to determine the most efficient way to find Additions and removals. The key field is fpr_owning_acct_no||FPR_SERV_NO||FPR_PRODUCT_CODE , and I am using Lead and Lag functions to determine if this key was added or removed by month.

However I don’t think I am doing it the most efficient way. Maybe these functions can be used a more efficient way, or there are more appropriate functions that can be used.

The following is the core script. To this I just need two more columns, Adds and Removals:
select a.FPR_SERV_NO,DATE_PART('year', FPR_TIME_STAMP)*100+DATE_PART('month', FPR_TIME_STAMP) as TIME_STAMP, a.fpr_owning_acct_no, a.FPR_PRODUCT_CODE,
a.FPR_RENTAL_CHARGE,a.fpr_owning_acct_no||a.FPR_SERV_NO||a.FPR_PRODUCT_CODE as serv
from cis_prod.CST_FULL_PERIOD_RENTALS a
where a.FPR_SERV_NO in ('6231021', '6622997')
and a.fpr_product_code in (36,122)

I ended up with the following script which I don’t think is efficient enough:
select e.* from (
select
d.*,c.YEAR_MONTH,c.serv2,
case when lag (d.serv) over ( partition by c.serv2 order by c.YEAR_MONTH) is null and d.serv is not null and (d.TIME_STAMP > (min(d.TIME_STAMP) over())) then 1 end Adds ,
case when lead (d.serv) over ( partition by c.serv2 order by c.YEAR_MONTH) is null and d.serv is not null and (c.YEAR_MONTH <= (max(d.TIME_STAMP) over())) then 1 end Removals

from // part A
(select * from
(select distinct YEAR_MONTH from "public".Dim_Dates where YEAR >=2014 and DATE_DAY <= now())a
cross join
(select distinct a.fpr_owning_acct_no||a.FPR_SERV_NO||a.FPR_PRODUCT_CODE as serv2
from cis_prod.CST_FULL_PERIOD_RENTALS a
where a.FPR_SERV_NO in ('6231021', '6622997') and a.fpr_product_code in (36,122)
)b
)c
left join //part B
(select a.FPR_SERV_NO,DATE_PART('year', FPR_TIME_STAMP)*100+DATE_PART('month', FPR_TIME_STAMP) as TIME_STAMP, a.fpr_owning_acct_no, a.FPR_PRODUCT_CODE,
a.FPR_RENTAL_CHARGE,a.fpr_owning_acct_no||a.FPR_SERV_NO||a.FPR_PRODUCT_CODE as serv
from cis_prod.CST_FULL_PERIOD_RENTALS a
where a.FPR_SERV_NO in ('6231021', '6622997')
and a.fpr_product_code in (36,122))d
on c.YEAR_MONTH = d.TIME_STAMP
and c.serv2 = d.serv
)e
where e.FPR_SERV_NO is not null
order by e.serv2, e.YEAR_MONTH

I have ran it in steps and attached the results to try and explain what I am doing.
Part A – join the key field (serv2) to all calendar months from Jan 2014 going forward
select * from
(select distinct YEAR_MONTH from "public".Dim_Dates where YEAR >=2014 and DATE_DAY <= now())a
cross join
(select distinct a.fpr_owning_acct_no||a.FPR_SERV_NO||a.FPR_PRODUCT_CODE as serv2
from cis_prod.CST_FULL_PERIOD_RENTALS a
where a.FPR_SERV_NO in ('6231021', '6622997') and a.fpr_product_code in (36,122)
)b

Part B – pulls all the months (TIME_STAMP ) with data for serv2
select a.FPR_SERV_NO,DATE_PART('year', FPR_TIME_STAMP)*100+DATE_PART('month', FPR_TIME_STAMP) as TIME_STAMP, a.fpr_owning_acct_no, a.FPR_PRODUCT_CODE,
a.FPR_RENTAL_CHARGE,a.fpr_owning_acct_no||a.FPR_SERV_NO||a.FPR_PRODUCT_CODE as serv
from cis_prod.CST_FULL_PERIOD_RENTALS a
where a.FPR_SERV_NO in ('6231021', '6622997')
and a.fpr_product_code in (36,122)

A left join B – joins the two above (showing months in between where there is no data for serv2)

Result – select the following from above (A left join B)
select d.*,c.YEAR_MONTH,c.serv2,
case when lag (d.serv) over ( partition by c.serv2 order by c.YEAR_MONTH) is null and d.serv is not null and (d.TIME_STAMP > (min(d.TIME_STAMP) over())) then 1 end Adds ,
case when lead (d.serv) over ( partition by c.serv2 order by c.YEAR_MONTH) is null and d.serv is not null and (c.YEAR_MONTH <= (max(d.TIME_STAMP) over())) then 1 end Removals

Final result (removing nulls) – the nulls are removed

If there is a more efficient way of doing this, please let me know

Thanks!
Attachments
Sample.xlsx
(17.76 KiB) Downloaded 559 times

dazz
Newbie
Newbie
Posts: 12
Joined: Sat May 26, 2012 3:26 pm

Re: getting additions and removals

Post by dazz » Fri Aug 07, 2015 3:16 pm

Hi, to simplify what Im trying to do..

I have a script as follows which extracts info for a.FPR_SERV_NO by month. Some months have no data for the FPR_SERV_NO. I want to flag those as removals, and if it is added back, I want to flag it as an addition. Also the first time it is added, I want to flag it as an addition. Please see attached for sample data, and the Output I want to get. Ive been trying to use Lead and Lag, but not getting it to work properly. Doesnt have to be these functions though, but anything that will work. Will appreciate any help..thanks!

select a.FPR_SERV_NO,a.TIME_STAMP, a.FPR_RENTAL_CHARGE
from cis_prod.CST_FULL_PERIOD_RENTALS a
where a.FPR_SERV_NO in ('6231021', '6622997')
Attachments
Sample.xlsx
(10.55 KiB) Downloaded 461 times

Post Reply

Return to “Vertica SQL”