Get max of each month of the last 3 years to select the record month

Question

We are using MS SQL SERVER 2012.

We are recording like everytime a user makes a phone call, by adding a Datetime and Userid into a table for each call the user makes.

Now I need to calculate the top monthly, weekly, daily record from the users off all time since creation.

Meaning, I need to calculate the highest monthly count any user ever had for a given month since creation. So the record monthly count, and the name with it.

I'm not really sure how to best go for that.This is what I created for now,and I was wondering if there is a better or more efficient way to do that.

Here is the updated version with sample. (I applied the date change, I hope this helps)

This is for the monthly record:

DECLARE @id int, @mr int
DECLARE @first datetime, @last datetime
DECLARE @name nvarchar(60)

DECLARE @UserTable TABLE(
    ID int,
    Name nvarchar(60)
)

INSERT INTO @UserTable (ID, Name) VALUES (1, 'Tester');
INSERT INTO @UserTable (ID, Name) VALUES (2, 'Dummy');
INSERT INTO @UserTable (ID, Name) VALUES (3, 'User');


DECLARE @SampleTable TABLE(
    A_Termin_AE int,
    A_Termin_Status int,
    A_Termin_Set_Date datetime  
)

INSERT INTO @SampleTable (A_Termin_AE, A_Termin_Status, A_Termin_Set_Date) VALUES (1, 603, CAST('20090525' AS Datetime)); 
INSERT INTO @SampleTable (A_Termin_AE, A_Termin_Status, A_Termin_Set_Date) VALUES (1, 601, Cast('20090512' AS Datetime));
INSERT INTO @SampleTable (A_Termin_AE, A_Termin_Status, A_Termin_Set_Date) VALUES (1, 603, Cast('20090525' AS Datetime));
INSERT INTO @SampleTable (A_Termin_AE, A_Termin_Status, A_Termin_Set_Date) VALUES (1, 603, Cast('20100803' AS Datetime));
INSERT INTO @SampleTable (A_Termin_AE, A_Termin_Status, A_Termin_Set_Date) VALUES (1, 603, Cast('20100805' AS Datetime));
INSERT INTO @SampleTable (A_Termin_AE, A_Termin_Status, A_Termin_Set_Date) VALUES (1, 603, Cast('20100817' AS Datetime));
INSERT INTO @SampleTable (A_Termin_AE, A_Termin_Status, A_Termin_Set_Date) VALUES (1, 601, Cast('20110521' AS Datetime));


INSERT INTO @SampleTable (A_Termin_AE, A_Termin_Status, A_Termin_Set_Date) VALUES (2, 601, Cast('20090501' AS Datetime));
INSERT INTO @SampleTable (A_Termin_AE, A_Termin_Status, A_Termin_Set_Date) VALUES (2, 603, Cast('20090502' AS Datetime));
INSERT INTO @SampleTable (A_Termin_AE, A_Termin_Status, A_Termin_Set_Date) VALUES (2, 603, Cast('20100815' AS Datetime));
INSERT INTO @SampleTable (A_Termin_AE, A_Termin_Status, A_Termin_Set_Date) VALUES (2, 603, Cast('20110501' AS Datetime));
INSERT INTO @SampleTable (A_Termin_AE, A_Termin_Status, A_Termin_Set_Date) VALUES (2, 603, Cast('20110505' AS Datetime));
INSERT INTO @SampleTable (A_Termin_AE, A_Termin_Status, A_Termin_Set_Date) VALUES (2, 603, Cast('20110508' AS Datetime));
INSERT INTO @SampleTable (A_Termin_AE, A_Termin_Status, A_Termin_Set_Date) VALUES (2, 603, Cast('20110510' AS Datetime));
INSERT INTO @SampleTable (A_Termin_AE, A_Termin_Status, A_Termin_Set_Date) VALUES (2, 603, Cast('20110514' AS Datetime));


INSERT INTO @SampleTable (A_Termin_AE, A_Termin_Status, A_Termin_Set_Date) VALUES (3, 603, Cast('20120503' AS Datetime));
INSERT INTO @SampleTable (A_Termin_AE, A_Termin_Status, A_Termin_Set_Date) VALUES (3, 603, Cast('20120508' AS Datetime));
INSERT INTO @SampleTable (A_Termin_AE, A_Termin_Status, A_Termin_Set_Date) VALUES (3, 603, Cast('20120510' AS Datetime));
INSERT INTO @SampleTable (A_Termin_AE, A_Termin_Status, A_Termin_Set_Date) VALUES (3, 603, Cast('20120514' AS Datetime));
INSERT INTO @SampleTable (A_Termin_AE, A_Termin_Status, A_Termin_Set_Date) VALUES (3, 603, Cast('20120520' AS Datetime));
INSERT INTO @SampleTable (A_Termin_AE, A_Termin_Status, A_Termin_Set_Date) VALUES (3, 601, Cast('20130701' AS Datetime));
INSERT INTO @SampleTable (A_Termin_AE, A_Termin_Status, A_Termin_Set_Date) VALUES (3, 603, Cast('20130703' AS Datetime));

DECLARE @TempTable TABLE(
    ID int,
    UserID int,
    Name nvarchar(60),
    Record int,
    Datum datetime
)

INSERT INTO @TempTable (ID, UserID, Name, Record, Datum) VALUES (1, 0, '', 0, GETDATE());

SET @first = CAST('20080101' AS Datetime);

WHILE @first < GETDATE() 
BEGIN

    SET @first = dateadd(month, 1, @first)
    SET @last = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@first)+1,0));

    DECLARE User_Cur CURSOR
        FOR SELECT tu.Id, tu.Name FROM @UserTable tu
        OPEN User_Cur;

            FETCH NEXT FROM User_Cur INTO @id, @name;

            WHILE @@FETCH_STATUS = 0
            BEGIN    
                SET @mr = (SELECT COUNT(t.A_Termin_Set_Date) 
                             FROM @SampleTable t 
                            WHERE t.A_Termin_AE = @id 
                              AND t.A_Termin_Status = 603 
                              AND t.A_Termin_Set_Date BETWEEN @first AND @last);

                IF (SELECT Record FROM @TempTable WHERE ID = 1) < @mr 
                BEGIN
                    UPDATE @TempTable 
                       SET UserID = @id, 
                           Name = @name, 
                           Record = @mr, 
                           Datum = @first 
                     WHERE ID = 1;
                END

            FETCH NEXT FROM User_Cur INTO @id, @name;
            END

        CLOSE User_Cur;
    DEALLOCATE User_Cur;

END

SELECT * FROM @TempTable;

So the user with the most calls made in a month is: 'Dummy' with 5 Records in May 2011;

I appreciate any help and suggestions on this.

To get correct attention, tag the dbms you're using. (That code is product specific.) — jarlh, Oct 23 '17 at 08:50
Have a look on windowing functions (the 'over' clause). Also showing a short dummy example of raw date and expected result might help to express your question in more concise way — Jan, Oct 23 '17 at 09:44
Please **[EDIT]** your question and add some [sample data](http://plaintexttools.github.io/plain-text-table/) and the expected output based on that data. [**Formatted text**](http://stackoverflow.com/help/formatting) please, [**no screen shots**](http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557#285557). **[edit]** your question - do **not** post code or additional information in comments. — , Oct 23 '17 at 09:51
This code fails - did you actually try to run the queries? e.g. `INSERT INTO @SampleTable (A_Termin_AE, A_Termin_Status, A_Termin_Set_Date) VALUES (1, 603, '25.05.2009');` will fail due to the fact you are trying to convert string into date time and you don't have correct formatting. You would have to do something like this: `INSERT INTO @SampleTable (A_Termin_AE, A_Termin_Status, A_Termin_Set_Date) VALUES (1, 603, CAST('20090525' as datetime));` The formatting of your code makes it really **hard** to read - I have tried to fix it but you re-edited without the fix, please don't do that. — tukan, Oct 23 '17 at 10:44
It's german time format, that's probably why it fails for you. And yes it runs for me, otherwise I wouldn't have posted it. — Developer, Oct 23 '17 at 11:21
I know where the issue is - that is European formatting (dd.mm.yyyy). The point is that you should make the queries as general as possible. You probably want to get help from someone who does not use such formatting. The best is use some standard (for MSSQL it is yyyymmdd), but there is of course the ISO 8601 (https://technet.microsoft.com/en-us/library/ms190977(v=sql.90).aspx) format. — tukan, Oct 23 '17 at 11:30
Do you have a dates table you can join on which will allow you access to Window function using WeekNumber, MonthNumber? — QHarr, Oct 23 '17 at 11:31

score 1 · Answer 1 · answered Oct 24 '17 at 01:53

The sample data contains 2 users in the different months with the same maximum count. So why would you only list 1? How would you know there is more than 1, it could be 4 or 2 or 37.

SELECT
         Name
       , Mnth
       , Record
       , dense_rank() over(ORDER BY record DESC) RNK
FROM (
        SELECT
                 Name
               , Mnth
               , Record
               , dense_rank() over(ORDER BY record DESC) RNK
        FROM (
                SELECT
                         tu.Name
                       , DATEADD(MONTH, DATEDIFF(MONTH, 0, t.A_Termin_Set_Date), 0) AS Mnth
                       , COUNT(t.A_Termin_AE) AS Record
                FROM @SampleTable t
                JOIN @UserTable tu ON tu.Id = t.A_Termin_AE
                WHERE t.A_Termin_Status = 603
                GROUP BY tu.Name, DATEADD(MONTH, DATEDIFF(MONTH, 0, t.A_Termin_Set_Date), 0)
         ) d1
      ) d2
WHERE rnk = 1
ORDER BY Mnth DESC, Name

Result:

  Name           Mnth           Record   RNK  
 ------- --------------------- -------- ----- 
  User    01.05.2012 00:00:00        5     1  
  Dummy   01.05.2011 00:00:00        5     1

refer: http://rextester.com/DORH41455

score 0 · Answer 2 · answered Oct 23 '17 at 12:37

OK, I just had a chat with a friend, and he explained me a very simple, fast and easy way for this. In case anyone comes accross the same problem, here is the answer:

SELECT TOP(1) COUNT(t.A_Termin_AE) AS Record
       , tu.Name
       ,  DATEADD(MONTH, DATEDIFF(MONTH, 0, t.A_Termin_Set_Date), 0) AS Datum
    FROM @SampleTable t
        JOIN @UserTable tu ON tu.Id = t.A_Termin_AE
            WHERE t.A_Termin_Status = 603
                GROUP BY tu.Name, DATEADD(MONTH, DATEDIFF(MONTH, 0, t.A_Termin_Set_Date), 0)
                    ORDER BY Record DESC;

Thanks again for all the help!!!

What happens if there are several months with the same high value? — Paul Maxwell, Oct 23 '17 at 14:30

score 0 · Answer 3 · answered Oct 24 '17 at 10:21

I have to agree with @Used_By_Already.

Without nesting SQL statements I have done something similar with multiple-CTE approach:

...

;WITH counting_records AS (
           SELECT  tu.name,
                   DATEADD(MONTH, DATEDIFF(MONTH, 0, st.A_Termin_Set_Date), 0) AS month,
                   COUNT(st.A_Termin_AE) AS record
                FROM @SampleTable st
                INNER JOIN @UserTable tu ON tu.Id = st.A_Termin_AE
                WHERE st.A_Termin_Status = 603
                GROUP BY tu.Name, DATEADD(MONTH, DATEDIFF(MONTH, 0, st.A_Termin_Set_Date), 0)
),  get_rank (name, month, record, rank) AS (
          SELECT cr.name,
                 cr.month,
                 cr.record, 
                 DENSE_RANK() OVER( ORDER BY cr.record DESC) AS rank
            FROM counting_records AS cr
           )

-- getting the highest count (lowest rank)
   SELECT get_rank.name,
          get_rank.month,
          get_rank.record,
          get_rank.rank
     FROM get_rank
    WHERE get_rank.rank = 1
    ORDER BY get_rank.month

Having result:

name    month                   record  rank
Dummy   2011-05-01 00:00:00.000 5       1
User    2012-05-01 00:00:00.000 5       1

I think it would be also nice to explain DENSE_RANK() OVER(ORDER BY cr.record DESC) AS rank.

DENSE_RANK() ... Returns the rank of rows within the partition of a result set, without any gaps in the ranking.

Syntax:  `DENSE_RANK ( ) OVER ( [ <partition_by_clause> ] < order_by_clause > )`

Get max of each month of the last 3 years to select the record month

3 Answers3