how do arrays work?

Discussion in 'ASP General' started by middletree, Nov 19, 2003.

  1. middletree

    middletree Guest

    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, Nov 19, 2003
    #1
    1. Advertising

  2. middletree

    TomB Guest

    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" <> wrote in message
    news:...
    > 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, Nov 19, 2003
    #2
    1. Advertising

  3. middletree

    Ray at Guest

    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" <> wrote in message
    news:...
    > 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 at, Nov 19, 2003
    #3
  4. middletree

    middletree Guest

    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:...
    > 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" <> wrote in message
    > news:...
    > > 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, Nov 19, 2003
    #4
  5. middletree

    middletree Guest

    This looks like something I can work with. Thanks!


    "TomB" <> wrote in message
    news:#...
    > 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" <> wrote in message
    > news:...
    > > 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, Nov 19, 2003
    #5
  6. middletree

    dlbjr Guest

    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
     
    dlbjr, Nov 19, 2003
    #6
  7. middletree

    middletree Guest

    Comments inline:

    "dlbjr" <> 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
    >
    >
     
    middletree, Nov 19, 2003
    #7
  8. middletree

    dlbjr Guest

    Had company and had to act like I cared.

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

    Discerning resolutions for the alms
     
    dlbjr, Nov 20, 2003
    #8
  9. middletree

    Ray at Guest

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


    Outstanding! :]

    Ray at work
     
    Ray at, Nov 20, 2003
    #9
  10. 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" <> wrote in message
    news:#...
    > 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" <> wrote in message
    > news:...
    > > 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
    > >
    > >

    >
    >
     
    William Morris, Nov 20, 2003
    #10
  11. middletree

    Ray at Guest

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

    Ray at work

    "William Morris" <> wrote in message
    news:bpija5$1ovs8i$-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
    >
     
    Ray at, Nov 20, 2003
    #11
  12. "middletree" <> wrote in message
    news:...
    > 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
     
    Chris Hohmann, Nov 20, 2003
    #12
  13. middletree

    middletree Guest

    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" <> wrote in message
    news:...
    > "middletree" <> wrote in message
    > news:...
    > > 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, Nov 20, 2003
    #13
  14. middletree

    dlbjr Guest

    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
     
    dlbjr, Nov 20, 2003
    #14
  15. "middletree" <> wrote in message
    news:%...
    > 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. :)
     
    Chris Hohmann, Nov 20, 2003
    #15
  16. middletree

    middletree Guest

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


    "Chris Hohmann" <> wrote in message
    news:...
    > "middletree" <> wrote in message
    > news:%...
    > > 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, Nov 21, 2003
    #16
  17. middletree

    middletree Guest

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


    "dlbjr" <> 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
    >
    >
     
    middletree, Nov 21, 2003
    #17
  18. "middletree" <> wrote in message
    news:...
    > 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
     
    Chris Hohmann, Nov 21, 2003
    #18
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Alexandra Stehman
    Replies:
    5
    Views:
    30,653
    Chris Smith
    Jun 17, 2004
  2. Bill Reyn
    Replies:
    3
    Views:
    2,259
    Bob Hairgrove
    Jun 22, 2004
  3. Mantorok Redgormor

    initializing arrays of arrays

    Mantorok Redgormor, Sep 10, 2003, in forum: C Programming
    Replies:
    4
    Views:
    569
  4. kelvSYC

    Arrays and Pointers to Arrays

    kelvSYC, Sep 26, 2003, in forum: C Programming
    Replies:
    2
    Views:
    386
  5. Philipp
    Replies:
    21
    Views:
    1,130
    Philipp
    Jan 20, 2009
Loading...

Share This Page