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.
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 likeIF 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. |
|
|
martes
Starting Member
13 Posts |
Posted - 2011-02-18 : 08:10:11
|
I just tested it on AdventureWorksSELECT TOP 1000IF DATEADD(Month, 1, FullDateAlternateKey) <= GETDATE() PRINT 'Y'ELSE PRINT 'N'FROM [AdventureWorksDB].[dbo].[DimTime] It throws a syntax error on the IF |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2011-02-18 : 08:18:36
|
SELECT TOP 1000CASE WHEN DATEADD(Month, 1, FullDateAlternateKey) <= GETDATE() THEN 'Y' ELSE 'N'END as ColumnNameFROM [AdventureWorksDB].[dbo].[DimTime] No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
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. |
|
|
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] |
|
|
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 Year1-2 Years2-3 Years3-4 Years>4 Yearsand Band 2 is say<6 months6-9 months9-12 months1-5 years>5 yearsAn 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? |
|
|
yuanyelss
Starting Member
4 Posts |
Posted - 2011-02-18 : 23:40:03
|
unspammed |
|
|
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] |
|
|
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. |
|
|
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. |
|
|
|
|
|
|
|