Community
Showing results for 
Search instead for 
Do you mean 
Reply

Help with SQL Case Statement

Accepted Solution Solved
Copper Contributor
Posts: 21
Country: United States
Accepted Solution

Help with SQL Case Statement

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


Accepted Solutions
Solution
Accepted by topic author richspitz
‎09-25-2015 03:20 AM
Nickel Contributor
Posts: 175
Country: USA

Re: Help with SQL Case Statement

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.

View solution in original post


All Replies
Silver Super Contributor
Posts: 2,328
Country: USA

Re: Help with SQL Case Statement

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


If you would like to get more out of ACT! you can find an ACT! Certified Consultant near you by going to:www.act.com/acc.
-------------------------------------------------------------------------------------
Stan Smith
ACT! Certified Consultant
ADS Programming Services, Inc.
(205) 222-1661
www.adsprogramming.com
www.actwebhosting.com
Click Here to Purchase Act!
Silver Super Contributor
Posts: 2,328
Country: USA

Re: Help with SQL Case Statement

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


If you would like to get more out of ACT! you can find an ACT! Certified Consultant near you by going to:www.act.com/acc.
-------------------------------------------------------------------------------------
Stan Smith
ACT! Certified Consultant
ADS Programming Services, Inc.
(205) 222-1661
www.adsprogramming.com
www.actwebhosting.com
Click Here to Purchase Act!
Copper Contributor
Posts: 21
Country: United States

Re: Help with SQL Case Statement

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

Nickel Contributor
Posts: 175
Country: USA

Re: Help with SQL Case Statement

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.
Copper Contributor
Posts: 21
Country: United States

Re: Help with SQL Case Statement

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

Solution
Accepted by topic author richspitz
‎09-25-2015 03:20 AM
Nickel Contributor
Posts: 175
Country: USA

Re: Help with SQL Case Statement

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.

Copper Contributor
Posts: 21
Country: United States

Re: Help with SQL Case Statement

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. 

Nickel Contributor
Posts: 175
Country: USA

Re: Help with SQL Case Statement

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.