June 20, 2014 at 12:46 pm
This is the logic I need to incorporate in to sql
if the getdate() < term start date then R
if the getdate() > term date date and getdate() < term end date then C
if the getdate() > term end date then H
I have come up with the following type of case statement that will allow me to
determine the first two values (to a degree), however , I need to evaluate the end date as well.
Is there a way to look at multiple columns in a case statement?
CASE
WHEN DATEDIFF(DD,GETDATE(),TRM_BEGIN_DTE) >0 THEN 'R' else 'C' end
June 20, 2014 at 12:58 pm
Something like this?
case when getdate() < TermStart then 'R'
when getdate() > TermDate and getdate() < Term then 'C'
when getdate() > TermEnd then 'H'
else 'X'
end
_______________________________________________________________
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/
June 20, 2014 at 1:03 pm
Well it that makes complete sense! For some reason I did not think I could look at two different columns in a case statement. Thanks so much!
June 20, 2014 at 1:43 pm
ngustafson-1056093 (6/20/2014)
Well it that makes complete sense! For some reason I did not think I could look at two different columns in a case statement. Thanks so much!
No problem. You can in fact look at completely different columns and even different datatypes inside of each condition. You just have to make sure that the output of each condition is the same datatype. 😉
case when Column1 = 'asdf' then 'Col1'
when Column2 = 'qwer' then 'Col2'
when MyDateColumn = getdate() then 'Date'
end
_______________________________________________________________
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/
June 20, 2014 at 2:50 pm
Sean Lange (6/20/2014)
ngustafson-1056093 (6/20/2014)
Well it that makes complete sense! For some reason I did not think I could look at two different columns in a case statement. Thanks so much!No problem. You can in fact look at completely different columns and even different datatypes inside of each condition. You just have to make sure that the output of each condition is the same datatype. 😉
case when Column1 = 'asdf' then 'Col1'
when Column2 = 'qwer' then 'Col2'
when MyDateColumn = getdate() then 'Date'
end
CASE - my favorite TSQL construct!!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 20, 2014 at 3:19 pm
TheSQLGuru (6/20/2014)
Sean Lange (6/20/2014)
ngustafson-1056093 (6/20/2014)
Well it that makes complete sense! For some reason I did not think I could look at two different columns in a case statement. Thanks so much!No problem. You can in fact look at completely different columns and even different datatypes inside of each condition. You just have to make sure that the output of each condition is the same datatype. 😉
case when Column1 = 'asdf' then 'Col1'
when Column2 = 'qwer' then 'Col2'
when MyDateColumn = getdate() then 'Date'
end
Of all the languages I have worked with the case construct in TSQL is certainly the most flexible one. 😉
CASE - my favorite TSQL construct!!
_______________________________________________________________
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/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply