SQL Server - where clause condition being ignored

2019-09-14 04:41发布

问题:

For some reason the data condition in the where clause is being ignored - any suggestions?

SELECT  D.[Date],
        D.Year,
        D.MonthName,
        D.WeekOfYear,
        E.bu_name,
        E.emp_mgr,
        E.emp_sup,
        E.emp_name,
        E.emp_jde,
        C.calls,
        s.sales
FROM    DateTable AS D
        CROSS JOIN EmployeeTable AS E
        LEFT JOIN (SELECT   CAST([start_date] AS DATE) AS call_date,
                            [agent_no] AS agent_id,
                            SUM(CASE WHEN [skill_name] LIKE '5700 Sales l%'
                                          AND [Agent_Time] != '0' THEN 1
                                     ELSE 0
                                END) AS calls
                   FROM     CallTable
                   GROUP BY CAST([start_date] AS DATE),
                            [agent_no]
                  ) AS C ON D.[Date] = C.call_date
                            AND E.emp_vcc = C.agent_id
        LEFT JOIN (SELECT   [AC#DTE_dt] AS sale_date,
                            [EMPJDENUM] AS emp_jde,
                            SUM(CASE WHEN [CHANNEL] = 'I'
                                          AND ([ICGCD2] = 'L'
                                               OR [ICGCD2] = 'H')
                                          AND [ITMQTY] > 3
                                          AND [EMPBUNCOD] IN ('5044','5077') THEN 1
                                     ELSE 0
                                END) AS sales
                   FROM     SalesTable
                   GROUP BY [AC#DTE_dt],
                            [EMPJDENUM]
                  ) AS S ON D.Date = s.sale_date
                            AND E.emp_jde = S.emp_jde
WHERE   c.calls > 0
        OR S.sales > 0
        AND d.Year = '2016'

回答1:

This is not really an answer but an example of how you could format this query so you can read it more easily. I also modified your where predicates to accommodate what is almost certainly the issue.

SELECT D.[Date]
    , D.Year
    , D.MonthName
    , D.WeekOfYear
    , E.bu_name
    , E.emp_mgr
    , E.emp_sup
    , E.emp_name
    , E.emp_jde
    , C.calls
    , s.sales
FROM [Date Table] AS D
CROSS JOIN [Employee TABLE] AS E
LEFT JOIN 
(
    SELECT Cast([start_date] AS DATE) AS call_date
        , [agent_no] AS agent_id
        , Sum(CASE WHEN [skill_name] LIKE '5700 Sales l%' AND [Agent_Time] != '0' THEN 1 ELSE 0 END) AS calls
    FROM [Call TABLE]
    GROUP BY Cast([start_date] AS DATE), [agent_no]
) AS C ON D.[Date] = C.call_date
        AND E.emp_vcc = C.agent_id
LEFT JOIN 
(
    SELECT [AC#DTE_dt] AS sale_date
        , [EMPJDENUM] AS emp_jde
        , Sum(CASE WHEN [CHANNEL] = 'I' AND [ICGCD2] IN ('L', 'H') AND [ITMQTY] > 3 AND [EMPBUNCOD] IN ( '5044', '5077' ) THEN 1 ELSE 0 END) AS sales
    FROM [Sales TABLE]
    GROUP BY [AC#DTE_dt], [EMPJDENUM]
) AS S ON D.Date = s.sale_date
    AND E.emp_jde = S.emp_jde
WHERE 
(
    c.calls > 0
    OR 
    S.sales > 0
)
AND d.Year = '2016';