我是多个客户端使用,所以新建表删除这样的情况好像不能实现为何我改成这样也不行'创建临时表 '打开记录集 sql="select * from L_Accmain_js" Rst.Open Sql, Conn, 1, 1 if not Rst.Eof then Do While Not Rst.EOF tmpSql = "select top 1 a.Pan,b.AccName,a.OpenDate,a.Incml,a.CustMgr,a.OrgMgr into #tmpAccmain from L_Accmain_js as a,L_Accmain as b where a.pan=b.pan and b.Pan='99999999'" Conn.Execute tmpSql '插入临时表 Sql = "insert into #tmpAccmain values('" & Rst!Pan & "','" & Rst!AccName & "','" & Rst!OpenDate & "'," & sJs & ",'" & Rst!CustMgr & "','" & Rst!OrgMgr & "')" Conn.Execute Sql
Rst.MoveNext Loop End If Rst.Close Set Rst = Nothing
select case when 登记日期 > 年份 + case when 季度=1 then '1' when 季度=2 then '4' when 季度=3 then '7' when 季度=4 then '10' end + '1' then 积数/(datediff(d,年份+季度+'1',年份+cast((cast(季度 as int)+1) as varchar(10))+'1')-1) else 积数/(datediff(d,登记日期,年份+cast((cast(季度 as int)+1) as varchar(10))+'1')-1) end as 平均余额 from tname这样一个SQL语句就可以了
'打开记录集
sql="select * from L_Accmain_js"
Rst.Open Sql, Conn, 1, 1
if not Rst.Eof then
Do While Not Rst.EOF
tmpSql = "select top 1 a.Pan,b.AccName,a.OpenDate,a.Incml,a.CustMgr,a.OrgMgr into #tmpAccmain from L_Accmain_js as a,L_Accmain as b where a.pan=b.pan and b.Pan='99999999'"
Conn.Execute tmpSql
'插入临时表
Sql = "insert into #tmpAccmain values('" & Rst!Pan & "','" & Rst!AccName & "','" & Rst!OpenDate & "'," & sJs & ",'" & Rst!CustMgr & "','" & Rst!OrgMgr & "')"
Conn.Execute Sql
Rst.MoveNext
Loop
End If
Rst.Close
Set Rst = Nothing
第二个程序也是一样的,临时表只在那一个execute里面有效,下一个execute的时候已经被清掉了。
如果登记日期大于季度的开始日期,平均余额=积数/(季度结束日期-登记日期)的天数
case
when 登记日期 > 年份 +
case
when 季度=1 then '1'
when 季度=2 then '4'
when 季度=3 then '7'
when 季度=4 then '10'
end + '1'
then 积数/(datediff(d,年份+季度+'1',年份+cast((cast(季度 as int)+1) as varchar(10))+'1')-1)
else
积数/(datediff(d,登记日期,年份+cast((cast(季度 as int)+1) as varchar(10))+'1')-1)
end as 平均余额
from tname这样一个SQL语句就可以了