SSRS:如果布尔表达式 1 或布尔表达式 2 不起作用，则为 SQL

Question

>我正在处理一个 SSRS 项目，我有 4 个参数，它们是@DateFrom，@DateTo，@VhGroup，@Agent。情况是，如果@Agent OR @VHGroup 等于"(空白("，则将执行 else 语句。但我似乎无法让它工作。这是 sql 语句

IF @VHGroup <> '(Blank)' OR @Agent <> '(Blank)'
    SELECT [Call ID of Abandoned]                                                                                               as CallIdAbandoned
    ,[InboundQueue]                                                                                                             as AbandonedCallQueue
    ,CONVERT(DATETIME2(0),[OriginalDateTime])                                                                                                           as CallArivalDateTime
    ,[OriginalEndDateTime]                                                                                                      as AbandonedDateTime
    ,[OriginalDuration]                                                                                                         as AbandonedDuration
    ,[CallIDVH]                                                                                                                                             as AgentGroup
    ,CASE WHEN [Outcome] = 'Handled Note : Call Could not be connected' THEN 'Subs Unavailable' ELSE [OutCome] END              as Outcome
    ,[TalkTime]                                                                                                                 as OutBoundDuration
     FROM [AgentStatisticsEOD].[dbo].[VH_Data]
     WHERE CONVERT(Date,[SCBCallDateTimeRequest]) BETWEEN @DateFrom and @DateTo 
     AND [AgentGroup]  collate database_default in (SELECT value from dbo.FnSplit(@VHGroup,','))
     AND [CallOutAgentName] collate database_default in (SELECT value from dbo.FnSplit(@Agent,','))
ELSE
    SELECT [Call ID of Abandoned]                                                                                               as CallIdAbandoned
    ,[InboundQueue]                                                                                                             as AbandonedCallQueue
    ,CONVERT(DATETIME2(0),[OriginalDateTime])                                                                                                           as CallArivalDateTime
    ,[OriginalEndDateTime]                                                                                                      as AbandonedDateTime
    ,[OriginalDuration]                                                                                                         as AbandonedDuration
    ,[CallIDVH]                                                                                                                                                 as AgentGroup
    ,CASE WHEN [Outcome] = 'Handled Note : Call Could not be connected' THEN 'Subs Unavailable' ELSE [OutCome] END              as Outcome
    ,[TalkTime]                                                                                                                 as OutBoundDuration
     FROM [AgentStatisticsEOD].[dbo].[VH_Data]
     WHERE CONVERT(Date,[SCBCallDateTimeRequest]) BETWEEN @DateFrom and @DateTo
     AND [AgentGroup]  collate database_default in (SELECT value from dbo.FnSplit(@VHGroup,','))
     OR [CallOutAgentName] collate database_default in (SELECT value from dbo.FnSplit(@Agent,','))

Answer 1

我不确定你在这里的确切逻辑，但这种方法需要较少重复的代码：

在此查询中，如果任一参数为空，则根本不会对其进行筛选。

只要绝对确定参数值确实(Blank)(不是''或NULL(

    SELECT [Call ID of Abandoned]                                                                                               as CallIdAbandoned
    ,[InboundQueue]                                                                                                             as AbandonedCallQueue
    ,CONVERT(DATETIME2(0),[OriginalDateTime])                                                                                                           as CallArivalDateTime
    ,[OriginalEndDateTime]                                                                                                      as AbandonedDateTime
    ,[OriginalDuration]                                                                                                         as AbandonedDuration
    ,[CallIDVH]                                                                                                                                             as AgentGroup
    ,CASE WHEN [Outcome] = 'Handled Note : Call Could not be connected' THEN 'Subs Unavailable' ELSE [OutCome] END              as Outcome
    ,[TalkTime]                                                                                                                 as OutBoundDuration
     FROM [AgentStatisticsEOD].[dbo].[VH_Data]
     WHERE CONVERT(Date,[SCBCallDateTimeRequest]) BETWEEN @DateFrom and @DateTo 
     AND 
     (
        @VHGroup = '(Blank)' 
        OR 
        [AgentGroup]  collate database_default in (
            SELECT value from dbo.FnSplit(@VHGroup,',')
        )
     )
     AND
     (
        @Agent = '(Blank)'
        OR
        [CallOutAgentName] collate database_default in (
           SELECT value from dbo.FnSplit(@Agent,',')
        )
     )

Answer 2

你的逻辑是错误的，IF语句应该像这样使用AND：

IF @VHGroup <> '(Blank)' AND @Agent <> '(Blank)'
    --snip
ELSE
    --snip