I am using SQL and I would like this number '1000' to appear once per month. I have a record set which has the first of every month appearing multiple times. I would like the number '1000' to appear once only and then '0' for the remaining records until the next month appears. I would like the below please- maybe a case type statement/order parition by? I am using SQL Server 2018 @@SQLSERVER. Please see table below of how i would like the data to appear.
Many Thanks :)
| Date | Amount |
|---|---|
| 01/01/2022 | 1000 |
| 01/01/2022 | 0 |
| 01/01/2022 | 0 |
| 01/02/2022 | 1000 |
| 01/02/2022 | 0 |
| 01/02/2022 | 0 |
| 01/03/2022 | 1000 |
| 01/03/2022 | 0 |
Solution for your problem:
WITH CT1 AS
(
SELECT *,
ROW_NUMBER() OVER(PARTITION BY CONCAT(MONTH([Date]),YEAR([Date])) ORDER BY [Date]) as rn
FROM your_table
)
SELECT [Date],
CASE WHEN rn = 1 THEN 1000 ELSE 0 END AS Amount
FROM CT1;
Working Example: DB<>Fiddle Link