| Author |
 Topic  |
|
|
martes
Starting Member
11 Posts |
 Posted - 02/18/2011 : 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
Flowing Fount of Yak Knowledge
Germany
8513 Posts |
Posted - 02/18/2011 : 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. |
 |
|
|
martes
Starting Member
11 Posts |
Posted - 02/18/2011 : 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 |
|
|
|
webfred
Flowing Fount of Yak Knowledge
Germany
8513 Posts |
Posted - 02/18/2011 : 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. |
|
|
|
martes
Starting Member
11 Posts |
Posted - 02/18/2011 : 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. |
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16746 Posts |
Posted - 02/18/2011 : 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
Time is always against us
|
|
|
|
martes
Starting Member
11 Posts |
Posted - 02/18/2011 : 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
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 |
|
|
|
yuanyelss
Starting Member
China
4 Posts |
Posted - 02/18/2011 : 23:40:03
|
| unspammed |
|
|
|
chris_n_osborne
Starting Member
USA
34 Posts |
Posted - 02/18/2011 : 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]
|
|
|
|
martes
Starting Member
11 Posts |
Posted - 02/19/2011 : 12:56:06
|
| Thanks Chris. I thought I had tried that, but it certainly works now. |
|
|
|
chris_n_osborne
Starting Member
USA
34 Posts |
Posted - 02/19/2011 : 13:16:36
|
quote:
Originally posted by martes
Thanks Chris. I thought I had tried that, but it certainly works now.
You're welcome. |
|
|
| |
Topic |
|
|
|
IF Within SELECT
