SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 IF Within SELECT
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

martes
Starting Member

13 Posts

Posted - 02/18/2011 :  08:00:20  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Germany
8766 Posts

Posted - 02/18/2011 :  08:05:34  Show Profile  Visit webfred's Homepage  Reply with Quote
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 - 02/18/2011 :  08:10:11  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Germany
8766 Posts

Posted - 02/18/2011 :  08:18:36  Show Profile  Visit webfred's Homepage  Reply with Quote
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 - 02/18/2011 :  08:22:45  Show Profile  Reply with Quote
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)

Singapore
17681 Posts

Posted - 02/18/2011 :  09:11:24  Show Profile  Reply with Quote
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
Time is always against us

Go to Top of Page

martes
Starting Member

13 Posts

Posted - 02/18/2011 :  11:06:18  Show Profile  Reply with Quote
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

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];


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?

Edited by - martes on 02/18/2011 11:10:28
Go to Top of Page

yuanyelss
Starting Member

China
4 Posts

Posted - 02/18/2011 :  23:40:03  Show Profile  Visit yuanyelss's Homepage  Reply with Quote
unspammed
Go to Top of Page

chris_n_osborne
Starting Member

USA
34 Posts

Posted - 02/18/2011 :  23:54:43  Show Profile  Reply with Quote
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 - 02/19/2011 :  12:56:06  Show Profile  Reply with Quote
Thanks Chris. I thought I had tried that, but it certainly works now.
Go to Top of Page

chris_n_osborne
Starting Member

USA
34 Posts

Posted - 02/19/2011 :  13:16:36  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000