28,391
社区成员
发帖
与我相关
我的任务
分享各位大哥来帮忙，查询结果问题
为什么能查询08年按月的数据，但是07年按月就查出来数据显示都是0，但是07年每个月按天又能查，怎么回事啊？各位高手看下是不是代码哪里出了问题。代码如下：
<body>
<!--#include file="head.asp"-->
<Div id="pos">当前所在位置:业务统计 >> 即时查询</Div>
<%
Dim starttime,endtime,Dbtype
starttime = Request.Form("stime")
endtime = Request.Form("etime")
Dbtype = Request.Form("type")
If starttime="" Then starttime=Year(Date())&"-"&Month(Date())&"-1"
If endtime="" Then endtime = Date()
If Dbtype ="" Then Dbtype = "d"
%>
<form name="form1" method="post" action="">
<div id="dvdate">
日期选择:<input name="stime" id="today" onfocus="setday(this)" value="<%=starttime%>" readonly="true">至<input name="etime" id="today" onfocus="setday(this)" value="<%=endtime%>" readonly="true"> <input name="type" type="radio" value="d" <%If Dbtype="d" then Response.Write("checked")%> />
按日 <input type="radio" name="type" value="m" <%If Dbtype="m" then Response.Write("checked")%>/>按月
<input type="submit" name="Submit" value="查 询" id="btn"/>
</div></form>
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr bgcolor="#E8E8E8" align="center">
<td width="18%">时 间</td>
<td width="12%">收入</td>
<td width="12%">时长(分)</td>
<td width="12%">上线人数</td>
<td width="12%">拨打次数</td>
<td width="12%">平均时长(秒)</td>
<td width="12%">二次拨打率</td>
</tr>
<%
DateSub = DateDiff("d",starttime,endtime)
Set Conn = OpenConn()
If Dbtype="d" Then
If DateSub<=31 Then
Dim j
For j = 0 To DateSub
ForDate = DateAdd("d",j,starttime)
sql = "exec Zone_Lin '"&ForDate&"','"&ForDate&"'"
Set rs = Conn.Execute(sql)
If rs("amount")=rs("mass") and rs("mass")<>0 Then
percent = "100%"
ElseIf rs("mass")=0 Then
percent = "0%"
Else
percent = Round((rs("amount")-rs("mass"))/rs("mass"),4)*100&"%"
End If
times = Round((clng(rs("connlen"))))
html = "<tr>"
html = html&"<td>"&ForDate&"</td>"
html = html&"<td>"×*2&"</td>"
html = html&"<td>"×&"</td>"
html = html&"<td>"&rs("mass")&"</td>"
html = html&"<td>"&rs("amount")&"</td>"
html = html&"<td>"&rs("avetime")&"</td>"
html = html&"<td>"&percent&"</td>"
html = html&"</tr>"
AllTime = AllTime+times
sxrs = sxrs + rs("mass")
bdcs = bdcs + rs("amount")
Response.Write(html)
Next
Call CloseConn()
Response.Write("<tr><td>总计</td><td>"&AllTime*2&"元</td><td>"&AllTime&"</td><td>"&sxrs&"</td><td>"&bdcs&"</td><td> </td><td> </td></tr>")
Else
Call CloseConn()
Call Alert("时间跨度请不要超过一个月")
End If
Else
If Year(starttime)=Year(Date()) Then
MonthSub = Month(endtime)
For k = 1 To MonthSub
sday = Year(Date())&"-"&k&"-1"
eday = Year(Date())&"-"&k&"-"&LastDay(sday)
sql = "exec Zone_Lin '"&sday&"','"&eday&"'"
Set rs = Conn.Execute(sql)
If rs("amount")=rs("mass") and rs("mass")<>0 Then
percent = "100%"
ElseIf rs("mass")=0 Then
percent = "0%"
Else
percent = Round((rs("amount")-rs("mass"))/rs("mass"),4)*100&"%"
End If
times = Round((clng(rs("connlen"))))
html = "<tr>"
html = html&"<td>"&k&"月份</td>"
html = html&"<td>"×*2&"</td>"
html = html&"<td>"×&"</td>"
html = html&"<td>"&rs("mass")&"</td>"
html = html&"<td>"&rs("amount")&"</td>"
html = html&"<td>"&rs("avetime")&"</td>"
html = html&"<td>"&percent&"</td>"
html = html&"</tr>"
AllTime = AllTime+times
sxrs = sxrs + rs("mass")
bdcs = bdcs + rs("amount")
Response.Write(html)
Next
Else
For k = 1 To 12
sday = Year(stime)&"-"&k&"-1"
eday = Year(stime)&"-"&k&"-"&LastDay(sday)
sql = "exec Zone_Lin '"&sday&"','"&eday&"'"
Set rs = Conn.Execute(sql)
If rs("amount")=rs("mass") and rs("mass")<>0 Then
percent = "100%"
ElseIf rs("mass")=0 Then
percent = "0%"
Else
percent = Round((rs("amount")-rs("mass"))/rs("mass"),4)*100&"%"
End If
times = Round((clng(rs("connlen"))))
html = "<tr>"
html = html&"<td>"&k&"月份</td>"
html = html&"<td>"×*2&"</td>"
html = html&"<td>"×&"</td>"
html = html&"<td>"&rs("mass")&"</td>"
html = html&"<td>"&rs("amount")&"</td>"
html = html&"<td>"&rs("avetime")&"</td>"
html = html&"<td>"&percent&"</td>"
html = html&"</tr>"
AllTime = AllTime+times
sxrs = sxrs + rs("mass")
bdcs = bdcs + rs("amount")
Response.Write(html)
Next
End If
Call CloseConn()
Response.Write("<tr><td>总计</td><td>"&AllTime*2&"元</td><td>"&AllTime&"</td><td>"&sxrs&"</td><td>"&bdcs&"</td><td> </td><td> </td></tr>")
End If
Function LastDay(chkday)
Dim IntDay,i
IntDay = 28
For I = 29 To 31
If IsDate(Year(chkday)&"-"&Month(chkday)&"-"&I) Then
IntDay=I
End If
Next
LastDay = IntDay
End Function
%>
</table>
</body>
...全文
请发表友善的回复…
发表回复
beyondamane 2008-02-15
- 打赏
- 举报
昨天好象解决了一个同样的问题,把下面的2行修改一下
sday = Year(stime)&"-"&k&"-1"
eday = Year(stime)&"-"&k&"-"&LastDay(sday)
改成
sday = Year(starttime)&"-"&k&"-1"
eday = Year(starttime)&"-"&k&"-"&LastDay(sday)
sday = Year(stime)&"-"&k&"-1"
eday = Year(stime)&"-"&k&"-"&LastDay(sday)
改成
sday = Year(starttime)&"-"&k&"-1"
eday = Year(starttime)&"-"&k&"-"&LastDay(sday)
Go 旅城通票 2008-02-14
- 打赏
- 举报
一堆代码,看都不想看了
在没个
sql = "exec Zone_Lin '"&sday&"','"&eday&"'"
下加一个输出语句,看这个语句对不
在没个
sql = "exec Zone_Lin '"&sday&"','"&eday&"'"
下加一个输出语句,看这个语句对不
sql = "exec Zone_Lin '"&sday&"','"&eday&"'"
response.write sql&"<br/>"'=================
yan9299 2008-02-14
- 打赏
- 举报
难道就没人知道吗？
我晕啊。同志们帮忙啊！
我晕啊。同志们帮忙啊！
yan9299 2008-02-14
- 打赏
- 举报
大哥们来帮忙啊，急啊！！！
yan9299 2008-02-14
- 打赏
- 举报
存储过程如下：CREATE PROCEDURE Zone_Lin (@time as datetime,@etime as datetime) AS
select
count(caller) as amount,count(distinct(caller)) as mass,isnull(sum((connlen-7)/60 +1),0) as connlen,cast(isnull(sum((connlen-7))/count(caller),0) as int)as avetime
from (select * from taga_talkin_record where starttime between @time+'00:00:00' and @etime+'23:59:59' and caller<>'10157818820' and callee<>'13216145522')want
GO
select
count(caller) as amount,count(distinct(caller)) as mass,isnull(sum((connlen-7)/60 +1),0) as connlen,cast(isnull(sum((connlen-7))/count(caller),0) as int)as avetime
from (select * from taga_talkin_record where starttime between @time+'00:00:00' and @etime+'23:59:59' and caller<>'10157818820' and callee<>'13216145522')want
GO
tantaiyizu 2008-02-14
- 打赏
- 举报
把最终的 sql 语句打印出来看看
