01-31-2014 10:43 AM
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
02-03-2014 11:16 AM
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.
01-31-2014 11:05 AM
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
01-31-2014 11:18 AM
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
02-02-2014 07:20 AM
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.!
02-03-2014 06:00 AM
02-03-2014 10:23 AM
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
02-03-2014 11:16 AM
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.
02-04-2014 02:09 PM
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.
02-04-2014 02:16 PM