Outage calculation

[ShortyA]

Hi,
I am using CR XI reporting via ODBC against an Oracle database. The purpose of my report is to show the number of hours a service was unavailable during the month.

Data sample:

TICKET_ID (unique), START_DATE, END_DATE
1, 17/04/07, 03/05/07
2, 01/04/07, 07/04/07

If a ticket was opened and closed during the same month then it would be simple. However, I am not clear on how to show the amount of time the service was out in hours. For example TICKET_ID 1 was only out for 3 days (72 hours) in May but if I used the START_DATE and END_DATE I would retrieve several weeks. Has anyone been able to get a solution to this requirement as I am banging my head against the wall ?

Many thanks for any help!
ShortyA

 

[Madawc]

From the example you give, it's not apparent that ticket 1 was only out for 3 days. Just what do the records look like?

If the details allow, you might be able to get hours at record level using DateDiff. Put this in a formula field and then add, if only one record is involved. Otherwise accumulate using variables.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[ShortyA]

Hi Madawc,
in the example above the report would be run for a fixed date range, for instance month to date or last full month. If the report was run for the month to date then for ticket 1 only 3 days of the month to date would have been out. Doing a datediff on the START and END felds would give me the total but I am trying to ascertain how much of that total outage for a row eg ticket 1, is in the reporting range "last full month" or "month to date".
ShortyA

 

[Madawc]

You mean the three days is wrong?

If you want to report on just the month, but show outage for a longer period, then select everything and suppress the detail lines you don't want.

Right-click on the section and choose Format Section. Then choose the formula icon (x+2 and a pencil) for suppression.


Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[ShortyA]

If I was running the report for the month to date then ticket 1 has been out for 3 days. However, the actual outage is just over a couple of weeks. What I want to do is show an outage for a specific month. For May ticket 1 was out for 3 days. If I then run the same report but for April it should show ticket 1 as having 13 days of outage (ie the part of the outage that was in April).

The simplest way to explain it is that for an outage (START to END) I only want to show the number of days within a specific month instead of the whole outage. It is to do with Service Availability and how well a provider performed.

 

[lbass]

Your formula is a little more complex than just a datediff, because you are apparently excluding certain days (weekends?). Anyway, maybe you can adapt the following:

//{@starttoend}:
if month({table.startdate}) = month({table.enddate}) then
datediff("h", {table.startdate},{table.enddate})

//{@firstmonth}:
if month({table.startdate}) <> month({table.enddate}) then
datediff("h",{table.startdate}, dateadd("m",1,{table.startdate})-day({table.startdate}))

//{@secondmonth}:
if month({table.startdate}) <> month({table.enddate}) then
datediff("h",{table.enddate}-day({table.enddate})+1,{table.enddate})

-LB

 

[ShortyA]

Thanks for your reply. The figures are going to be used for trend analysis over a maximum 10 year period so could be used but would be heavy on the formulas. I think this may be a case of the data not being in the format required for reporting. I will investigate setting up a view that utilises a table containing every month during the period and relate that into the service table to calculate how many hours within the month were out.
Thanks again!
ShortyA

 

[GJParker]

One way to accomplish this would be to create a calendar table which contained a record for each working day.

This can then be linked to your existing table to give 1 record for every day between the start date and end date.

the join would need to be

Calendar.date >= table.start_date and
calendar.date <= table.end_date

HTH

Gary Parker
MIS Data Analyst
Manchester, England