logic for subtracting and getting variance in oracle

how to write a logic for getting the below output

with b2 as
(
select COUNT(SaLE) As cnt, 
TO_CHAR(date,'YYYY-MON') As Period

  from Order
  where date between date '2020-02-01' and date '2020-02-28'
group by TO_CHAR(BATCH_DTE_CYMD,'YYYY-MON') 

union all
select  COUNT(Sale) As cnt,
TO_CHAR(Date,'YYYY-MON') As Period

  from Order
  where date between date '2020-01-01' and date '2020-01-31'
group by TO_CHAR(Date,'YYYY-MON') 

)
select  cnt, Period,
       100*(cnt-lag(cnt,1,cnt) over (order by period))
       /lag(cnt,1,cnt) over (order by period)
       as "variance(%)"
  from b2
 order by period 

i am getting this ouput

Cnt   | period     |    variance(%)
11917 | 2020-FEB   |    0
11707 | 2020-JAN   |    -1.76218847025258034740286984979441134514

but i want the this output

Cnt   | period     |    variance(%)                                               | sign                                                                                
11917 | 2020-FEB   |    JAN-FEB (Variance we get in feb in % (with no decimal))   | Increase/decrease 
11707 | 2020-JAN   |    0                                                         |  0

Answer

The issue with your code is, You are using LAG on the CHAR column(PERIOD) which is not correct as 2020-FEB is lower than the 2020-JAN when the comparison is in the string.

You must use them as the date in LAG function as following:

WITH B2 AS (
    SELECT COUNT(SALE) AS CNT,
           TRUNC(DATE, 'MON') AS PERIOD
      FROM ORDER
     WHERE DATE BETWEEN DATE '2020-01-01' AND DATE '2020-02-28'
    GROUP BY TRUNC(DATE, 'MON')
)
SELECT
    CNT,
    TO_CHAR(PERIOD,'YYYY-MON') AS PERIOD,
    100 * ( CNT - LAG(CNT, 1, CNT) OVER( ORDER BY PERIOD ) ) 
    / LAG(CNT, 1, CNT) OVER(ORDER BY PERIOD) 
    AS "variance(%)"
FROM B2
ORDER BY PERIOD

Cheers!!