If Statement in a View/Query

  • 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?

  • 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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • 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!

  • 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?

  • 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!

  • Thanks again!!!!!!!:-)

    This forum is awesome!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply