Argh ... Next SQL/ASP dilema

[TonyRosen]

I have an SQL database with a table set up similar to:

id | identifier | activityNumber | locationID | activity_date

The locationID field can repeat, so it is possible to have something like:

1 | 200 | 1 | 3 | 4/11/2004
....
5 | 200 | 3 | 3 | 4/15/2004
6 | 200 | 2 | 2 | 4/15/2004
....
10 | 200 | 3 | 2 | 5/3/2004
11 | 200 | 2 | 4 | 5/3/2004

With the above data, I'd want to see where "200" was (locationID) on 5/1/2004

I've gotten as far as:
strSQL = "SELECT * FROM this_table WHERE identifier = '200';"
Set objRS = objConn.Execute(strSQL)
If objRS.EOF Or objRS.BOF Then
'Do whatever I want it to do if nothing is found
Else
objRS.MoveFirst
Do While Not objRS.EOF Or objRS.BOF
iActivityNumber = objRS.Fields("activityNumber")
iLocationID = objRS.Fields("locationid")
vActivityDate = objRS.Fields("activity_date")
If iActivityNumber = 1 Or iActivityNumber = 2 Then
'Check the date
If vActivityDate = "4/1/2004" Or vActivity < "4/1/2004" Then
'I want to use this ONE entry and stop checking
Else
'Do nothing and keep checking
End If
Else
'Don't do anything
End If
objRS.MoveNext
Loop
End If

I do I stop the checking this identifier and go on to the next one? The identifier is being pulled and used as a variable right before this, and there are literally hundreds of them.

 

[mbiro]

First of all, why don't you adjust your query so that it only returns '200' for the day in question?

 

[vbkris]

sql = "SELECT * FROM this_table WHERE identifier = '200' and date=# 5/1/2004#"

rs.open sql,con
response.write rs("locationID")


Known is handfull, Unknown is worldfull

 

[mbiro]

Exactly.

 

[TonyRosen]

Ah. I wasn't clear ... the "day in question" isn't necessarily the right thing I'm looking for.

Specifically, we need to know where '200' (or the previous, or the next - this part is derived dynamically from a different table and loops through until it's "finished" with '200' -- which is the part I'm looking for ... the finished part) is on 4/1/2004 in the example above.

They may have arrived at where ever they are at (locationID) before 4/1/2004 .... I just need to know where they are at at that date.

 

[vbkris]

now u really arent clear, i am confused...

Known is handfull, Unknown is worldfull

 

[TonyRosen]

Okay, full script with this type of info:

We'll call this Table1
id | identity | firstname | lastname

we'll call this Table2
id | identifier | activityNumber | locationID | activity_date

strSQL = "SELECT * FROM Table1 ORDER BY id;"
Set objRS = objConn.Execute(strSQL)
If objRS.EOF Or objRS.BOF Then

Else
objRS.MoveFirst
Do While Not objRS.EOF Or objRS.BOF
iIdentity = objRS.Fields("identity")
' Since the last entry will have the highest id#, we
' shall sort DESC
strSQL1 = "SELECT * FROM Table2 WHERE identifier = '" & iIdentity & "' ORDER BY id DESC;"
Set objRS1 = objConn.Execute(strSQL1)
If objRS1.EOF Or objRS1.BOF Then
' I do not really want to do anything to this one
Else
objRS1.MoveFirst
Do While Not objRS1.EOF Or objRS1.BOF
iActivityNumber = objRS1.Fields("activityNumber")
iLocationID = objRS1.fields("locationID")
vActivityDate = objRS1.Fields("activity_date")
If iActivityNumber = 1 Or iActivityNumber = 2 Then
If vActivityDate = '4/1/2004' Or vActivityDate < "4/1/2004" Then
'I want to count this one, and stop checking
'this identifier and move on to the next one.
' And, that's my major issue
Else
'Move on to the next entry in Table2 until we
' get to the entry which fits our criteria
End If
objRS1.MoveNext
Loop
End If
objRS.MoveNext
Loop
End If

Does that help?

 

[vbkris]

strSQL1 = "SELECT * FROM Table2 WHERE identifier = '" & iIdentity & "' and (activityNumber=1 or activityNumber=2) and activity_date<=#4/1/2004# ORDER BY id DESC;"


this sql will give u only the records that will fulfill the conditions that u have set...


Known is handfull, Unknown is worldfull

 

[mbiro]

If you add a top 1 to it, you will only get the relevant record

strSQL1 = "SELECT TOP 1 * FROM Table2 WHERE identifier = '" & iIdentity & "' and (activityNumber=1 or activityNumber=2) and activity_date<=#4/1/2004# ORDER BY id DESC;

 

[TonyRosen]

Aye, except ... having to deal with somebody else's code, I have to stay within what they've set this up to do ....

The activity_date field isn't an actual "datetime" field ... I have to get the information out and CDate the thing ... thus, the above won't work

 

[vbkris]

what kind of field is it? if its not dat/time then change it to date/time, if all old values in it are dates it can be easily converted...

Known is handfull, Unknown is worldfull

 

[TonyRosen]

It's now a datetime field ... but, that still didn't work ...

 

[DreXor]

i guess what i'm missing here or what the others might be missing is the flow of things, specifically the seed values.

do you start with 200? or with the date value? or both ?

do you list everything, then give an option to "filter" or search for a date or i guess it's an event (200).

are you looking to output everything and essentially give a form of "total" box at the end of each section being where someone is on the grouping of date?




DreX
aKa - Robert
if all else fails, light it on fire and do the happy dance!

 

[TonyRosen]

Here's the question boiled down to 1 sentence ...

How do I make a Do While Not ... Loop stop before the end?

 

[Chopstik]

exit loop

------------------------------------------------------------------------------------------------------------------------
If you don't have a sense of humor, you probably don't have any sense at all.
- Anonymous

 

[TonyRosen]

Microsoft VBScript compilation error '800a040f'

Invalid 'exit' statement

/reports/scripts/census.bom.1.asp, line 109

Exit Loop

 

[Chopstik]

Ok, let me put my brain back in. Try

exit do

------------------------------------------------------------------------------------------------------------------------
If you don't have a sense of humor, you probably don't have any sense at all.
- Anonymous

 

[TonyRosen]

heh ... already did that ... it now "works", but the numbers aren't even close, which means that it's now counting something it's not supposed to ... which, I think I can fix with a simple temp table ...