SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


If Statement in a View/Query


If Statement in a View/Query

Author
Message
Meatloaf
Meatloaf
SSC-Addicted
SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)

Group: General Forum Members
Points: 486 Visits: 421
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?
Sean Lange
Sean Lange
SSC Guru
SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)

Group: General Forum Members
Points: 65360 Visits: 17980
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 Modens 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)
Lowell
Lowell
SSC Guru
SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)

Group: General Forum Members
Points: 75671 Visits: 40990
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
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Meatloaf
Meatloaf
SSC-Addicted
SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)

Group: General Forum Members
Points: 486 Visits: 421
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?
Lowell
Lowell
SSC Guru
SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)

Group: General Forum Members
Points: 75671 Visits: 40990
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
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Meatloaf
Meatloaf
SSC-Addicted
SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)

Group: General Forum Members
Points: 486 Visits: 421
Thanks again!!!!!!!:-)

This forum is awesome!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search