CODEWITHSUNDEEP
sqldynamichivehiveqllateral-join
FakeSake
FakeSake

Reputation: 23

How do I make array dynamic in LATERAL VIEW EXPLODE?

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!

Upvotes: 1

Views: 381

Answers (2)

leftjoin
leftjoin

Reputation: 38335

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

Upvotes: 1

Michiel
Michiel

Reputation: 94

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

Upvotes: 0

Related Questions