January 12, 2017 at 6:16 am
Hi all,
Looking to get some help with the following that I just can't get the case statement right with.....
CONVERT(VARCHAR(10), z.Date, 105) as VisitDate,
case
when v.acc_Visit_Frequency = '1' then CONVERT(VARCHAR(10), dateadd(m,12,z.date), 105)
when v.acc_Visit_Frequency = '2' then CONVERT(VARCHAR(10), dateadd(m,6,z.date), 105)
when v.acc_Visit_Frequency = '3' then CONVERT(VARCHAR(10), dateadd(m,4,z.date), 105)
when v.acc_Visit_Frequency = '4' then CONVERT(VARCHAR(10), dateadd(m,3,z.date), 105)
else null
end as DateDue,
I think it may be due to using the same field 'z.date' twice but appreciate any help.
Thanks
January 12, 2017 at 6:23 am
What's the problem that you're getting? What are you seeing? What are you expecting / hoping to see?
Thomas Rushton
blog: https://thelonedba.wordpress.com
January 12, 2017 at 6:24 am
Error?
------------------------------------------------------------------------------------------------Standing in the gap between Consultant and ContractorKevin3NFDallasDBAs.com/BlogWhy is my SQL Log File HUGE?!?![/url]The future of the DBA role...[/url]SQL Security Model in Plain English[/url]
January 12, 2017 at 6:24 am
rob.king - Thursday, January 12, 2017 6:16 AMHi all,Looking to get some help with the following that I just can't get the case statement right with.....
CONVERT(VARCHAR(10), z.Date, 105) as VisitDate,
case
when v.acc_Visit_Frequency = '1' then CONVERT(VARCHAR(10), dateadd(m,12,z.date), 105)
when v.acc_Visit_Frequency = '2' then CONVERT(VARCHAR(10), dateadd(m,6,z.date), 105)
when v.acc_Visit_Frequency = '3' then CONVERT(VARCHAR(10), dateadd(m,4,z.date), 105)
when v.acc_Visit_Frequency = '4' then CONVERT(VARCHAR(10), dateadd(m,3,z.date), 105)
else null
end as DateDue,I think it may be due to using the same field 'z.date' twice but appreciate any help.
Thanks
Looks fine. What's it not doing that it should be doing?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 12, 2017 at 6:26 am
Sorry - getting invalid column name 'date' on the four case lines
January 12, 2017 at 6:27 am
rob.king - Thursday, January 12, 2017 6:26 AMSorry - getting invalid column name 'date' on the four case lines
Case-sensitive database? Try changing 'date' to 'Date'.
Thomas Rushton
blog: https://thelonedba.wordpress.com
January 12, 2017 at 6:29 am
What are the names of the columns in the table aliased as 'z'? Can you post the rest of the query and the CREATE TABLE statement?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 12, 2017 at 6:30 am
You're a star - didn't spot that!!
Thank you v.much
January 12, 2017 at 6:36 am
ThomasRushton - Thursday, January 12, 2017 6:27 AMrob.king - Thursday, January 12, 2017 6:26 AMSorry - getting invalid column name 'date' on the four case linesCase-sensitive database? Try changing 'date' to 'Date'.
nice catch 🙂
------------------------------------------------------------------------------------------------Standing in the gap between Consultant and ContractorKevin3NFDallasDBAs.com/BlogWhy is my SQL Log File HUGE?!?![/url]The future of the DBA role...[/url]SQL Security Model in Plain English[/url]
January 12, 2017 at 6:41 am
Kevin3NF - Thursday, January 12, 2017 6:36 AMThomasRushton - Thursday, January 12, 2017 6:27 AMrob.king - Thursday, January 12, 2017 6:26 AMSorry - getting invalid column name 'date' on the four case linesCase-sensitive database? Try changing 'date' to 'Date'.
nice catch 🙂
Thanks. Been bitten by that a few times in the past... :/
Thomas Rushton
blog: https://thelonedba.wordpress.com
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply