Cisco Support Community
cancel
Showing results for 
Search instead for 
Did you mean: 
Announcements

Welcome to Cisco Support Community. We would love to have your feedback.

For an introduction to the new site, click here. If you'd prefer to explore, try our test area to get started. And see here for current known issues.

New Member

CUIC Relative Dates with Days of Week in Anonymous Block

Hi All -

I seem to be missing something in terms of how to correctly format my SQL in Anonymous Block. I want to enable my report to pull from specific Days of the Week. I have added the parameter with the "_Days" suffix so that the Days show up on my Filter page for the report, but the query itself is bombing.

Can I ask how I specify/format the parameters in my SQL? I have something like:

"...WHERE DateTime BETWEEN :start_date :dow..."

When I look at the SQL, it is formatting it like this: '2014-10-12 00:00:00' 'MON:TUE:WED:THU:FRI'

How do I use that date format specification? Do I need to cast/convert it?

Thanks!

- Bill
Everyone's tags (3)
1 ACCEPTED SOLUTION

Accepted Solutions

When editing your "_Days"

When editing your "_Days" parameter, do you see an option for "Value Delimiter"? If so, you should change that to a comma. Quoting the individual values would be good to keep as well.  Assuming :dow is your _Days parameter, your resulting WHERE statement should look something like:

WHERE
DateTime BETWEEN :StartDate AND :EndDate
AND UPPER(CAST(datename(dw,DateTime) AS VARCHAR(3))) IN (:dow)

 

-Jameson

-Jameson
6 REPLIES

Hi,yes, you need to adjust

Hi,

yes, you need to adjust your SQL query, I am afraid, but let me understand this: you want to filter within a certain interval - let's say, one month (2014-10-01 00:00:00 - 2014-11-01 00:00:00) so that weekends are excluded (meaning you don't really need a separate parameter in the report, you can sort of hardcode this filter into your SQL query WHERE clause); or do you want to offer the end user the possibilit of picking certain days within a week (within an interval of course) - for instance, within one month (2014-10-01 00:00:00 - 2014-11-01 00:00:00) so that only days chosen by the user are counted (for instance, Monday and Wednesday)? 

G.

New Member

Hi Gergely -Thank you for

Hi Gergely -

Thank you for responding, and apologies as I wrote this post after a long day, so it may not be very coherent! I'm referencing the latter of your options, as this is a "built-in" capability in CUIC. I just haven't seen an example of how to actually implement it with Anonymous Block.

The link to the reference is here: Enable Relative Date

I have it showing correctly and enabling the "Only show results that are on certain days of the week" section in my Filter dialog, but the days are returned in a big String in the following format:

'MON:TUE:WED:THU:FRI:SAT:SUN'

I can modify the parameter to have it quote the individual values, but it is always separated by the colons. Do I need to use a complete parsing function to put the values into a temp table or something, then reference them in that way? Seems like a pretty poor implementation, but...! 

- Bill

When editing your "_Days"

When editing your "_Days" parameter, do you see an option for "Value Delimiter"? If so, you should change that to a comma. Quoting the individual values would be good to keep as well.  Assuming :dow is your _Days parameter, your resulting WHERE statement should look something like:

WHERE
DateTime BETWEEN :StartDate AND :EndDate
AND UPPER(CAST(datename(dw,DateTime) AS VARCHAR(3))) IN (:dow)

 

-Jameson

-Jameson
New Member

Thank you - this is what I

Thank you - this is what I ended up with (or very similar) after a few iterations. My specific formula was:

WHERE AT.AgentTeamID IN (:team_list)
  AND ASGI.DateTime BETWEEN :start_date AND :end_date
  AND CHARINDEX(UPPER(SUBSTRING(DATENAME(dw, ASGI.DateTime),1,3)),:dow) > 0

Not sure how elegant it is, but it seems to work ok and the query seems to return results pretty quickly, so I'm happy with it!

Thanks for the responses!

- Bill

Very interesting solution. I

Very interesting solution. I'll have to remember that use of CHARINDEX.

If you can get CUIC to change the delimeter to comma and use IN instead of CHARINDEX, you might get a slight speed improvement. Not sure.

-Jameson

-Jameson
New Member

I tried a few variations

I tried a few variations before using the formula above, but it seems CUIC is insistent on maintaining the colon as the delimiter (ya know, because that's such a common delimiter...) </sarcasm> !!

- Bill
380
Views
0
Helpful
6
Replies