求助sql的计算编程， - SQL Server论坛

Jym5278

等　级：新手上路
帖　子：11
专家分：0
注　册：2014-3-20
结帖率：50%

楼主

已结贴√ 问题点数：20 回复次数：2

求助sql的计算编程，

本人自编了一个sql的数据库，目前有一个略微复杂的计算编程,想请有经验的专家，可付一定的费用,有意者可联系。

搜索更多相关主题的帖子: 数据库　

2014-03-20 19:22

owenlu1981

等　级：贵宾
威　望：13
帖　子：211
专家分：1130
注　册：2013-5-17

第 3 楼

得分:10

VB 代码如下，可自行转成SQL，逻辑上是一样的

程序代码：

Private Sub Command1_Click()
    Rs_1.CursorLocation = adUseClient
    Rs_2.CursorLocation = adUseClient
    SQL_1 = "Select * From 收款单 where [奖金已结] = False Order By [经营人员],[客户名称],[发生时间]"
    Rs_1.Open SQL_1, Conn, 1, 1
    If Not Rs_1.EOF Then
        Rs_1.MoveFirst
        Do While Not Rs_1.EOF
            SYK = Rs_1.Fields("收款金额")
            SQL_2 = "Select * from 发货单 where [经营人员]='" & Rs_1.Fields("经营人员") & "' and [客户名称]='" & Rs_1.Fields("客户名称") & "' and [发货金额] > 0 and ([已收金额] is Null or [发货金额] <> [已收金额]) Order By [发货时间]"
            Rs_2.Open SQL_2, Conn, 1, 1
            If Not Rs_2.EOF Then
                Rs_2.MoveFirst
                Do While Not Rs_2.EOF
                    If Rs_2.Fields("已收金额") & vbNullString = "" Then
                        If SYK >= Rs_2.Fields("发货金额") Then
                            JJ = IIf(DateDiff("d", Rs_2.Fields("发货时间"), Rs_1.Fields("发生时间")) > 240, 0, (240 - DateDiff("d", Rs_2.Fields("发货时间"), Rs_1.Fields("发生时间"))) * Rs_2.Fields("发货金额") * 0.008 / 240)
                            StrSQL = "Update 发货单 Set [已收金额] = " & Rs_2.Fields("发货金额") & ",[收款日期] = #" & Rs_1.Fields("发生时间") & "#,[奖金] = " & JJ & " where [经营人员]='" & Rs_2.Fields("经营人员") & "' and " & _
                                          "[客户名称]='" & Rs_2.Fields("客户名称") & "' and [凭证编号]='" & Rs_2.Fields("凭证编号") & "'"
                            Conn.Execute (StrSQL)
                            SYK = SYK - Rs_2.Fields("发货金额")
                        Else
                            JJ = IIf(DateDiff("d", Rs_2.Fields("发货时间"), Rs_1.Fields("发生时间")) > 240, 0, (240 - DateDiff("d", Rs_2.Fields("发货时间"), Rs_1.Fields("发生时间"))) * SYK * 0.008 / 240)
                            StrSQL = "Update 发货单 Set [已收金额] = " & SYK & ",[收款日期] = #" & Rs_1.Fields("发生时间") & "#,[奖金] = " & JJ & " where [经营人员]='" & Rs_2.Fields("经营人员") & "' and " & _
                                          "[客户名称]='" & Rs_2.Fields("客户名称") & "' and [凭证编号]='" & Rs_2.Fields("凭证编号") & "'"
                            Conn.Execute (StrSQL)
                            SYK = SYK - Rs_2.Fields("发货金额")
                        End If
                    Else
                        If SYK >= Rs_2.Fields("发货金额") - Rs_2.Fields("已收金额") Then
                            JJ = Rs_2.Fields("奖金") + IIf(DateDiff("d", Rs_2.Fields("发货时间"), Rs_1.Fields("发生时间")) > 240, 0, (240 - DateDiff("d", Rs_2.Fields("发货时间"), Rs_1.Fields("发生时间"))) * (Rs_2.Fields("发货金额") - Rs_2.Fields("已收金额")) * 0.008 / 240)
                            StrSQL = "Update 发货单 Set [已收金额] = " & Rs_2.Fields("发货金额") & ",[收款日期] = #" & Rs_1.Fields("发生时间") & "#,[奖金] = " & JJ & " where [经营人员]='" & Rs_2.Fields("经营人员") & "' and " & _
                                          "[客户名称]='" & Rs_2.Fields("客户名称") & "' and [凭证编号]='" & Rs_2.Fields("凭证编号") & "'"
                            Conn.Execute (StrSQL)
                            SYK = SYK - (Rs_2.Fields("发货金额") - Rs_2.Fields("已收金额"))
                        Else
                            JJ = Rs_2.Fields("奖金") + IIf(DateDiff("d", Rs_2.Fields("发货时间"), Rs_1.Fields("发生时间")) > 240, 0, (240 - DateDiff("d", Rs_2.Fields("发货时间"), Rs_1.Fields("发生时间"))) * SYK * 0.008 / 240)
                            StrSQL = "Update 发货单 Set [已收金额] = " & (Rs_2.Fields("已收金额") + SYK) & ",[收款日期] = #" & Rs_1.Fields("发生时间") & "#,[奖金] = " & JJ & " where [经营人员]='" & Rs_2.Fields("经营人员") & "' and " & _
                                          "[客户名称]='" & Rs_2.Fields("客户名称") & "' and [凭证编号]='" & Rs_2.Fields("凭证编号") & "'"
                            Conn.Execute (StrSQL)
                            SYK = SYK - (Rs_2.Fields("发货金额") - Rs_2.Fields("已收金额"))
                        End If
                    End If
                    If SYK <= 0 Then
                        Conn.Execute ("Update 收款单 Set [奖金已结] = True where [经营人员]='" & Rs_1.Fields("经营人员") & "' and [客户名称]='" & Rs_1.Fields("客户名称") & "' and [发生时间]=#" & Rs_1.Fields("发生时间") & "#")
                        Exit Do
                    End If
                    Rs_2.MoveNext
                Loop
            End If
            Rs_2.Close
            Rs_1.MoveNext
        Loop
    End If
    Rs_1.Close
    Rs_1.Open "Select [经营人员],[客户名称],Sum([发货金额]) as [发货总金额],Sum([已收金额]) as [已收总金额],Sum([奖金]) as [总奖金] from 发货单 Group By [经营人员],[客户名称]", Conn, 1, 1
    Set DataGrid1.DataSource = Rs_1
    DataGrid1.Refresh
End Sub

2014-03-22 10:45