Method:Classic General Q&A

Re: SQL Override Conditional Formatting

SMcGovern — Sun, 16 Mar 2014 19:36:25 GMT

Your second "fancy" solution did the trick. I think without converting to character was causing the issue. Once it was converted, worked like a charm.

Thanks!!!

Shawn

Re: SQL Override Conditional Formatting

Method_Paul — Sun, 16 Mar 2014 19:06:48 GMT

if you want to get fancy, and dance around the date conversion a little.....

(CASE WHEN [DueDateStart] < GETUTCDATE() THEN '' ELSE '' END) + CONVERT(varchar(10), DateAdd(HOUR,-5,viewActivity.DueDateStart), 20) + ''

Re: SQL Override Conditional Formatting

Method_Paul — Sun, 16 Mar 2014 19:00:51 GMT

In that case you want:

(CASE WHEN [DueDateStart] < GETUTCDATE() THEN '' ELSE '' END) + viewActivity.DueDateStart + ''

Re: SQL Override Conditional Formatting

SMcGovern — Sun, 16 Mar 2014 18:49:31 GMT

Paul,

If I move the END to the end of the statement, the case will not display the requested field unless it results in the ELSE portion of the CASE

I did try your suggestion but ended up with the same error.

I'll keep playing

Re: SQL Override Conditional Formatting

Method_Paul — Sun, 16 Mar 2014 18:32:11 GMT

Try:

(CASE WHEN [DueDateStart] < GETUTCDATE() THEN '' ELSE '' + viewActivity.DueDateStart + '' END)

- I haven't tried this, just a first glance attempt.

- better to use GETUTCDATE() instead, since all times are actually stored on the server without timezone information, so it can be translated and viewed properly anywhere in the world based on where you are.

- Your END was in the wrong spot, and you weren't wrapping in brackets.

BUT - note, using a date in an SQL override is undesirable. The date will come in UTC rather than converted to your local time zone, and it will come unformatted. No easy way around this.

Re: SQL Override Conditional Formatting

SMcGovern — Sun, 16 Mar 2014 18:23:18 GMT

So I have another one that I could use some help on. I want to be able to compare the current time to the requested start date and then color it red if its late. Here was my idea, but it returns "error generating grid"

CASE WHEN [DueDateStart] < GETDATE() THEN '' ELSE '' END + viewActivity.DueDateStart + ''

Any thoughts?

Thanks,

Shawn

Re: SQL Override Conditional Formatting

SMcGovern — Sat, 15 Mar 2014 21:21:13 GMT

In case anybody is interested, I figured out my own solution:

CASE WHEN [ActivityPriority] = '1-Emergency' THEN '' WHEN [ActivityPriority] = '2-24 Hour Response' THEN '' ELSE '' END + viewActivity.Entity + ' ' + '' + ISNULL(viewActivity.ActivityPriority,'') + ' ' + ISNULL(viewActivity.WorkOrderInstructions,'')

Thanks,

Shawn

SQL Override Conditional Formatting

SMcGovern — Sat, 15 Mar 2014 19:48:06 GMT

I'm trying to create a new dispatcher's dashboard and wanted to color code the Work Order grid based on the priority of the activity. I figured I could do something along the lines of:

CASE WHEN [viewActivity.ActivityPriority]='1-Emergency' then '' WHEN [viewActivity.ActivityPriority]='2-24 Hour Response' then '' ELSE END + '' + viewActivity.Entity + ' ' + '' + ISNULL(viewActivity.ActivityPriority,'') + ' ' + ISNULL(viewActivity.WorkOrderInstructions,'')

When I tried this I got "Error Generating Grid"

Do I have an issue with my CASE statement wrong or is this beyond the SQL Override?

Thanks,

Shawn