create table del_test_oper_2_0 (client_pin varchar(25), value_day varchar(25), turnover int);
INSERT ALL
INTO del_test_oper_2_0(client_pin,value_day , turnover ) values ('AQ256','2019-06-02', 2000)
INTO del_test_oper_2_0(client_pin,value_day , turnover ) values ('AQ256','2019-06-03', 3000)
INTO del_test_oper_2_0(client_pin,value_day , turnover ) values ('AQ256','2019-06-04', 1000)
INTO del_test_oper_2_0(client_pin,value_day , turnover ) values ('AQ256','2019-06-05', 10000)
INTO del_test_oper_2_0(client_pin,value_day , turnover ) values ('AQ256','2019-06-24', 20000)
INTO del_test_oper_2_0(client_pin,value_day , turnover ) values ('AQ256','2019-06-30', 35000)
INTO del_test_oper_2_0(client_pin,value_day , turnover ) values ('AQ256','2019-07-06', 3500)
INTO del_test_oper_2_0(client_pin,value_day , turnover ) values ('AQ256','2019-07-08', 10500)
INTO del_test_oper_2_0(client_pin,value_day , turnover ) values ('AQ256','2019-07-09', 5000)
INTO del_test_oper_2_0(client_pin,value_day , turnover ) values ('AQ256','2019-07-15', 100000)
INTO del_test_oper_2_0(client_pin,value_day , turnover ) values ('AQ256','2019-07-21', 85000)
INTO del_test_oper_2_0(client_pin,value_day , turnover ) values ('AQ256','2019-07-27', 95000)
INTO del_test_oper_2_0(client_pin,value_day , turnover ) values ('AQ345','2019-07-01', 1500)
INTO del_test_oper_2_0(client_pin,value_day , turnover ) values ('AQ345','2019-07-02', 2500)
INTO del_test_oper_2_0(client_pin,value_day , turnover ) values ('AQ345','2019-07-20', 4000)
INTO del_test_oper_2_0(client_pin,value_day , turnover ) values ('AQ345','2019-07-22', 65000)
INTO del_test_oper_2_0(client_pin,value_day , turnover ) values ('AQ345','2019-07-23', 25000)
INTO del_test_oper_2_0(client_pin,value_day , turnover ) values ('AQ345','2019-07-28', 15000)
SELECT * FROM DUAL;
COMMIT;
select * from del_test_oper_2_0;
with q1 as ( ---ΡΡΠΌΠΌΠ° ΡΡΠ°Π½Π·Π°ΠΊΡΠΈΠΉ Π² Π΄Π΅Π½Ρ Π½Π° ΠΊΠ»ΠΈΠ΅Π½ΡΠ°
select
VALUE_DAY
,CLIENT_PIN
,sum (TURNOVER) as sum_TURN_day
from del_test_oper_2_0
group by
VALUE_DAY
,CLIENT_PIN
)
,q2 as ( ---Π Π°ΡΡΠ΅Ρ ΠΊΠ΅ΡΠ±Π΅ΠΊΠ° Π½Π° Π΄Π΅Π½Ρ ΠΏΠΎ ΡΡΠ»ΠΎΠ²ΠΈΡΠΌ
select
distinct
VALUE_DAY
,CLIENT_PIN
,SUM_TURN_DAY
,SUM_TURN_DAY * 0.05 as sum_cashbak --Π±Π΅Π· ΠΎΠ³ΡΠ°Π½ΠΈΡΠ΅Π½ΠΈΡ
from q1
order by VALUE_DAY
)
,q3 as ( ---Π‘ΡΠΌΠΌΠ° ΡΡΠ°Π½Π·Π°ΠΊΡΠΈΠΉ ΠΈ ΠΊΠ΅ΡΠ±Π΅ΠΊΠ° Ρ ΠΎΠ³ΡΠ°Π½ΠΈΡΠ΅Π½ΠΈΡΠΌΠΈ ΠΈ Π±Π΅Π· Π½Π° ΠΊΠ»ΠΈΠ΅Π½ΡΠ° Π² ΠΌΠ΅ΡΡΡ
select
LAST_DAY(TO_DATE(VALUE_DAY, 'yyyy.mm.dd')) AS VALUE_mnt
,CLIENT_PIN
,sum(SUM_TURN_DAY) as SUM_TURN_mnt
,sum(SUM_CASHBAK) as SUM_CASHBAK_mnt
from q2
group by
LAST_DAY(TO_DATE(VALUE_DAY, 'yyyy.mm.dd'))
,CLIENT_PIN
)
,q4 as (--- ΠΠ±ΡΠ°Ρ ΡΡΠΌΠΌΠ° ΡΡΠ°Π½Π·Π°ΠΊΡΠΈΠΉ ΠΈ ΡΠ°ΡΡΡΠΈΡΠ°Π½Π½ΡΠΉ ΠΊΠ΅ΡΠ±Π΅ΠΊ Π½Π° ΠΌΠ΅ΡΡΡ ΠΏΠΎ Π²ΡΠ΅ΠΌ ΠΊΠ»ΠΈΠ΅Π½ΡΠ°ΠΌ
select
LAST_DAY(TO_DATE(a.VALUE_DAY, 'yyyy.mm.dd')) AS VALUE_mnt
,sum(a.SUM_TURN_DAY) as SUM_TURN_mnt
,sum(a.SUM_CASHBAK) as SUM_CASHBAK_mnt
,case when SUM_TURN_DAY * 0.05 >=1000 then 1000 else SUM_TURN_DAY * 0.05 end as cashback_sum_day -- Ρ ΠΎΠ³ΡΠ°Π½ΠΈΡΠ΅Π½ΠΈΠ΅ΠΌ
from q2 a
--left join q3 b on a.CLIENT_PIN=b.CLIENT_PIN
group by
LAST_DAY(TO_DATE(VALUE_DAY, 'yyyy.mm.dd'))
,case when SUM_TURN_DAY * 0.05 >=1000 then 1000 else SUM_TURN_DAY * 0.05 end
)
select * from q4
Telegram: DB developers channel