'for payment type
sCmd = "Select Sum(B.PaymentAmount) As SPaymentAmount, Sum(B.Changes) As SChange, "
sCmd = sCmd & " B.PaymentType From Trans A INNER JOIN TransPayment B ON A.TransID = B.TransID "
sCmd = sCmd & " Where B.BusinessDate " & sDtCr & " And "
sCmd = sCmd & " A.TransStatus In (2, 3, 4) "
sCmd = sCmd & " Group By B.PaymentType "
sCmd = sCmd & " Order By B.PaymentType"
Set oRs = oCmn.ExecRS(sCmd, SDCN)
Do While Not oRs.EOF
cTotalSales = cTotalSales + IIf(IsNull(oRs!SPaymentAmount), 0, oRs!SPaymentAmount)
If UCase(Trim(oRs!PaymentType)) = "CASH" Then
cCashInDrawer = cCashInDrawer + IIf(IsNull(oRs!SPaymentAmount), 0, oRs!SPaymentAmount)
End If
sPrnLn = Space(9) & PADR(Trim(oRs!PaymentType), 10) & ": " & PADR(oCurrency.FormatCurrency(IIf(IsNull(oRs!SPaymentAmount), 0, oRs!SPaymentAmount), , , True), 10)
AddContents sPrnLn
'P_PrintText sPrnLn, oPrn
oRs.MoveNext
Loop'for deposit payment type
sCmd = "Select Sum(B.PaymentAmount) As SPaymentAmount, Sum(B.Changes) As SChange, "
sCmd = sCmd & " B.PaymentType From TableBookTrans A INNER JOIN TableBookPayment B ON A.MenuID = B.MenuID "
sCmd = sCmd & " Where B.BusinessDate " & sDtCr & " And "
sCmd = sCmd & " A.Status=0 "
sCmd = sCmd & " Group By B.PaymentType "
sCmd = sCmd & " Order By B.PaymentType"
Set oRs = oCmn.ExecRS(sCmd, SDCN)
Do While Not oRs.EOF
dTotalSales = dTotalSales + IIf(IsNull(oRs!SPaymentAmount), 0, oRs!SPaymentAmount)
If UCase(Trim(oRs!PaymentType)) = "CASH" Then
dCashInDrawer = dCashInDrawer + IIf(IsNull(oRs!SPaymentAmount), 0, oRs!SPaymentAmount)
End If
sPrnLn = Space(9) & PADR(Trim(oRs!PaymentType), 10) & ": " & PADR(oCurrency.FormatCurrency(IIf(IsNull(oRs!SPaymentAmount), 0, oRs!SPaymentAmount), , , True), 10)
AddContents sPrnLn
oRs.MoveNext
Loop
第一个结果集:
Cash:200
Visa:500
Master:600第二个结果集:
Nets:1000
Cash:900
..怎么统计出所有的付款方式:cash:1100
nets:1000
master:600
visa:500
....
sCmd = "Select Sum(B.PaymentAmount) As SPaymentAmount, Sum(B.Changes) As SChange, "
sCmd = sCmd & " B.PaymentType From Trans A INNER JOIN TransPayment B ON A.TransID = B.TransID "
sCmd = sCmd & " Where B.BusinessDate " & sDtCr & " And "
sCmd = sCmd & " A.TransStatus In (2, 3, 4) "
sCmd = sCmd & " Group By B.PaymentType "
sCmd = sCmd & " Order By B.PaymentType"
Set oRs = oCmn.ExecRS(sCmd, SDCN)
Do While Not oRs.EOF
cTotalSales = cTotalSales + IIf(IsNull(oRs!SPaymentAmount), 0, oRs!SPaymentAmount)
If UCase(Trim(oRs!PaymentType)) = "CASH" Then
cCashInDrawer = cCashInDrawer + IIf(IsNull(oRs!SPaymentAmount), 0, oRs!SPaymentAmount)
End If
sPrnLn = Space(9) & PADR(Trim(oRs!PaymentType), 10) & ": " & PADR(oCurrency.FormatCurrency(IIf(IsNull(oRs!SPaymentAmount), 0, oRs!SPaymentAmount), , , True), 10)
AddContents sPrnLn
'P_PrintText sPrnLn, oPrn
oRs.MoveNext
Loop'for deposit payment type
sCmd = "Select Sum(B.PaymentAmount) As SPaymentAmount, Sum(B.Changes) As SChange, "
sCmd = sCmd & " B.PaymentType From TableBookTrans A INNER JOIN TableBookPayment B ON A.MenuID = B.MenuID "
sCmd = sCmd & " Where B.BusinessDate " & sDtCr & " And "
sCmd = sCmd & " A.Status=0 "
sCmd = sCmd & " Group By B.PaymentType "
sCmd = sCmd & " Order By B.PaymentType"
Set oRs = oCmn.ExecRS(sCmd, SDCN)
Do While Not oRs.EOF
dTotalSales = dTotalSales + IIf(IsNull(oRs!SPaymentAmount), 0, oRs!SPaymentAmount)
If UCase(Trim(oRs!PaymentType)) = "CASH" Then
dCashInDrawer = dCashInDrawer + IIf(IsNull(oRs!SPaymentAmount), 0, oRs!SPaymentAmount)
End If
sPrnLn = Space(9) & PADR(Trim(oRs!PaymentType), 10) & ": " & PADR(oCurrency.FormatCurrency(IIf(IsNull(oRs!SPaymentAmount), 0, oRs!SPaymentAmount), , , True), 10)
AddContents sPrnLn
oRs.MoveNext
Loop
第一个结果集:
Cash:200
Visa:500
Master:600第二个结果集:
Nets:1000
Cash:900
..怎么统计出所有的付款方式:cash:1100
nets:1000
master:600
visa:500
....
sCmd = "Select Sum(B.PaymentAmount) As SPaymentAmount, Sum(B.Changes) As SChange, "
sCmd = sCmd & " B.PaymentType as PType From Trans A INNER JOIN TransPayment B ON A.TransID = B.TransID "
sCmd = sCmd & " Where B.BusinessDate " & sDtCr & " And "
sCmd = sCmd & " A.TransStatus In (2, 3, 4) "
sCmd = sCmd & " Group By B.PaymentType "
sCmd = sCmd & " union all "
sCmd = "(Select Sum(B.PaymentAmount) As SPaymentAmount, Sum(B.Changes) As SChange, "
sCmd = sCmd & " B.PaymentType as PType From TableBookTrans A INNER JOIN TableBookPayment B ON A.MenuID = B.MenuID "
sCmd = sCmd & " Where B.BusinessDate " & sDtCr & " And "
sCmd = sCmd & " A.Status=0 "
sCmd = sCmd & " Group By B.PaymentType) "
sCmd = "select * from (" & sCmd & ") A "
sCmd = sCmd & " Order By PType"
输出都没有纪录
sCmd = "select sum(SPaymentAmount),sum(SChange),PType from (" & sCmd & ") A "
sCmd = sCmd & "group by PType Order By PType"debug.print sCmd 拼出这个值放到数据库中查询,看看咯部分数据是否正常.
sCmd = "Select Sum(B.PaymentAmount) As SPaymentAmount, Sum(B.Changes) As SChange, "
sCmd = sCmd & " B.PaymentType as PType From Trans A INNER JOIN TransPayment B ON A.TransID = B.TransID "
sCmd = sCmd & " Where B.BusinessDate " & sDtCr & " And "
sCmd = sCmd & " A.TransStatus In (2, 3, 4) "
sCmd = sCmd & " Group By B.PaymentType "
sCmd = sCmd & " union all "
sCmd = "(Select Sum(C.PaymentAmount) As SPaymentAmount, Sum(C.Changes) As SChange, "
sCmd = sCmd & " C.PaymentType as PType From TableBookTrans D INNER JOIN TableBookPayment C ON D.MenuID = C.MenuID "
sCmd = sCmd & " Where C.BusinessDate " & sDtCr & " And "
sCmd = sCmd & " D.Status=0 "
sCmd = sCmd & " Group By C.PaymentType) "
sCmd = "select sum(SPaymentAmount),sum(SChange),PType from (" & sCmd & ") S "
sCmd = sCmd & " group by PType Order By PType "
select sum(SPaymentAmount),sum(SChange), PType from ((Select Sum(C.PaymentAmount) As SPaymentAmount, Sum(C.Changes) As SChange, C.PaymentType as PType From TableBookTrans D INNER JOIN TableBookPayment C ON D.MenuID = C.MenuID Where C.BusinessDate Between '2010-03-03' And '2010-03-03' And D.Status=0 Group By C.PaymentType) ) S group by PType Order By PType
sCmd = sCmd & "(Select Sum(C.PaymentAmount) As SPaymentAmount, Sum(C.Changes) As SChange, "
少了个scmd &
sCmd = sCmd & " B.PaymentType From "
sCmd = sCmd & "(Select Sum(B.PaymentAmount) As SPaymentAmount, Sum(B.Changes) As SChange, "
sCmd = sCmd & " B.PaymentType From Trans A INNER JOIN TransPayment B ON A.TransID = B.TransID "
sCmd = sCmd & " Where B.BusinessDate " & sDtCr & " And "
sCmd = sCmd & " A.TransStatus In (2, 3, 4) "
sCmd = sCmd & " Group By B.PaymentType "
sCmd = sCmd & " UNION ALL "
sCmd = sCmd & " Select Sum(B.PaymentAmount) As SPaymentAmount, Sum(B.Changes) As SChange, "
sCmd = sCmd & " B.PaymentType From TableBookTrans A INNER JOIN TableBookPayment B ON A.MenuID = B.MenuID "
sCmd = sCmd & " Where B.BusinessDate " & sDtCr & " And "
sCmd = sCmd & " A.Status=0 "
sCmd = sCmd & " Group By B.PaymentType "
sCmd = sCmd & ") T"
sCmd = sCmd & " Group By B.PaymentType "
sCmd = sCmd & " Order By B.PaymentType"