Help with SQL Case Statement

[richspitz]

Jim Durkin hasn't been able to figure this out, so I thought I would ask you geniuses for some assistance. I am trying to write a Case statement that basically says. If a date exists, use that date, else add 30 days to the another Date. Heres what I have:

CASE WHEN CommOpp.[Start Date] IS NOT NULL THEN CommOpp.[Start Date]
ELSE (DateAdd(DAY, 30, OPPORTUNITY.[CF Date])) END AS NewDate

What I get is some non-readable code (see attached). Is there something special I have to do with Dates to get this to work?

I am writing the code in Durkins Impact Suite Report Writer, so it needs to work there, but Jim says it uses the OLEDB Reader.

Thanks in advance.

Rich Spitz

new date.png ‏2 KB

[knif · Accepted solution]

http://msdn.microsoft.com/en-us/library/ms187928.aspx

This link shows the syntax for both CAST and CONVERT.

In looking at your most recent image you attached, it looks like you want to pick Start Date if not null, otherwise, pick COMMSTARTDT. I'm not sure how you have it all set up, but could you possibly use COALESCE([Start Date], COMMSTARTDT)? This assumes that COMMSTARTDT is a "pickable" field. If it is calculated, could you try this:

COALESCE([Start Date], DATEADD(day,30,[CF Date])) END AS NewDate

COALESCE is basically picking the first non-null value in the list (the passed in values), in the order they are passed. It would be doing basically the same thing as your CASE. I think it's worth while to try based on the screenshot of the results you posted, since COMMSTARTDT appeared to be fine, especially if you can pick it and not put in the calculation.

[wellmet]

Could be something wrong with your table and column names.  This worked for me:

CASE WHEN TBL_Opportunity.CreateDate IS NOT NULL THEN TBL_OPPORTUNITY.CreateDate
ELSE (DateAdd(DAY, 30, TBL_OPPORTUNITY.[CreateDate])) END AS NewDate

Stan

[wellmet]

I reran it using this statement (I'm actually using SQL's T-SQL to run them)

CASE WHEN TBL_Opportunity.[CUST_NewDateField_010821960] IS NOT NULL THEN TBL_OPPORTUNITY.[CUST_NewDateField_010821960]
ELSE (DateAdd( DAY, 30, TBL_OPPORTUNITY.[CREATEDATE] )) END AS NewDate

It worked fine.  Are you sure you have a valid date in "CF Date" on the records that don't have a date in CommOpp.[Start Date]?  I assume there is an already established relationship between CommOpp and Opportunity.  Perhaps you should be using "CommOpp.[CF Date]"?

Stan

[richspitz]

Stan,

Thanks so much for trying to help me out. Just a few clarifications. There is a relationship between the tables Comm.Opp and Opportunity, but I tested just using one table and got the same results. I think I am OK as far as names and tables. I also tested using your scenario and got similar results. I think the problem is how dates are are being interpreted correctly. 

I get this to work

CASE WHEN CommOpp.[Start Date] IS NOT NULL THEN 'Something' ELSE 'Empty'

but I don't get this to work

CASE WHEN CommOpp.[Start Date] IS NOT NULL THEN CommOpp.[Start Date] ELSE 'Empty'

I have kluged a table together that shows the test data that I am working with and what the results I am getting are. 

Again, I am running this SQL statement inside of Durkins report writer and the results may be different if using T-SQL or another environment.

Thanks again for your help. As always you are the man, Stan.!

SQL testing.jpg ‏112 KB

[knif]

In reading this, I was wondering what are your fields declared as? Have you tried explicitly casting/converting the fields in question to a datetime type?

Example:
CASE WHEN CommOpp.[Start Date] IS NOT NULL THEN CAST(CommOpp.[Start Date] AS datetime)
ELSE DateAdd(DAY, 30, CAST(OPPORTUNITY.[CF Date] AS datetime)) END AS NewDate

Just some thoughts without knowing the table/view definitions that you have.

[richspitz]

Its definitely something to do with the date format as you implied. I tried the CAST as you suggested, but got the same result. What would be the format for the Convert statement.

Thanks for your help.

Rich

[knif · Accepted solution]

http://msdn.microsoft.com/en-us/library/ms187928.aspx

This link shows the syntax for both CAST and CONVERT.

In looking at your most recent image you attached, it looks like you want to pick Start Date if not null, otherwise, pick COMMSTARTDT. I'm not sure how you have it all set up, but could you possibly use COALESCE([Start Date], COMMSTARTDT)? This assumes that COMMSTARTDT is a "pickable" field. If it is calculated, could you try this:

COALESCE([Start Date], DATEADD(day,30,[CF Date])) END AS NewDate

COALESCE is basically picking the first non-null value in the list (the passed in values), in the order they are passed. It would be doing basically the same thing as your CASE. I think it's worth while to try based on the screenshot of the results you posted, since COMMSTARTDT appeared to be fine, especially if you can pick it and not put in the calculation.

[richspitz]

It worked!!!!

One small note is that you didn't need the END

So instead of 

COALESCE([Start Date], DATEADD(day,30,[CF Date])) END AS NewDate

It should be

COALESCE([Start Date], DATEADD(day,30,[CF Date])) AS NewDate

Thanks so much for your help Knif. 

[knif]

Sorry about the END, not sure why that got in there.

While, this got around the issue, it didn't really solve it. I'm still very puzzled why you were getting Unicode like characters as results, but I'm guessing I'll just have to chalk it up as something I'll never know.