Home Table Self Join for Year over Year comparison
Reply: 1

Table Self Join for Year over Year comparison

Jim Cole
1#
Jim Cole Published in 2018-02-13 18:24:07Z

I am having trouble self joining a table of data to provide year over year results in a single row.

My Data is currently stored as follows in table sales. I can work with either Postgres or sqlite3.

we(date) | store | category | planu | planrev | merchu | merchrev

by desired outcome is:

I need to be able to show values for LY for 1/7/17 in the last 4 columns.

I will then union the results with those for all other partners replicating the same query for other tables.

It can be assumed that all current year data will be we>1/1/18 to match the date 364 days ago for previous year results.

Through reading other posts I think I may need to craft a CTE query, I just don't know where to start.

I hope this was clear.

Any help in working this out would be greatly appreciated.

dnswlt
2#
dnswlt Reply to 2018-02-13 19:51:11Z

It looks like you want to join on identical store and category as well as same month and day of the year. That would look as follows in PostgreSQL:

select 
  'PartnerA' as channel,
  cy.we as date,
  cy.month,
  cy.year,
  cy.store,
  'PartnerA ' || cy.store as ch_store,
  cy.category,
  cy.planu,
  cy.planrev,
  cy.merchu,
  cy.merchrev,
  ly.planu as planu_ly,
  ly.planrev as planrev_ly,
  ly.merchu as merchu_ly,
  ly.merchrev as merchrev_ly
from sales cy
join sales ly on cy.store = ly.store and cy.category = ly.category
  and cy.we - interval '1 year' = ly.we
;   
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.310485 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO