sqlsql-serversql-order-bypartitionrownum

Select MIN day within each month

I am trying to pull min day within each month/Year (it is not always the first) - and balance and account that is attached to that day

This is what I currently have:

SELECT [ACCT10]
  ,[MyDATE]
  ,[BALANCE]
   rownum=row_number() OVER(
   PARTITION BY DATEADD(month,DATEDIFF(month,0,MyDate),0),[ACCT10]
   order by MyDate asc
   ) FROM
   [Accounts]
   )
    SELECT [ACCT10]
    ,[MyDATE]
    ,[BALANCE]
   FROM ranked where rownum = 1
   FROM [Accounts]

DATA:

Current

ACCT10 MyDate BALANCE
X546785e 1/1/2023 57200
X546785e 1/2/2023 57500
X546785e 1/5/2023 59050
X546785e 2/3/2023 57800
X546785e 2/4/2023 60500
X546785e 2/5/2023 61200
X5s5485e 1/2/2023 16500
X5s5485e 1/5/2023 16520
X5s5485e 1/6/2023 19800
X5s5485e 2/1/2023 15000

Desired

ACCT10 MyDate BALANCE
X546785e 1/1/2023 57200
X546785e 2/3/2023 57800
X5s5485e 1/2/2023 16500
X5s5485e 2/1/2023 15000

Unfortunately, I am unable to use the EXTRACT command

Solution

  • The basics of that query seem OK, it just needs some tweaking to correct the syntax:

    WITH ranked
AS (
    SELECT [ACCT10]
        , [MyDATE]
        , [BALANCE]
        , row_number() OVER (
            PARTITION BY DATEADD(month, DATEDIFF(month, 0, MyDate), 0)
            , [ACCT10] ORDER BY MyDate ASC
            ) AS rownum
    FROM [Accounts]
    )
SELECT [ACCT10]
    , [MyDATE]
    , [BALANCE]
FROM ranked
WHERE rownum = 1