how do arrays work?

[middletree]

OK, you pros out there are rolling your eyes at the subject line, but I have
never had to use arrays before.
The scenario:
ASP Intranet app keeps track of trouble tickets for a tech support group.
Table called Ticket has one row per ticket. Table named History has 0, 1, or
many rows per ticket. As the tech support person makes notes to update the
ticket, it adds a new row per entry into History table. One of the things
storeed in this table is the status (Closed, Suspended, Customer Research,
Customer Testing, my company research, my company coding, or my company
testing). I put it into this table instead of the Ticket table because we
want to know what state it was in as it when through the system until it got
resolved.

It works fine, so far, after 2 months in production. However, now the boss
wants a report which will summarize how long each ticket spent in each
state. So it will show that a ticket spent 3 hours in Customer Testing, 12
hours in my company research, 19 hours in my company coding, etc.

Here's what I have done so far to make this happen:

'(created 2 recordsets, called RSTicketList and RSTicketHistory)

Do While not RSTicketList.EOF

set rsTicketHistory = nothing
Set rsTicketHistory = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT * "
strSQL = strSQL & "FROM dbo.TKT_HISTORY H "
strSQL = strSQL & "WHERE TicketID ='"&strTicketID&"' "
rsTicketHistory.Open strSQL, objConnection,1,1

strTempStatus = rsTicketHistory("Status")
strTempTime = rsTicketHistory("TIMESTMP")


Do While not rsTicketHistory.EOF
If strTempStatus = rsTicketHistory("Status") then
'keep checking
Else
strStatusTime = somefunction(strTempTime -
rsTicketHistory("TIMESTMP"))
End if

----------------------------------------------------------------

NOTE: the "somefunction" thing in that 2nd-to-last line is a reference to
the fact that I will have to pull out hours which dont' fall into the
8:00-5:00 timeframe, but that's a bridge I'll cross when I get to it.

NOTE: Although the above code looks as if it would tax the performance of
the database and web server, this report would only be run once a month, and
only a few people use this app at any one time, anyway. Usually, less than 5
people.

Back to the problem: the time needs to be stored into that variable called
strStatusTime, but since it's a loop, there will be several. This probably
needs an array. (Please tell me if there is any easier way) Although I am
aware of their existence, I am not sure how to code arrays. I have
researched MSDN and my ASPbooks, but they aren't much help. They give some
philosophy about arrays, but I already know that stuff. I do much better
with code examples.

Is there an easier way than what I am doing? If I am on the right track,
can anyone offer me code samples or reference to code samples?
thanks

 

[TomB]

A simple example.....

Dim arrStrings(5) 'Create an array with 5 elements, numbered from 0 -
4

'Populate each value
arrStrings(0)="Hi"
arrStrings(1)="there."
arrStrings(2)="How"
arrStrings(3)="are"
arrStrings(4)="you."

Dim iLoop
for iLoop = 0 to 4
Response.write arrStrings(iLoop) & " "
next

This is a single dimension array. You can have multiple dimensions-I'm sure
there's a limit but I don't know what it is.
A two dimensional array you can think of like a table or a tic-tac-toe board

X O X
X O X
X O X

So the X at the top left would be (0,0), the O beside it is (0,1) and the
final X on that row is (0,2),
the next row is (1,0) - (1,1) - (1,2)
the bottom row is (2,0) - (2,1) - (2,2)

A three dimensional one would just be represented like (1,1,1) and four
dimensions like (1,1,1,1) -- Once you get past three or four dimensions, you
need a good imagination to visualize it.


In your question you don't know how big your array will be.
You can either loop through the recordset, like you are doing, and ReDim
preserve your array OR......

use GetRows method of your recordset to get it automagically converted to an
array!

Dim arrTickHistory
Set arrTickHistory=rsTicketHistory.GetRows()

Since you used a SELECT * in your query (naughty, naughty) I don't know how
many columns were returned. Let's pretend just two in which case your array
looks like.....

arrTickHistory(0,0) contains the returned value from first column, first row
arrTickHistory(1,0) contains the returned value from second column, first
row
arrTickHistory(0,1) contains the returned value from first column, second
row

etc....


Hope that helps

 

[Ray at]

I don't know that this is the best scenario for working with arrays. What I
would do in this case, is either figure out a query that will return the
summarized data already (I can't think of one, as I'm quite the querying
rookie), or create a variable for each status. You could put all the status
time subs into an array, but there wouldn't be much advantage, in my
opinion.

Ray at work

 

[middletree]

Thanks. I am less far along with queries than ASP, so I posted this question
to the SQL Prog forum, didn't get a workable answer.

 

[middletree]

This looks like something I can work with. Thanks!

 

[dlbjr]

middletree,

Can you post the field list per table?
I have posted a function to get the work hours between two date stamps
before.

I can repost if needed.

An expeditious solution would be a dictionary object to total the time per
section.
I will help if needed.

 

[middletree]

Comments inline:

middletree,

Can you post the field list per table?

Not sure what you mean. Do you mean list the fields here?

If so:

Tablename: Ticket
Fields: TicketID(identity field-integer), ActualTimeStamp, LastModified(a
timestamp field), AssignedEmployee (an integer, which I'll join with
Employee table to pull first and lastname), Environment(varchar),
ProbTye(int, join with ProbType table to obtain description)

and from Tablename: History:
Fields: TicketID (FK --PK on Ticket table), TIMESTMP, Status(Int, joined
with Status table to get description)

I have posted a function to get the work hours between two date stamps
before.

I can repost if needed.

Yes, that was a few days ago, in response to my question. I will have that.
I'm trying to tackle this first, then that.

An expeditious solution would be a dictionary object to total the time per
section.

I am not much of an ASP guy, and not sure what a dictionary is. I figure
it's something good to learn, but don't know if I can learn it in time to
get this done by next Wednesday, which is when the boss promised it to the
CEO.

I will help if needed.
--

I appreciate that.

 

[dlbjr]

Had company and had to act like I cared.

I'll pound on it tomorrow afternoon.

 

[Ray at]

Had company and had to act like I cared.

Outstanding! :]

Ray at work

 

[William Morris]

I think the dimensional limit is 256, though what you would possibly want to
do with an array that size is beyond me. In 15 years, I've never had a
reason to go above 3.

- Wm

William Morris
Product Development, Seritas LLC

 

[Ray at]

You've obviously never mapped out the dimensions of object's transformation
over time vbscript. ;]

Ray at work

 

[Chris Hohmann]

Thanks. I am less far along with queries than ASP, so I posted this question
to the SQL Prog forum, didn't get a workable answer.

When you get "further along" with queries, here's a stored procedure to
get you started. ;-)

CREATE PROCEDURE uspTicketStatusDurations
@TicketID INT
AS
SELECT
S.Description,
SUM(TH.Duration) AS Duration
FROM
Status AS S,
(
SELECT
THC.Status,
DATEDIFF("s",THC.TIMESTMP,MIN(THN.TIMESTMP)) AS Duration
FROM
TKT_HISTORY AS THC,
TKT_HISTORY AS THN
WHERE
THC.TicketID = @TicketID AND
THN.TicketID = @TicketID AND
THC.TIMESTMP < THN.TIMESTMP
GROUP BY
THC.TIMESTMP,
THC.Status
) AS TH
WHERE
TH.Status = S.StatusID
GROUP BY
S.Description


Notes:
1. In your original post, you wrapped the TicketID value in apostrophes.
This indicated that the datatype was a string. However, in a follow-up
post you indicate that the TicketID is an integer. In the future, please
provide details about related table structures. It would save us both
some time. ;-)

2. Also, please specify what database/version you are using. For the
above, I assumed SQL Server 2000.

3. The duration column returned by the above stored procedure is
reported in seconds.


HTH
-Chris Hohmann

 

[middletree]

Thanks for this. For the record, SQL Server 2000 is my RDBMS, and in the
tables, TicketID is in fact an Interger, yet when I have it in quotes:
Select * From Ticket Where TicketID = '12345'

it works fine.

 

[dlbjr]

Chris,

Your solution is the ultimate since MSSQL is in use.
This is why I asked if middletree was using Access.

Stored Procedures is the only way to go!

-dlbjr

Discerning resolutions for the alms

 

[Chris Hohmann]

Thanks for this. For the record, SQL Server 2000 is my RDBMS, and in the
tables, TicketID is in fact an Interger, yet when I have it in quotes:
Select * From Ticket Where TicketID = '12345'

it works fine.

Yes, there's an implicit coercion taking place, but for those of us
trying to reproduce your environment, knowing the column datatypes
helps.

 

[middletree]

Whatever I can do to help you help me, I'll do it.

 

[middletree]

I don't have the option of doing SP's. Long story, Painful, even.

 

[Chris Hohmann]

I don't have the option of doing SP's. Long story, Painful, even.

Hmm? I won't ask why, but you have my condolences. You're missing out on
A LOT without stored procedures. However, in this case, there's no real
programmatic logic in the stored procedure so you could just build the
SQL equivalent and run that. They are letting you run queries, right!?
;-)

strSQL = "" & _
"SELECT" & _
"S.Description," & _
"SUM(TH.Duration) AS Duration" & _
"FROM" & _
"Status AS S," & _
"(" & _
"SELECT" & _
"THC.Status," & _
"DATEDIFF("s",THC.TIMESTMP,MIN(THN.TIMESTMP)) AS Duration" & _
"FROM" & _
"TKT_HISTORY AS THC," & _
"TKT_HISTORY AS THN" & _
"WHERE" & _
"THC.TicketID = " & strTicketID & " AND" & _
"THN.TicketID = " & strTicketID & " AND" & _
"THC.TIMESTMP < THN.TIMESTMP" & _
"GROUP BY" & _
"THC.TIMESTMP," & _
"THC.Status" & _
") AS TH" & _
"WHERE" & _
"TH.Status = S.StatusID" & _
"GROUP BY" & _
"S.Description"

-Chris Hohmann