How to get the maximum month and year

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to get the maximum month and year

Author

Topic

mlawton
Starting Member

35 Posts

Posted - 2012-03-07 : 10:39:00

I have a table that has the following:
Order No Year Quarter MonthID Month Status
154545 2011 1 1 January 1
105200 2011 1 2 February 1
166334 2011 1 3 March 1
225444 2011 2 4 April 1
885451 2011 2 5 May 1
477444 2011 2 6 June 1
111554 2011 3 7 July 1
588775 2011 3 8 August 1
877777 2011 3 9 September 1
125666 2011 4 10 October 1
877777 2011 4 11 November 0
125666 2011 4 12 December 0
154545 2012 1 1 January 0
105200 2012 1 2 February 0
166334 2011 1 3 March 0
225444 2011 2 4 April 0
885451 2011 2 5 May 0
477444 2011 2 6 June 0
111554 2011 3 7 July 0
588775 2011 3 8 August 0
877777 2011 3 9 September 0
125666 2011 4 10 October 0

How do I get the maximum month and year when status is = 1?

Here is my query:
select max(month) as LatestMonth, max(year) as LatestYear
from dbo.Orders
where status = 1

This query returns September 2011.
It should return October 2011. How do I get it to return October 2011?

Thanks for your help.

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-07 : 10:43:39

'S' is Larger than 'O'

You need to use MonthId and I hope it's numeric

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

mlawton
Starting Member

35 Posts

Posted - 2012-03-07 : 11:23:51

Thanks. This works. But how do I get the month name - October?

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-07 : 11:50:28

Actually, doesn't max year = 2012 and max month 12???

So you really are getting data from 2 different rows?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

mlawton
Starting Member

35 Posts

Posted - 2012-03-07 : 11:58:55

No, because status is 0 for 2012. I only want max month and year when status is 1.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-03-07 : 12:06:51

[code]DECLARE @T TABLE (Order_No INT, [Year] INT, [Quarter] INT, MonthID INT, [Month] VARCHAR(20), Status BIT)

INSERT @T VALUES
(154545, 2011, 1, 1, 'January', 1),
(105200, 2011, 1, 2, 'February', 1),
(166334, 2011, 1, 3, 'March', 1),
(225444, 2011, 2, 4, 'April', 1),
(885451, 2011, 2, 5, 'May', 1),
(477444, 2011, 2, 6, 'June', 1),
(111554, 2011, 3, 7, 'July', 1),
(588775, 2011, 3, 8, 'August', 1),
(877777, 2011, 3, 9, 'September', 1),
(125666, 2011, 4, 10, 'October', 1),
(877777, 2011, 4, 11, 'November', 0),
(125666, 2011, 4, 12, 'December', 0),
(154545, 2012, 1, 1, 'January', 0),
(105200, 2012, 1, 2, 'February', 0),
(166334, 2011, 1, 3, 'March', 0),
(225444, 2011, 2, 4, 'April', 0),
(885451, 2011, 2, 5, 'May', 0),
(477444, 2011, 2, 6, 'June', 0),
(111554, 2011, 3, 7, 'July', 0),
(588775, 2011, 3, 8, 'August', 0),
(877777, 2011, 3, 9, 'September', 0),
(125666, 2011, 4, 10, 'October', 0)

SELECT
[Month],
[Year]
FROM
@T AS T
INNER JOIN
(
SELECT MAX(DATEADD(MONTH, (([Year] -1900)*12) + MonthID, 0)) AS MaxYearMonth
FROM @T
WHERE Status = 1
) AS A
ON DATEADD(MONTH, ((T.[Year] -1900)*12) + T.MonthID, 0) = A.MaxYearMonth
WHERE
Status = 1

-- Or more simply:

SELECT TOP 1
[Month],
[Year]
FROM
@T AS T
WHERE
Status = 1
ORDER BY
(T.[Year] * 100 + T.MonthID) DESC[/code]EDIT posted wrong query

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-07 : 13:17:14

quote:
Originally posted by mlawton

No, because status is 0 for 2012. I only want max month and year when status is 1.

Status has nothing to do with it

Lampy's got it right...

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

mlawton
Starting Member

35 Posts

Posted - 2012-03-07 : 14:44:53

Thanks guys!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources