Can we use dynamic SQL or loops to automate this process?-CodePudding

I have a base dataset that is updated monthly. This contains information about employees such as Employer ID. I would like to create a table where we can see the leavers and joiners for each month.

The logic for this is as follows: if employee ID appears in latest month but not prior, then it is a joiner. If ID appears in prior but not latest, then it is a leaver.

The base data is appended and we also have a date variable, so I am able to produce a table of joiners/leavers with either CTEs or CREATE TABLE by specifying date(s) in where clause and merging.

I was wondering whether there was a way I could do this without manually creating multiple tables/CTES ? I.E. something that repeats the logic for a date range.

Aware it’s fairly simple to do in other coding languages but not sure how to go about it in SQL. Any help is greatly appreciated.

CodePudding user response：

Self-join the table. Same employee, adjancent months. I am multiplying a year be twelve and add the month, so as to get a continues month numbering (e.g. 12/2020 = 24252, 01/2021 = 24253). I am using a full outer join and only keep the outer joined rows, thus getting the leavers and the joiners.

select
  extract(year from coalesce(m_next.date, date_trunc('month', m_prev.date)   interval '1 month')) as year,
  extract(month from coalesce(m_next.date, date_trunc('month', m_prev.date)   interval '1 month')) as month,
  count(m_next.date) as joiners,
  count(m_prev.date) as leavers 
from mytable m_next
full outer join mytable m_prev 
  on  m_prev.employee_id = m_next.employee_id
  and extract(year from m_prev.date) * 12   extract(month from m_prev.date) =
      extract(year from m_next.date) * 12   extract(month from m_next.date) - 1
where m_next.date is null or m_prev.date is null
group by
  extract(year from coalesce(m_next.date, date_trunc('month', m_prev.date)   interval '1 month')),
  extract(month from coalesce(m_next.date, date_trunc('month', m_prev.date)   interval '1 month'))
order by
  extract(year from coalesce(m_next.date, date_trunc('month', m_prev.date)   interval '1 month')),
  extract(month from coalesce(m_next.date, date_trunc('month', m_prev.date)   interval '1 month'));

Demo: https://dbfiddle.uk/?rdbms=postgres_14&fiddle=1c66b00a71d484cd3951baa0956ace63