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 2008 Forums
 Transact-SQL (2008)
 IF Within SELECT

Author  Topic 

martes
Starting Member

13 Posts

Posted - 2011-02-18 : 08:00:20
I am trying to do a simple query where I can flag if an item is within a given period, using DateAdd.

I have tried code like


IF DATEADD(Month, 1, Date_Field) <= GETDATE() PRINT 'Y'
ELSE PRINT 'N'


but this is not working. Ideally I would like to give the returned column my own label.

I don't want to use a WHERE clause as I want a number of these values (something could be in a period of 1-3 months AND also in a period of 2-6 months say).

Any help appreciated.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-02-18 : 08:05:34
Show a complete statement please and be more specific.
"this is not working" isn't very helpful for us.


No, you're never too old to Yak'n'Roll if you're too young to die.
Go to Top of Page

martes
Starting Member

13 Posts

Posted - 2011-02-18 : 08:10:11
I just tested it on AdventureWorks


SELECT TOP 1000
IF DATEADD(Month, 1, FullDateAlternateKey) <= GETDATE() PRINT 'Y'
ELSE PRINT 'N'
FROM [AdventureWorksDB].[dbo].[DimTime]


It throws a syntax error on the IF
Go to Top of Page

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-02-18 : 08:18:36
SELECT TOP 1000
CASE
WHEN DATEADD(Month, 1, FullDateAlternateKey) <= GETDATE() THEN 'Y'
ELSE 'N'
END as ColumnName
FROM [AdventureWorksDB].[dbo].[DimTime]





No, you're never too old to Yak'n'Roll if you're too young to die.
Go to Top of Page

martes
Starting Member

13 Posts

Posted - 2011-02-18 : 08:22:45
Okay that works, but are you saying that IF won't work/doesn't work?

For what I want, I am going to have a dozen or so single case CASE statements, which seems rather odd to me.
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-02-18 : 09:11:24
you can't use IF in a query. You have to use CASE WHEN . .

For what I want, I am going to have a dozen or so single case CASE statements, which seems rather odd to me.
It is perfectly normal to have a dozen case statement in a query.


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

martes
Starting Member

13 Posts

Posted - 2011-02-18 : 11:06:18
I agree, 12 cases in a CASE statemnet, but 12 single case CASE statements?

Can we take this a step further then?

What I have is a situation whereby I need 3 different sets of maturity bands in my query, and I would like to know where my data falls in each of those sets.

For instance, I have Band 1 as say
< 1 Year
1-2 Years
2-3 Years
3-4 Years
>4 Years

and Band 2 is say
<6 months
6-9 months
9-12 months
1-5 years
>5 years

An item could fall meet the 2-3 year category in Band 1 and also 1-5 years category in Band 2. Rather than have a separate case for each band I thought I could identify its category within each band using Case.

So I tried this

[CODE]SELECT
CASE FullDateAlternateKey
WHEN < DATEADD(Year, 1, GETDATE()) THEN [< 1 Year]
WHEN (>= DATEADD(Year, 1, GETDATE()) AND < DATEADD(Year, 2, GETDATE()) THEN [1-2 Years]
WHEN (>= DATEADD(Year, 2, GETDATE()) AND < DATEADD(Year, 3, GETDATE()) THEN [2-3 Years]
WHEN (>= DATEADD(Year, 3, GETDATE()) AND < DATEADD(Year, 4, GETDATE()) THEN [3-4 Years]
ELSE [>4 Years]
END AS [Maturity Band 1]
,CASE FullDateAlternateKey
WHEN < DATEADD(Month, 6, GETDATE()) THEN [< 6 M0nths]
WHEN (>= DATEADD(Month, 6, GETDATE()) AND < DATEADD(Month, 9, GETDATE()) THEN [6-9 Months]
WHEN (>= DATEADD(Month, 9, GETDATE()) AND < DATEADD(Month, 12, GETDATE()) THEN [9-12 Months]
WHEN (>= DATEADD(Year, 1 GETDATE()) AND < DATEADD(Year, 5, GETDATE()) THEN [1-5 Years]
ELSE [>5 Years]
END AS [Maturity Band 2]
FROM [AdventureWorksDW].[dbo].[DimTime];[/CODE]

but this was not liked, giving me a syntax error on the first <.

Any ideas how I could accomplish this in a neat, tidy, and elegant way?
Go to Top of Page

yuanyelss
Starting Member

4 Posts

Posted - 2011-02-18 : 23:40:03
unspammed
Go to Top of Page

chris_n_osborne
Starting Member

34 Posts

Posted - 2011-02-18 : 23:54:43
quote:
Originally posted by martes


SELECT  
CASE FullDateAlternateKey
WHEN < DATEADD(Year, 1, GETDATE()) THEN [< 1 Year]
WHEN (>= DATEADD(Year, 2, GETDATE()) AND < DATEADD(Year, 3, GETDATE()) THEN [2-3 Years]



Try:

SELECT
CASE
WHEN FullDateAlternateKey < DATEADD(Year, 1, GETDATE()) THEN [< 1 Year]
WHEN (FullDateAlternateKey >= DATEADD(Year, 2, GETDATE()) AND < DATEADD(Year, 3, GETDATE()) THEN [2-3 Years]
Go to Top of Page

martes
Starting Member

13 Posts

Posted - 2011-02-19 : 12:56:06
Thanks Chris. I thought I had tried that, but it certainly works now.
Go to Top of Page

chris_n_osborne
Starting Member

34 Posts

Posted - 2011-02-19 : 13:16:36
quote:
Originally posted by martes

Thanks Chris. I thought I had tried that, but it certainly works now.

You're welcome.
Go to Top of Page
   

- Advertisement -