|
Dim t As Table = e.Form.controls("Table1").Table
Dim d1 As Date = Date.today
Dim d2 As Date = new Date(Date.today.Year, Date.today.Month,1)
Dim sql1 As String = "Select a.学期,报名教师,Format(上课日期,""yyyy-MM"") As 月份,Count(学生编号) As 学生数,Sum(IIf(退费金额 Is Null,缴费金额,缴费金额-退费金额)) As 实收总数,Sum(IIf(退费金额 Is Null,缴费金额,缴费金额-退费金额)) * 教师奖励百分率/100 As 奖励金额 From {辅导班收费} a Inner JOIN {辅导教师工资} b ON a.学期 = b.学期 WHERE 上课日期 >= #" & d2.AddMonths(-1) & "# And 上课日期 <= #" & d2.AddDays(-1) & "# And (结束日期 Is Null Or 结束日期 > 上课日期) GROUP BY a.学期,报名教师,Format(上课日期,""yyyy-MM""),教师奖励百分率"
Dim sql2 As String = "Select a.学期,分管领导 As 报名教师,Format(上课日期,""yyyy-MM"") As 月份,Count(学生编号) As 学生数,Sum(IIf(退费金额 Is Null,缴费金额,缴费金额-退费金额)) As 实收总数,Sum(IIf(退费金额 Is Null,缴费金额,缴费金额-退费金额)) * 分管奖励百分率/100 As 奖励金额 From {辅导班收费} a Inner JOIN {辅导教师工资} b ON a.学期 = b.学期 WHERE 上课日期 >= #" & d2.AddMonths(-1) & "# And 上课日期 <= #" & d2.AddDays(-1) & "# And (结束日期 Is Null Or 结束日期 > 上课日期) GROUP BY a.学期,分管领导,Format(上课日期,""yyyy-MM""),分管奖励百分率"
t.Fill(sql1 & " union all " & sql2,"数据库",True)
|
加好友 
发短信
Post By：2017/10/13 10:50:00 [显示全部帖子]
