Foxtable(狐表)用户栏目专家坐堂 → [求助]统计2个时间段前3个记录均价闪退


  共有2924人关注过本帖树形打印复制链接

主题:[求助]统计2个时间段前3个记录均价闪退

帅哥哟,离线,有人找我吗?
panyongming
  1楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级:婴狐 帖子:37 积分:653 威望:0 精华:0 注册:2012/5/20 21:47:00
[求助]统计2个时间段前3个记录均价闪退  发帖心情 Post By:2018/10/10 15:26:00 [只看该作者]

Dim y1 As WinForm.NumericComboBox = e.Form.Controls("年份1")
Dim y2 As WinForm.NumericComboBox = e.Form.Controls("年份2")
Dim m1 As WinForm.NumericComboBox = e.Form.Controls("开始月")
Dim m2 As WinForm.NumericComboBox = e.Form.Controls("结束月")

Dim dtks1 As Date = New Date(y1.Value,m1.Value,1)
Dim dtjs1 As Date = New Date(y1.Value,m2.Value,Date.DaysInMonth(y1.Value,m2.Value))
Dim dtks2 As Date = New Date(y2.Value,m1.Value,1)
Dim dtjs2 As Date = New Date(y2.Value,m2.Value,Date.DaysInMonth(y2.Value,m2.Value))


For Each drwl1 As DataRow In DataTables("bom物料合并").DataRows
    Dim q1 As new QueryBuilder
    q1.TableName = "采购fa piaotop2"
    q1.C
    
    q1.SelectString = "Sel ect TOP 3 FItemID,FPriceDiscount FR OM {ICPurchaseEntry} LEFT JOIN {ICPurchase} ON {ICPurchaseEntry}.FInterID = {ICPurchase}.FInterID WHERE FDate <= '" & dtjs2 & "' And FPriceDiscount > 0 And FItemID = " & drwl1("物料内码") & " ORDER BY {ICPurchaseEntry}.FInterID DESC"
    q1.Build
    If Tables("采购fa piaotop2").Rows.Count > 0 Then
        drwl1("均价2") = Tables("采购fa piaotop2").Compute("Avg(FPriceDiscount)")
    End If
    
Next
For Each drwl As DataRow In DataTables("bom物料合并").DataRows
    Dim q As new QueryBuilder
    q.TableName = "采购fa piaotop1"
    q.C
    
    q.SelectString = "Sel ect TOP 3 FItemID,FPriceDiscount FR OM {ICPurchaseEntry} LEFT JOIN {ICPurchase} ON {ICPurchaseEntry}.FInterID = {ICPurchase}.FInterID WHERE FDate <= '" & dtjs1 & "' And FPriceDiscount > 0 And FItemID = " & drwl("物料内码") & " ORDER BY {ICPurchaseEntry}.FInterID DESC"
    q.Build
    If Tables("采购fa piaotop1").Rows.Count > 0 Then
        drwl("均价1") = Tables("采购fa piaotop1").Compute("Avg(FPriceDiscount)")
    End If
Next
[此贴子已经被作者于2018/10/10 15:33:42编辑过]

 回到顶部
帅哥哟,离线,有人找我吗?
有点甜
  2楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级:版主 帖子:85326 积分:427815 威望:0 精华:5 注册:2012/10/18 22:13:00
  发帖心情 Post By:2018/10/10 15:40:00 [只看该作者]

改成这样计算

 

For Each drwl As DataRow In DataTables("bom物料合并").DataRows
    Dim q As new SQLCommand
    q.ConnectionName = "k3"
    q.CommandText = "Select TOP 3 FItemID,FPriceDiscount FROM {ICPurchaseEntry} LEFT JOIN {ICPurchase} ON {ICPurchaseEntry}.FInterID = {ICPurchase}.FInterID WHERE FDate <= '" & dtjs1 & "' And FPriceDiscount > 0 And FItemID = " & drwl("物料内码") & " ORDER BY {ICPurchaseEntry}.FInterID DESC"
    Dim dt = q.ExecuteReader
    If dt.dataRows.Count > 0 Then
        drwl("均价1") = dt.Compute("Avg(FPriceDiscount)")
    End If
Next


 回到顶部
帅哥哟,离线,有人找我吗?
panyongming
  3楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级:婴狐 帖子:37 积分:653 威望:0 精华:0 注册:2012/5/20 21:47:00
  发帖心情 Post By:2018/10/10 15:52:00 [只看该作者]

谢谢老师!成功了,计算效率也高很多

 回到顶部