I have the following table "sales"

and I want to compare sales current week vs the same week last month and get the following result:

CodePudding user response：

select rn  as week_number
      ,[5] as revenue_may
      ,[6] as revenue_june
from  (
       select revenue
             ,row_number() over(partition by month(dte) order by dte) as rn
             ,month(dte) as mnth
      from t
      )tmp
pivot
(max(revenue) for mnth in([5], [6])) pvt

Fiddle

CodePudding user response：

Based on given data, following is for postgresql -

with data as (
    select date1,substring(date1 , 1,10) as date_col,
    substring(date1 , '[[:alpha:]] [[:digit:]] ') as week_col,
    revenue
    from revenue_tab
)
select 
    d1.date1, 
    d1.revenue as june_rev,
    d2.revenue as june_rev
from
    (select * from data 
     where extract(month from to_date(date_col,'yyyy-mm-dd'))=6) d1
join
    (select * from data 
     where extract(month from to_date(date_col,'yyyy-mm-dd'))=5) d2
ON d1.week_col = d2.week_col
AND extract(year from to_date(d1.date_col,'yyyy-mm-dd')) 
    = extract(year from to_date(d2.date_col,'yyyy-mm-dd'))
 ;

DB fiddle here.