[SOLVED] How to filter for first occurrence of record?

How to filter for first occurrence of record?

I have a Daily table in Teradata. On any given month, we can have about 500 clients (about 20 added a month) that meet the criteria below.

I built a query to pull data the 1st occurrence where the criteria was met. I was able to run the code successfully BUT ran into a problem I can't seem to fix.

SELECT MyFakeDate, MyFakeAccount

Row_Number() Over (Partition By MyFakeAccount ORDER BY MyFakeDate) AS ROW_N

FROM MyFakeTable

WHERE FakeField = 'Y' and MyFakeDate Betweem TRUNC(Current_Date - Extract(Day FROM Current_Date),'Month' And Last_Day(Trunc(Current_Date - Extract(Day From Current_Date),'Month')) Qualify Row_Number() OVER (Partition By MyFakeAccount ORDER BY MyFakeDate) = 1;

My results should have total records for July that met the criteria - and the total records that met the criteria in August. The query I have looks at July and August and combines the 520 records instead of giving me separate record sets for July (500 records) and another set for August (520).

Solution

SELECT MyFakeDate, MyFakeAccount, ROW_NUMBER() OVER (PARTITION BY MyFakeAccount, EXTRACT(YEAR FROM MyFakeDate), EXTRACT(MONTH FROM MyFakeDate) ORDER BY MyFakeDate) AS ROW_N FROM MyFakeTable WHERE FakeField = 'Y' AND MyFakeDate BETWEEN TRUNC(Current_Date - INTERVAL '2' MONTH, 'MONTH') + INTERVAL '1' DAY AND LAST_DAY(TRUNC(Current_Date - INTERVAL '1' MONTH, 'MONTH')) QUALIFY ROW_NUMBER() OVER (PARTITION BY MyFakeAccount, EXTRACT(YEAR FROM MyFakeDate), EXTRACT(MONTH FROM MyFakeDate) ORDER BY MyFakeDate) = 1;