CODEWITHSUNDEEP
sqlamazon-redshift
WGS
WGS

Reputation: 14169

Dynamically accommodate X count for Y date period in Z periods in SQL

I have a simple table with two columns: emp_id and date. Every time an employee punches for the day, the date gets appended to this table.

At certain intervals, I have to check which employees are working on 3 or less days per 2 weeks for the last 8 weeks. My query is as follows:

select x.emp_id
from
(
select
emp_id
, sum(case when tt.date >= dateadd('week', -8, '2016-08-15') and tt.date < dateadd('week', -6, '2016-08-15') then 1 else 0 end) as four_biweeks_ago
, sum(case when tt.date >= dateadd('week', -6, '2016-08-15') and tt.date < dateadd('week', -4, '2016-08-15') then 1 else 0 end) as three_biweeks_ago
, sum(case when tt.date >= dateadd('week', -4, '2016-08-15') and tt.date < dateadd('week', -2, '2016-08-15') then 1 else 0 end) as two_biweeks_ago
, sum(case when tt.date >= dateadd('week', -2, '2016-08-15') and tt.date < '2016-08-15' then 1 else 0 end) as last_biweek
from temps_timetable tt
where tt.date >= dateadd('week', -8, '2016-08-15')
) x
where x.four_biweeks_ago <= 3
and x.three_biweeks_ago <= 3
and x.two_biweeks_ago <= 3
and x.last_biweek <= 3

However, I want to "upgrade" this query and make it dynamic to accommodate any number of past weeks. Since this query is ran from an in-house interface, the only fields that are dynamic are the reference date (in this case, 2016-08-15) and the number of max dates each (3). I can make the number of past biweeks flexible as well via user interface, but I don't know how to upgrade the query to handle more than the 4 sum lines since this should change the query itself. For example, making it the last 10 weeks means I have to add sum(case when tt.date >= dateadd('week', -10, '2016-08-15') and tt.date < dateadd('week', -8, '2016-08-15') then 1 else 0 end) as five_biweeks_ago. The alias doesn't matter, btw.

I tried reading up on PIVOT to deal with this, but the logic is currently escaping me.

TL;DR: How do I change my query so I can dynamically accommodate X dates per Y weeks for the last Z weeks?

Upvotes: 1

Views: 150

Answers (1)

moertel
moertel

Reputation: 1569

The key to pivoting the table is using floor() (see Redshift documentation) and combining it with datediff(), which allows to easily partition by a given interval, i.e. biweekly.

So this should yield the same result as your query:

with _reference as (
  select
      '2016-08-15'::date as date
     , 8::int            as weeks_ago  -- Z
     , 2::float          as partition  -- Y
     , 3::int            as max_count  -- X
),
_stats as (
    select
        emp_id
        /** If datediff=3 and partition=2, this returns 1: */
      , floor(datediff(week, temps_timetable.date, _reference.date) / _reference.partition) as partition_in_past
      , count(distinct temps_timetable.date)
    from
        _reference, temps_timetable
    where
        datediff(week, temps_timetable.date, _reference.date) <= _reference.weeks_ago
    and
        temps_timetable.date < _reference.date
    group by
        emp_id
      , partition_in_past
)
select emp_id
from _stats, _reference
group by emp_id
having sum(case when _stats.count > _reference.max_count then 1 else 0 end) = 0
;

Upvotes: 1

Related Questions