Biggest YEAR() and biggest MONTH()

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)

Biggest YEAR() and biggest MONTH()

Author

Topic

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-01-16 : 11:03:18

Greetings

I have a table as such..how do I do a select in order to get the row with the highest EffectiveMonth and EffectiveYear
CREATE TABLE [dbo].[syBranchDefinitions](
[BranchDefinitionID] [int] IDENTITY(1,1) NOT NULL,
[DefinitionID] [int] NOT NULL,
[BranchID] [int] NOT NULL,
[DefinitionValue] [int] NULL,
[EffectiveYear] [int] NULL,
[EffectiveMonth] [int] NULL,
[UserDate] [datetime] NOT NULL CONSTRAINT [DF_syBranchDefinitions_UserDate] DEFAULT (getdate()),
[UserName] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]

Merci!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-16 : 11:07:29

[code]
SELECT *
FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY EffectiveMonth) AS MonthSeq,ROW_NUMBER() OVER (ORDER BY EffectiveYear) AS YearSeq,*
FROM Table
)t
WHERE t.MonthSeq=1
OR t.YearSeq=1
[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-16 : 11:11:20

SELECT TOP 1 *
FROM syBranchDefinitions
ORDER BY EffectiveYear DESC, EffectiveMonth DESC

E 12°55'05.63"
N 56°04'39.26"

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-01-16 : 11:13:36

Thanks v ! you are a bad boy but this is the result I get from that query.

MonthSeq YearSeq EffectiveYear EffectiveMonth
---------- -------- --------------
5 1 2003 1
1 3 2009 1

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-01-16 : 11:15:35

peso you are the money man!
THanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-16 : 11:16:24

quote:
Originally posted by yosiasz

Thanks v ! you are a bad boy but this is the result I get from that query.

MonthSeq YearSeq EffectiveYear EffectiveMonth
---------- -------- --------------
5 1 2003 1
1 3 2009 1

or do you need this?


SELECT *
FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY EffectiveYear DESC,EffectiveMonth DESC) AS Seq,*
FROM Table
)t
WHERE t.Seq=1

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-01-16 : 12:18:22

ok my table design was not good so I went with EffectiveDate a datetime column and got rid of effectmonth = int and effectiveyear int

now how do i check to see that the max effective date is less then today

is it ok to do this? because of time stamp issues I just wanted to do
CONVERT(CHAR(10), GETDATE() , 101) > CONVERT(CHAR(10), @EffectiveDate , 101)

or can I just do
IF GETDATE() > @EffectiveDate

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-16 : 12:21:41

quote:
Originally posted by yosiasz

ok my table design was not good so I went with EffectiveDate a datetime column and got rid of effectmonth = int and effectiveyear int

now how do i check to see that the max effective date is less then today

is it ok to do this? because of time stamp issues I just wanted to do
CONVERT(CHAR(10), GETDATE() , 101) > CONVERT(CHAR(10), @EffectiveDate , 101)

or can I just do
IF GETDATE() > @EffectiveDate

Thanks

just do
EffectiveDate<DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources