Get value for the first date in month

Question

I have weekly data of each product stock. I want to group it by year-month and get the first value of each month. In other words, I want to get the opening stock of each month, regardless the day of the month.

+------------+---------+
|   MyDate   | MyValue |
+------------+---------+
| 2018-01-06 |       2 |*
| 2018-01-13 |       7 |
| 2018-01-20 |       5 |
| 2018-01-27 |       2 |
| 2018-02-03 |       3 |*
| 2018-02-10 |      10 |
| 2018-02-17 |       6 |
| 2018-02-24 |       4 |
| 2018-03-03 |       7 |*
| 2018-03-10 |       5 |
| 2018-03-17 |       3 |
| 2018-03-24 |       4 |
| 2018-03-31 |       6 |
+------------+---------+

Desired results:

+----------------+---------+
| FirstDayOfMonth| MyValue |
+----------------+---------+
| 2018-01-01     |       2 |
| 2018-02-01     |       3 |
| 2018-03-01     |       7 |
+----------------+---------+

I thought this might work, but it ain't.

select 
    [product],
    datefromparts(year([MyDate]), month([MyDate]), 1),
    FIRST_VALUE(MyValue) OVER (PARTITION BY [Product], YEAR([MyDate]), MONTH([MyDate]) ORDER BY [MyDate] ASC) AS MyValue
from 
    MyTable 
group by 
    [Product],
    YEAR([MyDate]), MONTH([MyDate])

Edit. Thank you. The accent in my question is not how to get the first day of the month. I know that there are different techniques for that.

The accent is how to get the FIRST value in month (the opening stock). If there is a chance to get the closing stock in one shot - it would be great. The answers based on ROW_NUMBER do not allow to get closing stock in one shot, would require two joins.

Edit after accepting answer
Please consider John Cappelletti's answer as an alternative to the accepted one: https://stackoverflow.com/a/53559750/1903793

Answer 1

You can use apply & eomonth to find the last day of month & add one day :

select distinct dateadd(day, 1, eomonth(t1.mydate, -1)) as FistDayOfMonth, t1.myvalue
from table t cross apply
     ( select top (1) t1.mydate, t1.myvalue
       from table t1
       where t1.product = t.product and
             year(t1.MyDate) = year(t.MyDate) and month(t1.MyDate) = month(t.MyDate)
       order by t1.mydate
     ) t1;

Answer 2

You don't really need the GROUP BY if you have chosen the window function route:

SELECT Product, DATEADD(DAY, 1, EOMONTH(MyDate, -1)) AS Month, MyValue
FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY Product, DATEADD(DAY, 1, EOMONTH(MyDate, -1)) ORDER BY MyDate) AS rn
    FROM t
) AS x
WHERE rn = 1

UPDATE

To get the last row for the month just do a UNION ALL <above query> but change the order by clause to ORDER BY MyDate DESC. This will give you two rows per product-month.

Answer 3

Could also use a rowNumber and cte.

DEMO

WITH CTE as (
SELECT '2018-01-06' myDate,        2 Myvalue UNION ALL
SELECT '2018-01-13',        7 UNION ALL
SELECT '2018-01-20',        5 UNION ALL
SELECT '2018-01-27',        2 UNION ALL
SELECT '2018-02-03',        3 UNION ALL
SELECT '2018-02-10',       10 UNION ALL
SELECT '2018-02-17',        6 UNION ALL
SELECT '2018-02-24',        4 UNION ALL
SELECT '2018-03-03',        7 UNION ALL
SELECT '2018-03-10',        5 UNION ALL
SELECT '2018-03-17',        3 UNION ALL
SELECT '2018-03-24',        4 UNION ALL
SELECT '2018-03-31',        6),

CTE2 as (SELECT *
              , Row_Number() over (partition by DATEADD(month, DATEDIFF(month, 0, MyDate), 0) order by myDate) RN   
         FROM CTE)

SELECT DATEADD(month, DATEDIFF(month, 0, MyDate), 0), MyValue 
FROM cte2 
WHERE RN = 1

Giving us:

+----+---------------------+---------+
|    |  (No column name)   | MyValue |
+----+---------------------+---------+
|  1 | 01.01.2018 00:00:00 |       2 |
|  2 | 01.02.2018 00:00:00 |       3 |
|  3 | 01.03.2018 00:00:00 |       7 |
+----+---------------------+---------+

Answer 4

Just another option is using the WITH TIES, and then a little cheat for the date

Example

Select top 1 with ties 
       MyDate = convert(varchar(7),MyDate,120)+'-01'
      ,MyValue
 from  YourTable
 Order By Row_Number() over (Partition By convert(varchar(7),MyDate,120) Order By MyDate)

Returns

MyDate      MyValue
2018-01-01  2
2018-02-01  3
2018-03-01  7