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 :)

CodePudding user response：

Given just a list of dates you could use row_number and a conditional expression to arbitrarily assign one row of each month a value of 1000

select *, 
  Iif(Row_Number() over(partition by Month(date) order by (select null)) = 1, 1000, 0) Amount
from t
order by [date], Amount desc;

CodePudding user response：

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