SQL Snowflake Min Date For Each Record & Group By Month

Question

I am using Snowflake to get some information from the data. I have a table in which the same employee (PRS_ID) can be included in many rows and I want to take the EARLIEST date (BGN_DATE) for each employee (PRS_ID). Next, I want to see how many employees I have each month.

Ideally if I could have in YYYY-MM format (YYYYMM will work too). What I was working with is below:

SELECT PRS_ID, MIN(BGN_DATE), MONTH(BGN_DATE)
FROM myTable
WHERE EMP_STS = 'T' 
GROUP BY PRS_ID, MONTH(BGN_DATE)

However, this will show me the data per employee (PRS_ID), which is too granular (as explained above). But when I remove "PRS_ID" from grouping I get below error:

SQL compilation error: error line 1 at position 7 'myTable.PRS_ID' in select clause is neither an aggregate nor in the group by clause.

Does anyone know how to fix it?

Thank you

Sample Data:

PRS_ID	EMP_STS	BGN_DATE
homsimps	T	2022-01-30
homsimps	T	2022-02-28
homsimps	T	2022-03-30
bartsimps	T	2022-01-30
bartsimps	T	2022-02-28
bartsimps	T	2022-03-31
lisasimps	T	2022-04-30
lisasimps	T	2022-05-31
lisasimps	T	2022-06-30
lisasimps	T	2022-07-30
margesimps	T	2022-02-28
margesimps	T	2022-03-30

Expected Outcome:

Period	Count
2022-01	2
2022-02	1
2022-03	0
2022-04	1

the problem is the information for minim date and the colum for the count have no joining information. if ou wanted the number of employees for the month and year fromthe min date ok — nbk, Jul 09 '22 at 20:57
Please update your question to show some sample data and the result you want to achieve — NickW, Jul 09 '22 at 21:00

Lukasz Szozda · Accepted Answer · 2022-07-10T08:40:34.663

2

Using aggregation twice:

WITH cte AS (
   SELECT PRR_ID, MIN(BGN_DATE) AS min_bgn_date
   FROM my_table
   WHERE EMP_STS = 'T'
   GROUP BY PRS_ID
)
SELECT TO_VARCHAR(min_bgn_date, 'YYYYMM') AS month, COUNT(*) AS cnt
FROM cte
GROUP BY TO_VARCHAR(min_bgn_date, 'YYYYMM');
-- GROUP BY month

edited Jul 10 '22 at 08:40

answered Jul 09 '22 at 21:23

Lukasz Szozda

162,964
23
234
275

1

to avoid having to repeat the `TO_VARCHAR...` clause in the GROUPBY you can just refer to the position `1` or if there is no input columns called `month` you can use that with no problems also. – Simeon Pilgrim Jul 10 '22 at 04:54

Yaakov Bressler · Answer 2 · 2022-07-11T18:41:18.170

1

There is a simpler function here, DATE_TRUNC will allow you to convert dates to months. You can then convert to the format you'd like.

WITH MY_CTE AS (
    SELECT
        PRS_ID,
        DATE_TRUNC(MONTH, MIN(BGN_DATE)) AS MONTH_START
    FROM
        myTable
    WHERE
        EMP_STS = 'T'
    GROUP BY 1
)
SELECT
    TO_CHAR(MONTH_START, 'yyyy-mm') AS PERIOD,
    COUNT(PRS_ID)
GROUP BY 1

edited Jul 11 '22 at 18:41

answered Jul 10 '22 at 16:45

Yaakov Bressler

9,056
2
45
69

SQL Snowflake Min Date For Each Record & Group By Month

2 Answers2