Select Statement Help - SQL Server Forums

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)

Select Statement Help

Author

Topic

OKsoccer24
Starting Member

8 Posts

Posted - 2009-06-05 : 15:52:56

I have table and one field in it is called year month with possible values of 200905, 200904, 200903, 200812. These are dates but are acutally string fields. The first four characters are the year and the last two are the month. What I want is to find the max year_month then subtract 101 from that max. This will give the latest 12 months in the table. How can I accomplish this?? I know I will need to do multiple select statements, but not good at writing them.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-06-05 : 16:24:33

I'm not sure what you mean by: "This will give the latest 12 months in the table."

Here is way to do the conversion to get the Year_Month 13 months prior:

DECLARE @Yak TABLE (YearMonth VARCHAR(6))

INSERT @Yak
SELECT '200905'
UNION ALL SELECT '200901'
UNION ALL SELECT '200904'
UNION ALL SELECT '200903'
UNION ALL SELECT '200812'
UNION ALL SELECT '201001' -- Max for testing Jan

SELECT
	CONVERT(VARCHAR(6), DATEADD(MONTH, - 13, CAST(MAX(YearMonth) + '01' AS DATETIME)), 112) AS NewDate
FROM
	@Yak

OKsoccer24
Starting Member

8 Posts

Posted - 2009-06-05 : 16:34:34

select *
from events
where
year_month >= (select (max(cast(year_month as nvarchar))-101) from events)

This is what I wanted, I just figured it out. Thanks though

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-06-05 : 16:44:08

quote:
Originally posted by OKsoccer24

select *
from events
where
year_month >= (select (max(cast(year_month as nvarchar))-101) from events)

This is what I wanted, I just figured it out. Thanks though

2 things:
1. This will propbably not give you the results you want if the Max year_month is january of some year (like 201001) in my sample data.
2. Implicit conversions are bad form IMHO. But, if it works.. :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-07 : 03:38:23

quote:
Originally posted by OKsoccer24

select *
from events
where
year_month >= (select (max(cast(year_month as nvarchar))-101) from events)

This is what I wanted, I just figured it out. Thanks though

can i ask what is the need of converting this to varchar before subtraction?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources