C# WPF SQL) How to know latest date(where user inputed a record) or biggest value in specific month in SQL database table programmatically?

Question

This is not about last day of specific month.

I'm trying to calculate CAGR ratio (compound annual(month) growth rate). So, at the last inputed date, there's always biggest value in the specific month meaning the value only increase.

However, there's possibility that users don't input everyday(including last day of month). Therefore, I have to know which date is what user inputed most lately in the specific month.

Please refer to attached image.

I've been always happy with excellence of people in stackoverflow ! Thank you so much !

Answer 1

Here is the syntax for SQL Server:

WITH
CTE
AS
(
    SELECT 
        dt
        ,Value
        ,ROW_NUMBER() 
        OVER (PARTITION BY DATEDIFF(month, '2001-01-01', dt) ORDER BY dt desc) AS rn
    FROM YourTable
)
SELECT
    dt
    ,Value
FROM CTE
WHERE rn = 1
;

In general, look up top-n-per-group or greatest-n-per-group.

For a more detailed answer with other variants how to do it see: https://dba.stackexchange.com/questions/86415/retrieving-n-rows-per-group

Answer 2

SELECT top(1) * 
FROM table 
WHERE dt >= 01/02/2016 
    AND dt <= 29/02/2016 
ORDER BY value DESC