Total Availability of a phone line

[peterb58]

CR XI on Sybase 11.

I have to write a report to show the utilisation of some telephone lines.

I need to show how much of the time the line was busy compared to the total availability of the line.

The actual busy time is the easy part, what is proving difficult is the line availability.

The lines are split into groups along the lines of the phone number dialled, so one phone number can actually have several lines associated with it.
I have a table called Line Groups which defines which line ( in the system ) is associated with which group.

Group Line
1 1
1 2
1 3
2 4
2 5

So for a given period I know the available number of seconds. I need to multiply that by the number of lines in a group to give availability for that group for that period.

I can't figure out a formula to pull back the number of lines per group. Systems could have many more than the example.

Once I have that, the rest is simple maths!!

Thanks

Pete

 

[lbass]

You should be able to use:

distinctcount({table.line},{table.group})

-LB

 

[peterb58]

Lbass

I have dropped such a line into the Group footer. Unfortunately, it will count a line if it has not had at least on call.

So the data I am working on at the moment does not have any calls using line 3. So my calculation shows 2 lines for Group 1 but only a single line (line 4 ) for Group 2.


thanks

 

[peterb58]

I created a command which just does a count(*) and group by to get the values I need. So I can calculate the total time available for the requested time period.

What I need now is a way to show an entry for a line or a group which may not have any details.

The main table with the calls in is linked to the Line_Groups table using the Line_No. I have tried setting this to all the Join options and reversing the link, to no avail.

Thanks

 

[lbass]

Please show the content of your command, and if you are using it with other tables (a no-no), please also show the content of the SQL query (database->show SQL Query).

-LB

 

[peterb58]

The command is:-

select group_no, count(*) from line_groups group by group_no;

Nice and simple

The current SQL for the reports is:-
+++++++++++++++++++++++++++++++++++++++++++++++++++++

SELECT "CHistStatEx"."NotificationTime", "CHistStatEx"."SLN", "LINE_GROUPS"."LINE_NO", "CHistStatEx"."LineBusyDurationSecs", "CHistStatEx"."Meaning", "CHistStatEx"."CallCode", "CHistStatEx"."OffHookTime", "CHistStatEx"."ClearTime", "LINE_GROUPS"."GROUP_NO"
FROM {oj "STANDARD"."CHistStatEx" "CHistStatEx" LEFT OUTER JOIN "CONTROLCENTRE"."LINE_GROUPS" "LINE_GROUPS" ON "CHistStatEx"."SLN"="LINE_GROUPS"."LINE_NO"}
WHERE ("CHistStatEx"."NotificationTime">={ts '2012-02-01 00:00:00'} AND "CHistStatEx"."NotificationTime"<{ts '2012-03-01 00:00:00'})
ORDER BY "LINE_GROUPS"."LINE_NO"

select group_no, count(*) from line_groups group by group_no;

select current user from dummy


SELECT "RV_ADDR_CC"."CC_NAME", "RV_ADDR_CC"."Line4", "RV_ADDR_CC"."Line1", "RV_ADDR_CC"."Line2", "RV_ADDR_CC"."Line3", "RV_ADDR_CC"."Line5", "RV_ADDR_CC"."Line6", "RV_ADDR_CC"."Line7"
FROM "STANDARD"."RV_ADDR_CC" "RV_ADDR_CC"


SELECT "RV_Dates"."PrevQuarterStart", "RV_Dates"."PrevMonthStart", "RV_Dates"."PrevWeekStart", "RV_Dates"."CurrentQuarterStart", "RV_Dates"."PrevQuarterEnd", "RV_Dates"."PrevMonthEnd", "RV_Dates"."PrevWeekEnd", "RV_Dates"."CurrentQuarterEnd"
FROM "CONTROLCENTRE"."RV_Dates" "RV_Dates"

++++++++++++++++++++++++++++++++++++++

RV dates is just to give me some calculated info and the RV_ADDR_CC allows me to put the current centres details on the report.
Current user is also just for information on the report.

The command is currently an Inner Join to Line_Groups on Command_1.Group_No=Line_Groups.Group_No

ChistStatEx has Left Outer Join to Line Groups on ChistStatEx.SLN=Line_Groups.Line_No.
( SLN is the internal Synthetic Line Number used by our system )

The report is Grouped by Group_No.

Pete

 

[lbass]

Try the following as your command (or the equivalent, where you do the left join FROM the line troups table TO the ChiststatEx table AND you build the criteria into the FROM clause). THis should preserve the left outer join despite the selection criteria. Then you would get ALL group lines, and there would be no need for a command for count.

SELECT "CHistStatEx"."NotificationTime", "CHistStatEx"."SLN", "LINE_GROUPS"."LINE_NO", "CHistStatEx"."LineBusyDurationSecs", "CHistStatEx"."Meaning", "CHistStatEx"."CallCode", "CHistStatEx"."OffHookTime", "CHistStatEx"."ClearTime", "LINE_GROUPS"."GROUP_NO"
FROM "CONTROLCENTRE"."LINE_GROUPS" "LINE_GROUPS"
left outer join "STANDARD"."CHistStatEx" "CHistStatEx" ON
"LINE_GROUPS"."LINE_NO"="CHistStatEx"."SLN" and
("CHistStatEx"."NotificationTime">={ts '2012-02-01 00:00:00'} AND "CHistStatEx"."NotificationTime"<{ts '2012-03-01 00:00:00'})
ORDER BY "LINE_GROUPS"."LINE_NO"

-LB

 

[peterb58]

Lbass

Thanks for the advice, but I was still having a problem.
Looks like the sticking point is specifying the NotificationTime. Since some of the lines may not have had calls, they would never have any valid NotificationTimes and would never appear.

I have got my DEV team to build me a procedure which does all the work for me. I just pass it a month and year and I get the appropriate values back.

Once again thanks for the help.

Pete