Hi,
I want to calculate the Resolved time in mins for a ticket and availability for a day in crosstab.
e.g.
| 1-Jun-2012 | 2-Jun-2012 | 3-Jun-2012 |
----------|-------------- ----------------|--------------------------------|------------- --------------- -|--
|ResolvedTime|Availability|ResolvedTime|Availability |ResolvedTime|Availability|
----------|-----------------|-------------|-----------------|--------------|-----------------|------------- |--
12345 | 660 |54.16% | 1440 | 0% | 780 | 66.66% |
78980 | |100% | | 100% | | 100% |
Here, Start Date is 1-Jun- 2012 1:00 PM and Resolved Date is 3 - Jun -12 1:00 PM for ticket# 12345
Resolved time (in mins) = start Date - Resolved Date
Availability = ((24*60) - Resolved time (in mins))/(24*60)
For all other days, the availability should be 100%
My concern is that how to segregate the Resolved time daywise. Currently, I am getting below data:
| 1-Jun-2012 | 2-Jun-2012 |
--------- |-------------- ----------------|-------------------------------|--
|ResolvedTime|Availability|ResolvedTime|Availability |
--------- -|-----------------|-------------|-----------------|--------------|---
12345 | 2880 |-1% | | |
78980 | | | | |
Environment:
Cognos 8.2
SQL Server 2005
I believe you will need to break up the single row into 3 rows (representing the 3 dates) in the Tabular View of the Query.
I haven't done anything like this before, so I can't offer ideas on HOW to do it, but there are quite a few good date/time functions in Cognos, and probably more in your specific RDBMS's function set, so you will need to get creative and figure out how to derive 3 tabular rows from the single data row.
Search on the use of non-equi or theta joins. You can model solutions by using a calender table and an 'between' join. I think non-equi join cannot be build from within a report, but I use these from time to time to generate rows within a framework model..