我有一个表，在这个例子中，2列：
Person
Description
我需要创建一个结果表是这样的：
Person Yes No Total
John 1 5 6
Laura 4 9 13
Peter 0 1 1
Total 5 15 20
我把它直，但我必须做一个检查，如果有是字符串中的日期说明，弥敦道赖斯在这里它帮助过我的人： 得到一个范围的日期和搜索的字符串 。 一个人可以有N个说明，所以我需要循环播放。 如果在描述被发现的日期加1，是的，否则加1号
CODE已更新
<%
varYesTotal = 0
varNoTotal = 0
Do While Not rsPerson.EOF
varYes = 0
varNo = 0
strPersonName = rsPerson("Person")
Set rsCheckYesNo = T.Execute("SELECT Description FROM Person Where Person= '" & strPersonName & "' ORDER BY Person ASC")
strDescription= rsCheckYesNo("Description")
For intDateDiff = 0 to DateDiff("d",DataInicial,DataFinal)
arrDateParts = Split(DateAdd("d",intDateDiff,DataInicial),"/")
If arrDateParts(1) <= 9 Then
arrDateParts(1) = "0" & arrDateParts(1)
End If
strCheckDate = arrDateParts(0) & "/" & arrDateParts(1) & "/" & arrDateParts(2)
Do While Not rsCheckYesNo.EOF
strDescription= rsCheckYesNo("Description")
boolDateFound = False
If InStr(strDescription, strCheckDate) > 0 Then
boolDateFound = True
varYes = varYes + 1
Else
varNo = varNo + 1
End If
rsCheckYesNo.MoveNext
Loop
Next
%>
<%
Set rsPerson= T.Execute("SELECT DISTINCT Person FROM Table")
If Not rsPerson.EOF Then
%>
<table>
<thead>
<tr>
<th>Person</th>
<th>Yes</th>
<th>No</th>
<th>Total</th>
</tr>
</thead>
<%
'We need 2 sets of counters, one set that gets set
'to zero so we can total all the records.
varYesTotal = 0
varNoTotal = 0
Do While Not rsPerson.EOF
'The other set of counters gets reset per user
'so it should be inside the users loop.
varYes = 0
varNo = 0
strPersonName = rsPerson("Person")
Set rsCheckYesNo = T.Execute("SELECT Description FROM Person Where Person= '" & strPersonName & "' ORDER BY Person ASC")
strDescription= rsCheckYesNo("Description")
Do While Not rsCheckYesNo.EOF
'Start Nathan Rice Code
boolDateFound = False
For intDateDiff = 0 to DateDiff("d",DataInicial,DataFinal)
arrDateParts = Split(DateAdd("d",intDateDiff,DataInicial),"/")
If arrDateParts(1) <= 9 Then
arrDateParts(1) = "0" & arrDateParts(1)
End If
strCheckDate = arrDateParts(0) & "/" & arrDateParts(1) & "/" & arrDateParts(2)
If InStr(strDescription, strCheckDate) > 0 Then
boolDateFound = True
varYes = varYes + 1
Exit For
Else
varNo = varNo + 1
End If
Next
'End Nathan Rice Code
rsCheckYesNo.MoveNext
Loop
%>
<tbody>
<tr>
<td><%=strPersonName%></td>
<td><%=varYes%></td>
<td><%=varNo%></td>
<td><%=(varYes + varNo)%></td>
</tr>
</tbody>
<%
varYesTotal = varYesTotal + varYes
varNoTotal = varNoTotal + varNo
rsPerson.MoveNext
Loop
rsPerson.Close
%>
<tfoot>
<tr>
<td>Total</td>
<td><%=varYesTotal%></td>
<td><%=varNoTotal%></td>
<td><%=(varYesTotal+varNoTotal)%></td>
</tr>
</tfoot>
</table>
<%
End If
%>
你的计划应该是这样的：
PreP for whole task
Database
Date range
Total
Print header
Get persons
For all persons
PreP for person
Get name
Get descriptions
For all descriptions
Check and count
Next
PostP for person
Compute yes/no/all (one from two)
Print row
Update total
Next
PostP for whole task
Print total
Database
看起来你的代码没有说明循环。
更新：
在代码：
Option Explicit
Dim greDate : Set greDate = New RegExp
greDate.Global = True
greDate.Pattern = "(\d{2})/(\d{2})/(\d{4})" ' dd/mm/yyyy
Dim aTotal : aTotal = Array(0,0,0)
Dim aTests : aTests = Array( _
Array( "peter" _
, "In 21/02/2014 something happened") _
, Array( "paul" _
, "pi 19/02/2014 pa 26/02/2014 po" _
, "In 21/02/2013 something happened") _
, Array( "mary" _
, "pi 19/02/2014 pu 20/02/2014 25/02/2014 26/02/2014 po" _
, "pi 19/02/2014 pu 20/02/2014 ") _
)
Dim aPers
Dim dtFrom : dtFrom = #2/20/2014#
Dim dtTo : dtTo = #2/25/2014#
For Each aPers In aTests
WScript.Echo "#####", aPers(0)
Dim aPSum : aPSum = Array(0, 0, 0)
Dim nDescr
For nDescr = 1 To UBound(aPers)
Dim sTest : sTest = aPers(nDescr)
WScript.Echo "-----", qq(sTest)
Dim aDates : aDates = getDatesFrom(sTest, dtFrom, dtTo)
If -1 = UBound(aDates) Then
WScript.Echo " no interesting dates found."
aPSum(1) = aPSum(1) + 1
Else
WScript.Echo " found (m/d/yyyy!)", Join(aDates, ", ")
aPSum(0) = aPSum(0) + 1
End If
Next
aPSum(2) = aPSum(0) + aPSum(1)
WScript.Echo "*****", Join(aPSum)
Dim i
For i = 0 To UBound(aTotal) : aTotal(i) = aTotal(i) + aPSum(i) : Next
Next
WScript.Echo "#####", Join(aTotal)
Function getDatesFrom(sText, dtFrom, dtTo)
ReDim aTmp(-1)
Dim oMTS : Set oMTS = greDate.Execute(sText)
Dim oMT, dtFound
For Each oMT In oMTS
' dd/mm/yyyy
dtFound = DateSerial(CInt(oMT.SubMatches(2)), cInt(oMT.SubMatches(1)), CInt(oMT.SubMatches(0)))
If dtFound >= dtFrom And dtFound <= dtTo Then
ReDim Preserve aTmp(Ubound(aTmp) + 1)
aTmp(Ubound(aTmp)) = dtFound
End If
Next
getDatesFrom = aTmp
End Function
Function qq(s) : qq = """" & s & """" : End Function
输出：
cscript 21994835-2.vbs
##### peter
----- "In 21/02/2014 something happened"
found (m/d/yyyy!) 2/21/2014
***** 1 0 1
##### paul
----- "pi 19/02/2014 pa 26/02/2014 po"
no interesting dates found.
----- "In 21/02/2013 something happened"
no interesting dates found.
***** 0 2 2
##### mary
----- "pi 19/02/2014 pu 20/02/2014 25/02/2014 26/02/2014 po"
found (m/d/yyyy!) 2/20/2014, 2/25/2014
----- "pi 19/02/2014 pu 20/02/2014 "
found (m/d/yyyy!) 2/20/2014
***** 2 0 2
##### 3 2 5
