sql-server - 我可以在 SQL 查询中使用什么来帮助我确定查询未返回任何数据结果的原因

Question

有人可以帮助我对我的 SQL 查询进行故障排除，以发现为什么它没有返回任何结果，只返回列别名？

我已经把它分开了，显然组合在一起的所有部分都单独返回了预期的数据。感谢您提前提供任何指导/帮助。下面是我的脚本：

...

DECLARE @u_cnt INT;
DECLARE @f_yr DATE;
DECLARE @qrt VARCHAR(3);
DECLARE @dnum VARCHAR(5);


SET @u_cnt = 10000;
SET @f_yr = '2002-05-20';
SET @qrt = 'Q2';
SET @dnum = '43234';




SELECT c.GroupLabel AS ORG_Code,
   CONVERT (VARCHAR(7), FORMAT((CONVERT(DATE, c.changedate)), 'MM-yyyy')) AS [MONTH],
   COUNT(DISTINCT CASE s.TestType 
         WHEN 'IR' THEN c.changedate
              ELSE NULL END) AS TEST_DAYS,
   COUNT(DISTINCT c.changedate) AS ALLDAYS,
   COUNT(s.Id) AS total,
   (CASE WHEN (@u_cnt IS NULL) THEN -1 
         ELSE @u_cnt
         END) AS board_cnt,
   FORMAT((COUNT(s.Id) / CASE
                                WHEN (@u_cnt IS NULL) THEN -1 
                                ELSE @u_cnt
                                END), 'P0') AS pct_tested_text,

   CASE WHEN 100 * (COUNT(s.Id) / CASE 
                                   WHEN (@u_cnt IS NULL) THEN -1 
                                   ELSE @u_cnt 
                                   END) >= 15 
               AND (COUNT(DISTINCT CASE s.TestType
                          WHEN 'IR' THEN c.changedate
                          ELSE NULL END)) >= 4  
   THEN 'Yes'
   ELSE 'NO' END 
FROM cforms c 
INNER JOIN spitems sp
ON c.Id = s.FormId
WHERE c.Group = 'HR'
AND c.bFlag IS NULL
AND s.Report IN ('P', 'N')
AND CONVERT(VARCHAR(6), c.changedate, 112) IN
       (SELECT
            CASE 
                WHEN f.Quarter = 'Q1' THEN CONVERT(VARCHAR(4), YEAR(@f_yr) - 1) + f.FyMonthNumber
                WHEN f.Quarter = 'ALL' AND f.FyMonth IN ('OCT', 'NOV', 'DEC') THEN CONVERT(VARCHAR(4), YEAR(@f_yr) - 1, 112) + f.FyM
                ELSE CONVERT(VARCHAR(4), YEAR(@f_yr), 112) + f.FyM 
            END AS FY_MONTH
        FROM fis f
        WHERE f.Quarter = @qrt)
AND c.GroupLabel = 'Hr' + @dnum
GROUP BY c.GroupLabel, FORMAT((CONVERT(DATE, c.changedate)), 'MM-yyyy')
ORDER BY 1, FORMAT((CONVERT(DATE, c.changedate)), 'MM-yyyy');

标签： sql-serversql-server-2016ssms-2016

Answer 1

可能限制您的数据的所有内容都在下面代码的这一部分中。我把它拆开，并添加了关于它们为什么以及在哪里受到限制的评论。我认为你CONVERT是罪魁祸首。

--this inner join will limit the rows to only those with matching Id and FormId
INNER JOIN spitems sp
ON c.Id = s.FormId

--of the rows already filtered via the JOIN, they are further limited to thous with the Group = 'HR', a NULL bFlag, and Report = to P or N
WHERE c.Group = 'HR'
AND c.bFlag IS NULL
AND s.Report IN ('P', 'N')


--The first convert here changed changedate to yyyymmd (notice the day). 
--In the sub-query, you seem to only be returning yyyymm formatted with a -, 
--thus this would return ZERO rows. varchar(6) could resolve this, 
--by dropping the day, but you'd need to add the hyphen or remove it from the `IN` clause
AND CONVERT(VARCHAR(7), c.changedate, 112) IN
       (SELECT
            CASE 
                WHEN f.Quarter = 'Q1' THEN CONVERT(VARCHAR(4), YEAR(@f_yr) - 1) + '-' + f.FyMonthNumber
                WHEN f.Quarter = 'ALL' AND f.FyMonth IN ('OCT', 'NOV', 'DEC') THEN CONVERT(VARCHAR(4), YEAR(@f_yr) - 1, 112) + '-' + f.FyMonthNumber
                ELSE CONVERT(VARCHAR(4), YEAR(@f_yr), 112) + '-' + f.FyMonthNumber 
            END AS FY_MONTH
        FROM FyQm f
        WHERE f.Quarter = @qrt)

--Lastly, there may be a case sensitivity here Hr vs HR or there just simply aren't any rows that match this predicate
AND c.GroupLabel = 'Hr' + @dnum

编辑

详细说明我上面的答案......你已经改变了你的 where 子句的一部分。特别是您正在评估c.changedate值列表的部分。您已更改为：

AND CONVERT(VARCHAR(6), c.changedate, 112) IN ...

这是部分修复。它将删除您之前拥有的尾随 DAY 值，留下YYYYMM. 但是，在您的子查询中，您将值列表格式化为YYYYMM-?? 不管f.FyMonthNumber是什么。如您所见，这将永远不会与您的原始convert语句匹配，因为它没有连字符。首先要更改的是从字符串连接中删除连字符。在您编辑的帖子中，您已经做得很好了。接下来，问题可能是+当您尝试将其与f.FyMonthNumber. 如果f.FyMonthNumber是，int那么它将添加它。

DECLARE @f_yr DATE;
SET @f_yr = '2002-05-20';

SELECT CONVERT(VARCHAR(4), YEAR(@f_yr) - 1) + 02

在这里，您希望它返回 200102 但它返回 2003，因为它正在执行加法。您可以将其转换为varchar或char来解决此问题。

SELECT CONVERT(VARCHAR(4), YEAR(@f_yr) - 1) + cast('02' as varchar)

最后，您可能会遇到的一个问题是，如果f.FyMonthNumber存储为int，它将没有前导零。因此，对于 1 月，它将被表示为1而不是，01这也将在 10 月之前的任何月份返回零行。您可以使用该功能处理此问题right。

DECLARE @f_yr DATE;
SET @f_yr = '2002-05-20';

SELECT CONVERT(VARCHAR(4), YEAR(@f_yr) - 1) + right('0' + cast('1' as varchar(2)),2) --a month as a single digit
SELECT CONVERT(VARCHAR(4), YEAR(@f_yr) - 1) + right('0' + cast('12' as varchar(2)),2) --a month with double digits

综上所述，我怀疑此编辑会解决您的问题。不过我要注意，如果适用的话，您不会评估Q2、Q3 或 Q4 的任何 case 表达式......

DECLARE @u_cnt INT;
DECLARE @f_yr DATE;
DECLARE @qrt VARCHAR(3);
DECLARE @dnum VARCHAR(5);


SET @u_cnt = 10000;
SET @f_yr = '2002-05-20';
SET @qrt = 'Q2';
SET @dnum = '43234';




SELECT c.GroupLabel AS ORG_Code,
   CONVERT (VARCHAR(7), FORMAT((CONVERT(DATE, c.changedate)), 'MM-yyyy')) AS [MONTH],
   COUNT(DISTINCT CASE s.TestType 
         WHEN 'IR' THEN c.changedate
              ELSE NULL END) AS TEST_DAYS,
   COUNT(DISTINCT c.changedate) AS ALLDAYS,
   COUNT(s.Id) AS total,
   (CASE WHEN (@u_cnt IS NULL) THEN -1 
         ELSE @u_cnt
         END) AS board_cnt,
   FORMAT((COUNT(s.Id) / CASE
                                WHEN (@u_cnt IS NULL) THEN -1 
                                ELSE @u_cnt
                                END), 'P0') AS pct_tested_text,

   CASE WHEN 100 * (COUNT(s.Id) / CASE 
                                   WHEN (@u_cnt IS NULL) THEN -1 
                                   ELSE @u_cnt 
                                   END) >= 15 
               AND (COUNT(DISTINCT CASE s.TestType
                          WHEN 'IR' THEN c.changedate
                          ELSE NULL END)) >= 4  
   THEN 'Yes'
   ELSE 'NO' END 
FROM cforms c 
INNER JOIN spitems sp
ON c.Id = s.FormId
WHERE c.Group = 'HR'
AND c.bFlag IS NULL
AND s.Report IN ('P', 'N')
AND CONVERT(VARCHAR(6), c.changedate, 112) IN
       (SELECT
            CASE 
                WHEN f.Quarter = 'Q1' THEN CONVERT(VARCHAR(4), YEAR(@f_yr) - 1) + right('0' + cast(f.FyMonthNumber as varchar(2)))
                WHEN f.Quarter = 'ALL' AND f.FyMonth IN ('OCT', 'NOV', 'DEC') THEN CONVERT(VARCHAR(4), YEAR(@f_yr) - 1, 112) + right('0' + cast(f.FyMonthNumber as varchar(2)))
                ELSE CONVERT(VARCHAR(4), YEAR(@f_yr), 112) + right('0' + cast(f.FyMonthNumber as varchar(2)))
            END AS FY_MONTH
        FROM fis f
        WHERE f.Quarter = @qrt)
AND c.GroupLabel = 'Hr' + @dnum
GROUP BY c.GroupLabel, FORMAT((CONVERT(DATE, c.changedate)), 'MM-yyyy')
ORDER BY 1, FORMAT((CONVERT(DATE, c.changedate)), 'MM-yyyy');