Return first order by each month

I am trying to modify my current query to return the first order for each month in each year.

Here is my full table and my current query.

select orderdate, sum(UnitPrice*Qty) 
AS month_firstOrder_total
from OrderByDate
group by OrderDate

Answer

Try something like this:

WITH DataSource AS
(
    select *
          ,ROW_NUMBER() OVER (PARTITION BY YEAR(orderdate), MONTH(orderdate) ORDER BY orderdate ASC) rn
    from OrderByDate
)
SELECT *
FROM DataSource
WHERE rn = 1;

You can get the total sum per order date if you want, too:

WITH DataSource AS
(
    select *
          ,ROW_NUMBER() OVER (PARTITION BY YEAR(orderdate), MONTH(orderdate) ORDER BY orderdate ASC) rn
          ,sum(UnitPrice*Qty) OVER (PARTITION BY orderdate) as sum_qty
    from OrderByDate
)
SELECT *
FROM DataSource
WHERE rn = 1