以文本方式查看主题

-  Foxtable(狐表)  (http://foxtable.net/bbs/index.asp)
--  专家坐堂  (http://foxtable.net/bbs/list.asp?boardid=2)
----  case when 咨询  (http://foxtable.net/bbs/dispbbs.asp?boardid=2&id=173372)
--  作者：guosheng
--  发布时间：2021/11/26 13:19:00
--  case when 咨询
如何将一个组合条件filter作为case when的条件？
1、生成一个filter条件
Dim dt1 As New Date(2021, 4, 1)
Dim dt2 As New Date(2021, 1, 1)
Dim dt3 As New Date(2021, 6, 30)
Dim dt4 As New Date(2020, 10, 1)
Dim dt5 As New Date(2020, 12, 31)
Dim filter1 As String="(合同开始日期 < \'" & dt1 & "\'  And 应结转日期1 >= \'" & dt2 & "\' And 应结转日期1 <= \'" & dt3 & "\')" 
Dim filter2 As String="(合同开始日期 >= \'" & dt4 & "\' and 合同开始日期 <= \'" & dt5 & "\'  And 应结转日期1 >= \'" & dt4 & "\' And 应结转日期1 <= \'" & dt5 & "\')" 
Dim s3 As String="短租"
Dim filter3 As String="租赁类别 Like \'%" & s3 & "%\'"
Dim filter As String="(" & filter1 & " Or " & filter2 & ")" & " And " & filter3

2、将filter 或“filter的取反”  作为case when的条件
b3.Totals.AddExp("已结转不含税租金","Case When filter或filter取反 Then 应结转租金金额  Else 0 End","已结转_不含税租金" )
--  作者：有点蓝
--  发布时间：2021/11/26 13:36:00
--  
b3.Totals.AddExp("已结转不含税租金","Case When " & filter  & " Then 应结转租金金额  Else 0 End","已结转_不含税租金" )