Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

If Statement in a View/Query Expand / Collapse
Author
Message
Posted Thursday, July 11, 2013 8:14 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 1:06 PM
Points: 69, Visits: 170
Hello,

I would like to create a SQL query that will make a decision based on date. For example, if the date is Jun 30,2013 or greater, then show 2013, else 2012.

IF myDate>6/30/2013 THEN '2013' ELSE '2012' as getSeason FROM myTable

I know this is not the write syntax, but is it possible to do something this way?
Post #1472597
Posted Thursday, July 11, 2013 8:22 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 2:05 PM
Points: 13,327, Visits: 12,820
rayh 98086 (7/11/2013)
Hello,

I would like to create a SQL query that will make a decision based on date. For example, if the date is Jun 30,2013 or greater, then show 2013, else 2012.

IF myDate>6/30/2013 THEN '2013' ELSE '2012' as getSeason FROM myTable

I know this is not the write syntax, but is it possible to do something this way?


Do you mean the current date? In other words if you run the query before June 30th it will show the previous year data but if you run the query after June 30th it will show the current year data? A little bit more detail is required here to provide an accurate answer.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1472603
Posted Thursday, July 11, 2013 8:22 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:31 AM
Points: 12,962, Visits: 32,501
rayh 98086 (7/11/2013)
Hello,

I would like to create a SQL query that will make a decision based on date. For example, if the date is Jun 30,2013 or greater, then show 2013, else 2012.

IF myDate>6/30/2013 THEN '2013' ELSE '2012' as getSeason FROM myTable

I know this is not the write syntax, but is it possible to do something this way?


the CASE statement is used in sQL to do that; but in your case, you might want to use the YEAR function for this specific issue;

so it would look something like this:
SELECT 
OtherColumns,
CASE
WHEN myDate > '6/30/2013'
THEN '2013'
ELSE '2012'
END as getSeason
FROM myTable

--or

SELECT
OtherColumns,
YEAR(myDate) AS getSeason
FROM myTable



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1472604
Posted Thursday, July 11, 2013 8:36 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 1:06 PM
Points: 69, Visits: 170
Thank you both!

Sean, I should have been more clear, I have a date field that I am calling myDate and want to check against the field (not using current date/GetDate()). I always appreciate you help!

Lowell, this works great!

The next step I would be looking to do is be able to change the then and else values based on the date field year.

If the date field year is 2014 and greater it wouldnt make sense to have the THEN be 2013 and ELSE be 2013

For example:

SELECT
OtherColumns,
CASE
WHEN myDate > '6/30/2015'
THEN '2013'
ELSE '2012'
END as getSeason
FROM myTable

Can I make the THEN ELSE values vary based on the myDate field's year?
Post #1472616
Posted Thursday, July 11, 2013 8:46 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:31 AM
Points: 12,962, Visits: 32,501
ok ray, this looks like a business "fiscal year" question, right?
ie my biz year goes from july1 to jun30 of the next year?


substitue MyDate for GETDATE() in this query, and it will do what you ask for, i think;
see how i'm setting my biz date based on # days from the beginning of the year to calculate it....Feb 2014 would have a biz year of 2013 in my example
--# days since the first of the year:
select
DATEDIFF(DAY,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0),GETDATE()) As Dayz, --ie 191 for 07/11/2013
CASE
WHEN DATEDIFF(DAY,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0),GETDATE()) > 180
THEN YEAR(GETDATE())
ELSE YEAR(GETDATE()) -1
END As BizYear



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1472623
Posted Thursday, July 11, 2013 9:00 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 1:06 PM
Points: 69, Visits: 170
Thanks again!!!!!!!

This forum is awesome!
Post #1472630
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse