SELECT distinct B.C_STORE_ID,
sum(case
when add_months(to_date('${YEAR}' || '-' ||
Lpad('${MONTH}', 2, '0'),
'YYYY/MM'),
-12) > trunc(to_date(b.datebegin, 'yyyy/mm/dd'),'mm') then
case
when add_months(to_date('${YEAR}' || '-' ||
Lpad('${MONTH}', 2, '0'),
'YYYY/MM'),
-12) > trunc(to_date(b.dateend, 'yyyy/mm/dd'),'mm') then
0
when to_date(b.dateend, 'yyyy/mm/dd') between
trunc(add_months(to_date('${YEAR}' || '-' ||
Lpad('${MONTH}', 2, '0'),
'YYYY/MM'),
-12),
'mm') and trunc(to_date('${YEAR}' || '-' ||
Lpad('${MONTH}', 2, '0'),
'YYYY/MM'),
'mm') - 1 then