How do I make array dynamic in LATERAL VIEW EXPLODE?

Question

I have a bunch of student records in the table.

school  stduent_id   start_date    end_date
111      123         2010-02-03   2012-02-03
222      345         2013-02-03   2014-02-03
222      567         2015-02-03   2018-02-03

I wanted to count how many students each school has each year, say from 2014 all the way to 2021.

Currently, I am using lateral view, but it is hardcoded:

SELECT school
    , yrs
    , COUNT(DISTINCT stduent_id) AS cnt
FROM TABLE
LATERAL VIEW EXPLODE(ARRAY(2014, 2015, 2016, 2017, 2018, 2019, 2020, 2021))a AS yrs
WHERE 1=1
    AND YEAR(start_date) <= yrs
    AND YEAR(end_date) >= yrs

I wanted this to be updated automatically when the year moves, eg, now should be 2014-2021, next year it should be 2015-2022.

Any idea how should I do this? Thank you!

leftjoin · Accepted Answer · 2021-02-16T12:08:21.113

You can generate year range using posexplode:

with year_range as(--7 years back from current year till current year
select year(current_date)-7+i as yr
from ( select posexplode(split(space(7),' ')) as (i,x) 
) s
)

SELECT school
    , y.yr
    , COUNT(DISTINCT stduent_id) AS cnt
FROM TABLE
     CROSS JOIN year_range y
GROUP BY school, y.yr
WHERE 1=1
    AND YEAR(start_date) <= y.yr
    AND YEAR(end_date) >= y.yr

score 0 · Answer 2 · answered Feb 16 '21 at 08:07

0

SELECT school, yrs, COUNT(DISTINCT stduent_id) AS cnt
        FROM TABLE
        WHERE yrs 
        Between Year((DATEADD(YEAR,-7,GETDATE())))
        and
        Year(GETDATE())

answered Feb 16 '21 at 08:07

Michiel

94
5

2

While this code may provide a solution to the question, it's better to add context as to why/how it works. This can help future users learn and eventually apply that knowledge to their own code. You are also likely to have positive-feedback/upvotes from users, when the code is explained. – Amit Verma Feb 16 '21 at 08:54

How do I make array dynamic in LATERAL VIEW EXPLODE?

2 Answers2