0

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.

enter image description here

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

Kay Lee
  • 922
  • 1
  • 12
  • 40
  • 1
    How are you accessing the database? What are you using with your other queries (LINQ? SQL?)? – Tyress Feb 15 '16 at 02:35
  • @Tyress, Many thanks for your comment. I'm using SQL only as I'm really beginner for programming.. – Kay Lee Feb 15 '16 at 02:42
  • My solution thanks to answered people is, 'SELECT Max(my data column) FROM my table WHERE MONTH(my date column) = MONTH(DATEADD(MONTH, -1, getdate()))' -1 is for previous(last) month. Many thanks to people who shared excellent answers. – Kay Lee Feb 15 '16 at 05:19

2 Answers2

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

Community
  • 1
  • 1
Vladimir Baranov
  • 31,799
  • 5
  • 53
  • 90
  • So many thanks for your excellence. However, many terms are very difficult for me and I need to do this programmatically..Would you please kindly show me the way..? – Kay Lee Feb 15 '16 at 02:41
  • What do you mean by "programmatically"? I've shown an SQL query that would return one row per month with the date and value that correspond to the latest entry per month. – Vladimir Baranov Feb 15 '16 at 02:43
  • Hmm..Is this only one sql query command? I think bit difficult for beginner like me. I can understand only 50~60 % but not 'With From CTE, rn and so on..I meant 'programmatically' by C# code behind. Thanksfully, I'll look into grestest-n-per-group as your kind guide. – Kay Lee Feb 15 '16 at 02:50
  • Yes, this is one SQL query. You need to lookup `ROW_NUMBER` function and `Common Table Expression` for MS SQL Server to understand what it does. If you are using MS SQL Server in the first place. If you want to load the whole table into your C# application and then search for the necessary data using C# code, I'm afraid I can't help with that. – Vladimir Baranov Feb 15 '16 at 02:56
  • @KayLee wrap this logic inside a stored procedure and call this from your `C#` procedure, you can find lot of examples by googling with these keywords. – Hari Prasad Feb 15 '16 at 03:10
  • @Vladimir, You seem SQL expert and I highly appreciate you to let me step ahead to more mature field and your precious time. Many thanks again and just for your little reference, I've solved this through 'SELECT Max(my data column) FROM my table WHERE MONTH(my date column) = MONTH(DATEADD(MONTH, -1, getdate()))' -1 is for previous(last) month. – Kay Lee Feb 15 '16 at 05:25
  • You are welcome. If you are interested in one month only (which was not obvious to me from the question), then your variant is better. – Vladimir Baranov Feb 15 '16 at 05:33
1
SELECT top(1) * 
FROM table 
WHERE dt >= 01/02/2016 
    AND dt <= 29/02/2016 
ORDER BY value DESC
t j
  • 7,026
  • 12
  • 46
  • 66
Delgerbayar
  • 31
  • 5
  • I highly appreciate that you also provided excellent inspiration to me. Many thanks again and just for your little reference, I've solved this through 'SELECT Max(my data column) FROM my table WHERE MONTH(my date column) = MONTH(DATEADD(MONTH, -1, getdate()))' -1 is for previous(last) month. – Kay Lee Feb 15 '16 at 05:27