SQL Group and filter by Max monthly value

Question

I would like to make a query that dynamically filters to only include the last available monthly date.

The only values to be left in the screenshot below should be the last available value of the month.

You can see 4 April values available here, but only the last one should be visible and have the date value if the first of the month.

The result should be as following

Help would be greatly appreciated

Don't use image into your question, use tables instead – 0xNIC Apr 17 '23 at 14:22 — 0xNIC, Apr 17 '23 at 14:22

score 0 · Answer 1 · answered Apr 17 '23 at 14:29

We can use ROW_NUMBER along with a calculation to find the first of the month:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY CONVERT(VARCHAR(7), SnapahotDate, 120)
                                 ORDER BY SnapshotDate DESC) rn
    FROM yourTable
)

SELECT DATEADD(month, DATEDIFF(month, 0, SnapshotDate), 0) AS SnapshotDate,
       NrAccounts,
       Status,
       AccountType,
       RegisteredCountry,
       RegisteredCountryID
FROM cte
WHERE rn = 1
ORDER BY SnapshotDate;

score -1 · Answer 2 · answered Apr 17 '23 at 16:51

You can use window function and Cte to solve the problem

you use FORMAT to find part YYYY MM of date


;WITH _Lstdate AS (
    SELECT 
              SnapshotDate
             ,NrAccounts
             ,Status
             ,AccountType
             ,RegisteredCountry
             ,RegisteredCountryID
             ,  Rank() OVER (
                                  PARTITION BY  FORMAT (SnapshotDate, 'YYYY-MM')
                                  ORDER BY SnapshotDate desc
                             ) rw
    FROM Lstdate
)

SELECT 
       FORMAT (SnapshotDate, 'yyyy-MM')  +'-01'  AS SnapshotDate,
       NrAccounts,
       Status,
       AccountType,
       RegisteredCountry,
       RegisteredCountryID

FROM _Lstdate
WHERE rw = 1
ORDER BY SnapshotDate

You can insert the basic data with the following statements:

drop table if exists Lstdate

create table Lstdate (SnapshotDate date,NrAccounts int,Status varchar(100),RegisteredCountry varchar(100)
,RegisteredCountryId int,AccountType varchar(100))


insert into Lstdate
(SnapshotDate,AccountType,NrAccounts,Status,RegisteredCountry,RegisteredCountryId)
          select '2023/01/01' as SnapshotDate ,'BELGIAN_NRN' as  AccountType,585  as NrAccounts,'ACTIVATED' as Status,'Belgian' as RegisteredCountry,1 as RegisteredCountryId
union all select '2023/02/01' as SnapshotDate ,'BELGIAN_NRN' as  AccountType,585  as NrAccounts,'ACTIVATED' as Status,'Belgian' as RegisteredCountry,1 as RegisteredCountryId
union all select '2023/03/01' as SnapshotDate ,'BELGIAN_NRN' as  AccountType,585  as NrAccounts,'ACTIVATED' as Status,'Belgian' as RegisteredCountry,1 as RegisteredCountryId
union all select '2023/03/29' as SnapshotDate ,'BELGIAN_NRN' as  AccountType,585  as NrAccounts,'ACTIVATED' as Status,'Belgian' as RegisteredCountry,1 as RegisteredCountryId
union all select '2023/04/11' as SnapshotDate ,'BELGIAN_NRN' as  AccountType,7578 as NrAccounts,'ACTIVATED' as Status,'Belgian' as RegisteredCountry,1 as RegisteredCountryId
union all select '2023/04/13' as SnapshotDate ,'BELGIAN_NRN' as  AccountType,58   as NrAccounts,'ACTIVATED' as Status,'Belgian' as RegisteredCountry,1 as RegisteredCountryId
union all select '2023/04/14' as SnapshotDate ,'BELGIAN_NRN' as  AccountType,58   as NrAccounts,'ACTIVATED' as Status,'Belgian' as RegisteredCountry,1 as RegisteredCountryId
union all select '2023/04/15' as SnapshotDate ,'BELGIAN_NRN' as  AccountType,22   as NrAccounts,'ACTIVATED' as Status,'Belgian' as RegisteredCountry,1 as RegisteredCountryId

SQL Group and filter by Max monthly value

2 Answers2