SQL Server Select MIN day within each month

fdx2calv 于 2023-08-02 发布在其他

关注(0)|答案(1)|浏览(98)

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

sql-server

来源：https://stackoverflow.com/questions/76758594/select-min-day-within-each-month

1条答案

按热度按时间

34gzjxbg1#

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

赞(0）回复(0）举报 2023-08-02

我来回答

SQL Server Select MIN day within each month

1条答案

相关问题

热门标签

最新问答