Find the MAX(Month) from Month names with the current month has the highest value

Question

IF object_id('tempdb..#A')  IS NOT NULL DROP TABLE #A
CREATE TABLE #A (Computer varchar(20), Name varchar(20), Month varchar(20))

INSERT INTO #A
SELECT 'PC1', 'Bob', 'June'
UNION ALL
SELECT 'PC1', 'Tammy', 'January'
UNION ALL
SELECT 'PC1', 'Wes', 'September'
UNION ALL 
SELECT 'PC1', 'Susan', 'October'
UNION ALL
SELECT 'PC1', 'Kevin', 'February'


SELECT * FROM #A

This produces the results:

  Computer  Name        Month
    PC1     Bob         June
    PC1     Tammy       January
    PC1     Wes         September
    PC1     Susan       October
    PC1     Kevin       February

The month column is the most popular user that has logged in on this computer in that month.

I would like to display the most recently logged in user, but they may not have logged in this month or even last month.

In the above example, if today is in June I would want to select PC1 Kevin.

So really, I would want a function to return a number 1-12 for a specified month relative to the current month.

If today is June 26, then

udf_month('June') would return 12
udf_month('May') would return 11
udf_month('January') would return 7
udf_month('December') would return 6
udf_month('July') would return 1

If today was July 1st, then udf_month('June') would return 11

Then I could do a SELECT Computer, Name, Max(udf_Month(Month)) to find the most recent Name for each Computer.

Correct?

But really, you should just store the integer in your database to begin with instead of the month name. — Matt Johnson-Pint, Jun 27 '13 at 03:23
The data is coming from an Altiris query (software management database) — kevro, Jun 27 '13 at 17:49

Saharsh Shah · Answer 1 · 2013-07-03T05:12:08.273

2

Try this:

CREATE FUNCTION [dbo].[udf_month](@V_Month AS VARCHAR(20)) 
RETURNS INT AS 
BEGIN
    DECLARE @V_MonthNo INT, @V_InputMonthNo INT, @V_CurrentMonthNo INT;
    SELECT @V_InputMonthNo = DATEPART(MM, @V_Month + ' 01 2013'), @V_CurrentMonthNo = MONTH(GETDATE());
    SET @V_MonthNo = 12 - @V_CurrentMonthNo + @V_InputMonthNo;

    IF (@V_MonthNo > 12) 
    BEGIN 
        SET @V_MonthNo = @V_MonthNo - 12;
    END

    RETURN @V_MonthNo;
END

edited Jul 03 '13 at 05:12

answered Jun 27 '13 at 05:42

Saharsh Shah

28,687
8
48
83

This is great. The only problem is that it sets May = 12 and June = 1. To fix it, you just need to remove the " + 1" from the SET @V_MonthNo (line 6). – kevro Jun 27 '13 at 18:48
With this change we get: January 7 February 8 March 9 April 10 May 11 June 12 July 1 August 2 September 3 October 4 November 5 December 6 – kevro Jun 27 '13 at 18:48
That doesn't work for July. – kevro Jul 02 '13 at 16:50
@kevro Check my updated answer. It will work for you and I have tested for all months. Its working – Saharsh Shah Jul 03 '13 at 05:12

sathish · Accepted Answer · 2013-06-28T05:47:57.057

1

TRY THIS:-

ALTER FUNCTION [dbo].[udf_month](@Month AS VARCHAR(20))

RETURNS INT AS

BEGIN

declare @mon as varchar(50)

declare @year as varchar(50)

declare @date as varchar(50)

declare @RESULT as varchar(50)

set @mon= (select DATEPART(MM, @Month + ' 01 2012'))

set @year=(case when @mon<=MONTH(getdate()) then YEAR(getdate())+1 ELSE YEAR(getdate()) END)

set @date=(select @mon+'-01-'+@year)

set @RESULT= ( case when @mon<=MONTH(getdate()) then DATEDIFF(mm,getdate(),@date) ELSE replace(DATEDIFF(mm,@date,getdate()),'-','') END)

RETURN @RESULT ;

END

GO

edited Jun 28 '13 at 05:47

answered Jun 27 '13 at 07:14

sathish

36
3

This produced: January 5 February 4 March 3 April 2 May 1 June 0 July 11 August 10 September 9 October 8 November 7 December 6 – kevro Jun 27 '13 at 18:45
It should show: January 7 February 8 March 9 April 10 May 11 June 12 July 1 August 2 September 3 October 4 November 5 December 6 – kevro Jun 27 '13 at 18:50
try this edited scripts – sathish Jun 28 '13 at 06:23

Find the MAX(Month) from Month names with the current month has the highest value

2 Answers2