----------------------------------------------------
code I DEBIT I CREDIT I
----------------------------------------------------
1 I 0.00 I 100 I
235 I 10.00 I 0.00 I
5 I 100.00 I 0.00 I
-----------------------------------------------------
ก็แสดง Code 5 100.00 และ 235 = 10.00 ( ทำได้แล้ว)
' sql ส่วนนี้ ทำงานปกติ'
sqlfind = " SELECT b1.[Comcode], b1.[Bitem], '' as [Bline], b1.[Bdate], '' as [Bvoucher], b1.[Bref], '' as [Bchqno], '' as [Bchqdate], b1.[Boff], b1.[Bcomtaxid], '' as [Ac_code], '' as [Ac_name], '' as [Scode], '' as [Sname], b2.[BDEBIT] AS [BCREDIT], b1.[BDEBIT], b1.[Bnote]"
sqlfind &= " FROM (SELECT [Comcode], [Bitem], [Bdate], [Bref], [Boff], [Bcomtaxid], SUM([BDEBIT]) AS BDEBIT, [Bnote] FROM BOOK WHERE AC_CODE = '235' AND COMCODE ='" & tmpComCode & "' AND BDEBIT <> 0 AND CAST(SUBSTRING(BDATE, 4, 2) AS NUMERIC) = " & selDateTo.Month & " GROUP BY [Comcode], [Bitem], [Bdate], [Bref], [Boff], [Bcomtaxid], [Bnote]) b1 "
sqlfind &= " LEFT JOIN (SELECT [Comcode], [Bitem], [Bdate], [Bref], [Boff], [Bcomtaxid], SUM([BDEBIT]) AS BDEBIT, [Bnote] FROM BOOK WHERE AC_CODE <> '235' AND COMCODE ='" & tmpComCode & "' AND BDEBIT <> 0 AND CAST(SUBSTRING(BDATE, 4, 2) AS NUMERIC) = " & selDateTo.Month & " GROUP BY [Comcode], [Bitem], [Bdate], [Bref], [Boff], [Bcomtaxid], [Bnote]) b2 on b1.bitem = b2.bitem and b1.comcode = b2.comcode and b1.bdate = b2.bdate and b1.bref = b2.bref"
sqlfind &= " WHERE b1.BDEBIT *100/7 between b2.BDEBIT - 1 and b2.BDEBIT + 1 "
'แต่พอใส่ code ด้านล่าง เพื่อเพิ่มเงื่อนไข UNION ดังกล่าว ปรากฏว่า แสดงผลไม่ตรงเงื่อนไข'
sqlfind &= " UNION "
sqlfind &= " SELECT b1.[Comcode], b1.[Bitem], b1.[Bline], b1.[Bdate], b1.[Bvoucher], b1.[Bref], b1.[Bchqno], b1.[Bchqdate], b1.[Boff], b1.[Bcomtaxid], b1.[Ac_code], b1.[Ac_name], b1.[Scode], b1.[Sname], SUM(b2.[BDEBIT]) AS [BCREDIT], b1.[BDEBIT], b1.[Bnote]"
sqlfind &= " FROM BOOK b1 "
sqlfind &= " left join book b2 on b1.bitem = b2.bitem and b1.[Bdate] = b2.[Bdate] and b1.[Bvoucher] = b2.[Bvoucher] and b1.[Bref] = b2.[Bref] and b1.comcode = b2.comcode AND SUBSTRING(b2.ac_code, 1, 1) = '5'"
sqlfind &= " WHERE b1.AC_CODE ='" & "235" & "'"
sqlfind &= " AND b1.BDEBIT = 0 AND b1.BCREDIT = 0 AND b2.BDEBIT is not null"
sqlfind &= " AND b1.COMCODE ='" & tmpComCode & "'"
sqlfind &= " AND CAST(SUBSTRING(b1.BDATE, 4, 2) AS NUMERIC) = " & selDateTo.Month
sqlfind &= " GROUP BY b1.[Comcode], b1.[Bitem], b1.[Bline], b1.[Bdate], b1.[Bvoucher], b1.[Bref], b1.[Bchqno], b1.[Bchqdate], b1.[Boff], b1.[Bcomtaxid], b1.[Ac_code], b1.[Ac_name], b1.[Scode], b1.[Sname], b1.[BDEBIT], b1.[Bnote] "
ขอบคุณค่ะ
Tag : .NET, Ms SQL Server 2008, Crystal Report, VB.NET, VS 2008 (.NET 3.x), Windows
Load balance : Server 00
