how do arrays work?

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
Wed Nov 19 12:12:10 CST 2003

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





"middletree" <middletree@htomail.com> wrote in message
news:OeLYiNsrDHA.3416@tk2msftngp13.phx.gbl...
> 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
>
>

Ray
Wed Nov 19 12:15:08 CST 2003

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" <middletree@htomail.com> wrote in message
news:OeLYiNsrDHA.3416@tk2msftngp13.phx.gbl...
> 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
>
>

middletree
Wed Nov 19 13:04:51 CST 2003

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.


"Ray at <%=sLocation%>" <myfirstname at lane34 dot com> wrote in message
news:eMnuRksrDHA.1764@TK2MSFTNGP10.phx.gbl...
> 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" <middletree@htomail.com> wrote in message
> news:OeLYiNsrDHA.3416@tk2msftngp13.phx.gbl...
> > 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
> >
> >
>
>

middletree
Wed Nov 19 14:02:36 CST 2003

This looks like something I can work with. Thanks!


"TomB" <shuckle@hotmail.com> wrote in message
news:#cLEAisrDHA.2432@TK2MSFTNGP10.phx.gbl...
> 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
>
>
>
>
>
> "middletree" <middletree@htomail.com> wrote in message
> news:OeLYiNsrDHA.3416@tk2msftngp13.phx.gbl...
> > 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
> >
> >
>
>

dlbjr
Wed Nov 19 16:09:14 CST 2003

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.
--
-dlbjr

Discerning resolutions for the alms

middletree
Wed Nov 19 16:26:43 CST 2003

Comments inline:

"dlbjr" <dontknow@do.u> wrote in message
news:gSRub.198$Qy4.14735@typhoon01...
> 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
>
> Discerning resolutions for the alms
>
>

dlbjr
Wed Nov 19 20:57:10 CST 2003

Had company and had to act like I cared.

I'll pound on it tomorrow afternoon.
--
-dlbjr

Discerning resolutions for the alms

Ray
Thu Nov 20 07:34:50 CST 2003

"dlbjr" <dontknow@do.u> wrote in message
news:c4Wub.199$Qy4.15048@typhoon01...
> Had company and had to act like I cared.

Outstanding! :]

Ray at work

William
Thu Nov 20 08:32:07 CST 2003

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




"TomB" <shuckle@hotmail.com> wrote in message
news:#cLEAisrDHA.2432@TK2MSFTNGP10.phx.gbl...
> 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
>
>
>
>
>
> "middletree" <middletree@htomail.com> wrote in message
> news:OeLYiNsrDHA.3416@tk2msftngp13.phx.gbl...
> > 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
> >
> >
>
>

Ray
Thu Nov 20 08:55:38 CST 2003

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

Ray at work

"William Morris" <NOwmorrisS_P-A.M@seamlyne.com> wrote in message
news:bpija5$1ovs8i$1@ID-205671.news.uni-berlin.de...
> 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
>

Chris
Thu Nov 20 16:44:03 CST 2003

"middletree" <middletree@htomail.com> wrote in message
news:eQSsDAtrDHA.2332@TK2MSFTNGP09.phx.gbl...
> 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
Thu Nov 20 17:08:19 CST 2003

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.


"Chris Hohmann" <nospam@thankyou.com> wrote in message
news:ObgvQa7rDHA.2060@TK2MSFTNGP10.phx.gbl...
> "middletree" <middletree@htomail.com> wrote in message
> news:eQSsDAtrDHA.2332@TK2MSFTNGP09.phx.gbl...
> > 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
>
>

dlbjr
Thu Nov 20 17:20:55 CST 2003

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
Thu Nov 20 17:31:28 CST 2003

"middletree" <middletree@htomail.com> wrote in message
news:%23yLQxs7rDHA.3744@TK2MSFTNGP11.phx.gbl...
> 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
Fri Nov 21 15:05:14 CST 2003

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


"Chris Hohmann" <nospam@thankyou.com> wrote in message
news:uOTtw07rDHA.2260@TK2MSFTNGP09.phx.gbl...
> "middletree" <middletree@htomail.com> wrote in message
> news:%23yLQxs7rDHA.3744@TK2MSFTNGP11.phx.gbl...
> > 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
Fri Nov 21 15:05:35 CST 2003

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


"dlbjr" <dontknow@do.u> wrote in message
news:u0cvb.200$Qy4.15106@typhoon01...
> 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
Fri Nov 21 16:02:34 CST 2003

"middletree" <middletree@htomail.com> wrote in message
news:uNka2MHsDHA.2224@TK2MSFTNGP09.phx.gbl...
> 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