oracle - SQL query to get current and last year sales -
i have following table sales:
date store sales 1/1/2015 st01 12123 1/1/2015 st02 3123 1/1/2016 st01 4213 1/1/2016 st03 2134
when try self join year , last year sales closed store not showing up. result should this:
date store year sales last year sales 1/1/2016 st01 4213 1212 1/1/2016 st02 0 3123 1/1/2016 st03 2134 0
my query follows:
select cy.date, cy.store cy.sales, ly.sales sales cy, sales ly cy.store(+) = ly.store(+) , ly.date = cy.date - 365
oracle setup:
create table sales ( "date", store, sales ) select date '2015-01-01', 'st01', 12123 dual union select date '2015-01-01', 'st02', 3123 dual union select date '2016-01-01', 'st01', 4213 dual union select date '2016-01-01', 'st03', 2134 dual;
query:
select trunc( sysdate, 'yy' ) "date", store, sum( case when "date" = trunc( sysdate, 'yy' ) sales end ) "this year sales", sum( case when "date" = add_months( trunc( sysdate, 'yy' ), -12 ) sales end ) "last year sales" sales group store order store;
output:
date store year sales last year sales ------------------- ----- --------------- --------------- 2016-01-01 00:00:00 st01 4213 12123 2016-01-01 00:00:00 st02 3123 2016-01-01 00:00:00 st03 2134
Comments
Post a Comment