Trend Analysis.

[Bill]

At least I think that is what it is!!

I need to do a retrospective analysis of the number of Helpdesk Calllogs
that were open at the start of the week over the last few years. We have the
[open date] and the [closed date]. There are about 80,000 records. As well
as analysing the overall figure I would also like to do an analysis for each
member of staff.

I have to admit that I am struggling to make a start on this. I have a few
ideas, but in the past I have made this sort of thing into hard work,
whereas you guys have come with a straightforward solution

Any suggestions please?

Also can anyone suggest any good books that will help me with how to do this
sort of more complex analysis - at least it is to me!

Many thanks.
Bill.

 

[Guest]

Bill,
To clarify your request, does "open at the start of the week" mean something
like "open at 8.00 am Monday", or "open before Monday and closed Monday or
later"
Then presumably you are looking for weekly counts by "member of staff"
Does it matter if the log entry was opened and closed by different members,
or are these always the same?

Once these are defined, and the names of the fields are known we should be
able to offer more advice

Chris

 

[Bill]

I don't really mind. It could be be on a daily, weekly, monthly basis
really. I just want some sort of idea of how many calls were open on (say)
the Monday of each week. Use 8:00 am, or any other time (or day) if it
helps. This is intended to be an overall view of the number of 'open' calls
over a period of time. By open it means that, for the callog itself, an open
date exists, but a closed date does not.

Regarding the count for each member of staff, that may be something I have
to develop having (hopefully) learnt from the answer to the first question.
In fact the calllog is in a 'callog' table, wheras the technician is in an
'assignment' table, making things a little different for that side of my
question!! Help with the first issue would be much appreciated though.

Thanks for your reply.

Regards.
Roger.

Bill,
To clarify your request, does "open at the start of the week" mean something
like "open at 8.00 am Monday", or "open before Monday and closed Monday or
later"
Then presumably you are looking for weekly counts by "member of staff"
Does it matter if the log entry was opened and closed by different members,
or are these always the same?

Once these are defined, and the names of the fields are known we should be
able to offer more advice

Chris

At least I think that is what it is!!

I need to do a retrospective analysis of the number of Helpdesk Calllogs
that were open at the start of the week over the last few years. We have the
[open date] and the [closed date]. There are about 80,000 records. As well
as analysing the overall figure I would also like to do an analysis for each
member of staff.

I have to admit that I am struggling to make a start on this. I have a few
ideas, but in the past I have made this sort of thing into hard work,
whereas you guys have come with a straightforward solution

Any suggestions please?

Also can anyone suggest any good books that will help me with how to do this
sort of more complex analysis - at least it is to me!

Many thanks.
Bill.

 

[Guest]

Still don't have quite enough info, but lets get some data flowing and refine
things from there. While it is likely this could be done in a single query, I
would suggest using two for clarity

In these queries, replace everything inside the braces {} - including the
braces - with the field/table names from your database

First query: (query1)
Select [{OperatorNumber}], int([{DateOpen}]) as Opened From [{LogTable}]
where DatePart("d",[{DateOpen}],1) = 2 AND int([{DateOpen}]) <>
int([{DateClosed}])

This will give you a list of all log entries with an OPEN date that falls on
a Monday, and the CLOSE date falls on another day

Use this as the source of your second query
Select [{OperatorNumber}], CVDate([Opened]) as CallDate,
Count([{OperatorNumber}]) as Calls From Query1 Group By [{OperatorNumber}],
CVDate([Opened])

I don't really mind. It could be be on a daily, weekly, monthly basis
really. I just want some sort of idea of how many calls were open on (say)
the Monday of each week. Use 8:00 am, or any other time (or day) if it
helps. This is intended to be an overall view of the number of 'open' calls
over a period of time. By open it means that, for the callog itself, an open
date exists, but a closed date does not.

Regarding the count for each member of staff, that may be something I have
to develop having (hopefully) learnt from the answer to the first question.
In fact the calllog is in a 'callog' table, wheras the technician is in an
'assignment' table, making things a little different for that side of my
question!! Help with the first issue would be much appreciated though.

Thanks for your reply.

Regards.
Roger.

Bill,
To clarify your request, does "open at the start of the week" mean something
like "open at 8.00 am Monday", or "open before Monday and closed Monday or
later"
Then presumably you are looking for weekly counts by "member of staff"
Does it matter if the log entry was opened and closed by different members,
or are these always the same?

Once these are defined, and the names of the fields are known we should be
able to offer more advice

Chris

At least I think that is what it is!!

I need to do a retrospective analysis of the number of Helpdesk Calllogs
that were open at the start of the week over the last few years. We have the
[open date] and the [closed date]. There are about 80,000 records. As well
as analysing the overall figure I would also like to do an analysis for each
member of staff.

I have to admit that I am struggling to make a start on this. I have a few
ideas, but in the past I have made this sort of thing into hard work,
whereas you guys have come with a straightforward solution

Any suggestions please?

Also can anyone suggest any good books that will help me with how to do this
sort of more complex analysis - at least it is to me!

Many thanks.
Bill.