What can I use in a SQL query to help me determine

问题:

Can someone assist me in troubleshooting my SQL query to discover why it's not returning any results, only the column aliases?

I've broken it apart, and all sections that obviously group together returns the expected data individually. Thanks for any guidance/assistance in advance. Below is my script:

...

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');

回答1:

Everything that could be limiting your data is in this part of your code below. I broke it apart and added comments to why and where they are limited. I think your CONVERT is the culprit.

--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

EDIT

Elaborating on my answer above... you have changed a portion of your where clause. Specifically the portion where you are evaluating c.changedate to a list of values. You have made the change to :

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

This is a partial fix. It would remove the trailing DAY value you had before, leaving you with YYYYMM. However, in your subquery, you are formatting the list of values as YYYYMM-? where the ? is whatever f.FyMonthNumber is. As you can see, this will never match your original convert statement since it doesn't have a hyphen. The first thing to change would be remove the hyphen from the string concatenation. In your edited post, you have already done that so good job. Next, the issue could be that your + is not being treated as addition instead of concatenation when you are trying to combine it with f.FyMonthNumber. If f.FyMonthNumber is an int then it will add it.

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

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

Here you are wanting it to return 200102 but it returns 2003 since it's performing addition. You can cast it as a varchar or char to fix this.

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

Lastly, an issue you may run into is if f.FyMonthNumber is stored as an int, it won't have the leading zero. Thus, for January it would be represented as 1 instead of 01 and this would also return zero rows for any month before October. You can handle this with the right function.

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

Putting that all together, I would suspect this edit would fix your issue. I would note though, you aren't evaluating any case expressions for Q2, Q3, or Q4 if that would be applicable...

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');

回答2:

Try to change to this (look at the 1st convert):

...
AND CONVERT(VARCHAR(7), c.changedate, 120) 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)
...

You was converting 112 (yyyymm) instead of 120 (yyyy-mm) and your inner select returns yyyy-mm

回答3:

JOIN or/and WHERE clauses can be a reason. Following basic deduction method is to figure out which part of the query gives such result:

Firstly, eliminate all WHERE clauses and check if current JOIN can return rows by setting WHERE this way:

WHERE 1 = 1
--AND 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

Then, uncomment WHERE statetements one by one to figure out which one filters rows:

WHERE 1 = 1
AND 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

Then, another statement:

WHERE 1 = 1
AND 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

And so on, until you get into the point when no rows returned

This technique will bring you eventually to a part (parts) of the query which filters rows out

If original dataset returns too big number of rows it can be expensive to retrieve all of them during the debugging, so I would recomend to comment them out and use COUNT(*) instead:

SELECT COUNT(*)
/*
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 

回答4:

I've reformated your code with remarks:

declare @u_cnt int, @f_yr date, @qrt varchar(3), @dnum varchar(5);
select @u_cnt = 10000, @f_yr = '20020520', @qrt = 'Q2', @dnum = '43234';

select c.GroupLabel as ORG_CODE
      -- Assuming c.changedate is datetime, otherwise cast(c.changedate as datetime)
    , format(c.changedate, 'MM-yyyy') as [MONTH] 
    , count(distinct case s.TestType when 'IR' then c.changedate else null end) as IR_TEST_DAYS
    , count(distinct c.changedate) as TEST_DAYS
    , count(s.Id) as TOTAL
    , (case when (@u_cnt is null) then - 1 else @u_cnt end) as BOARD_CNT
    , format((count(s.Id) /
        -- avoiding also division by 0
        case when isnull(@u_cnt, 0) = 0
            then - 1
            else @u_cnt end), 'P0')
        as PCT
    , case
        when 100 * (count(s.Id) / 
            -- avoiding also division by 0
            case when isnull(@u_cnt, 0) = 0
                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 as PCT_TEST_COMP
from cforms c
join spitems s 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 -- yyyymm (ISO format)
        (
        select 
            cast(year(@f_yr) + 
                case
                    when f.Quarter = 'Q1'
                        then (-1)
                    when f.Quarter = 'ALL'
                        and f.FyMonthNumber in ('10', '11', '12')
                        then (-1)
                    else (0)
                end as varchar(4))
                + f.FyMonthNumber -- JAN = '01' or '1' ?
        from FyQm f
        where f.Quarter = @qrt
        )
    and c.GroupLabel = 'Hr' + @dnum
group by c.GroupLabel
      -- Assuming c.changedate is datetime, otherwise cast(c.changedate as datetime)
    , format(c.changedate, 'MM-yyyy')
order by ORG_CODE, [MONTH];

Could you check if FyQm.FyMonthNumber is varchar(2) or char(2) and represents January as '01' instead of '1'?

回答5:

Your main question is why you are not getting data for given query ? So you want to debug and check where the problem is.

So for given parameter,

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';

so start from basic

 select * 
    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')

note the comments part,does it return data, if yes then uncomment AND c.bFlag IS NULL and this way uncomments other part.

Are you sure it will be INNER JOIN or LEFt JOIN ?

Put the period subquery in temp table,though this is not main reason,if it return less records then you can use CTE also,

Create table #tempperiod(period varchar(6))
insert into #tempperiod(period)
select 
            cast(year(@f_yr) + 
                case
                    when f.Quarter = 'Q1'
                        then (-1)
                    when f.Quarter = 'ALL'
                        and f.FyMonthNumber in ('10', '11', '12')
                        then (-1)
                    else (0)
                end as varchar(4))
                + f.FyMonthNumber 
        from FyQm f
        where f.Quarter = @qrt

-- in order to test,does it return any records,does it return desire output
select * from #tempperiod

1. Check for space (LTRIM and RTRIM) in columns which is use in predicate.
2. Avoid division by 0 in your case statement
3. If it is really,INNER JOIN then use EXISTS clause coz you don't require spitems sp columns .
4. What is ORDER BY 1 ? GroupLabel ? then you don't need them in Order clause coz all rows will be 'HR'+'43234'
5. ABOVE all,you don't need Order by at all,coz Group By will sort it for you and that is the only requirement.

6. Thoroughly check #tempperiod data,is the format same as

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

回答6:

My bet is on different column definitions

DECLARE @qrt VARCHAR(3);

vs

whatever is FROM FyQm f   WHERE f.Quarter = @qrt

'Q2 ' with a blank or null probably does not equal  f.Quarter which may be defined as VARCHAR(2)

Perhaps we could try this sql to see that each criterion has some rows

Select
  Sum(1) as cntAll
 ,Sum (CASE When c.Group = 'HR' Then 1 Else 0 End) as cntGroup  
 ,Sum (CASE When c.bFlag IS NULL Then 1 Else 0 End) as cntbFlag
 ,Sum (CASE When s.Report IN ('P', 'N') Then 1 Else 0 End) as cntsReport
 ,Sum (CASE When CONVERT(VARCHAR(6), c.changedate, 112) 
    IN ('200204', '200205', '200206') Then 1 Else 0 End) as cntchangedate
 ,Sum (CASE When c.GroupLabel = 'Hr43234' Then 1 Else 0 End) as cntGroupLabel

FROM cforms c 
INNER JOIN spitems s
ON c.Id = s.FormId

Maybe time to follow @scsimon advice, and add back criteria one at a time and see which one blocks all of the rows

--  WHERE c.Group = 'HR'
--  AND c.bFlag IS NULL
--  AND s.Report IN ('P', 'N')
--  AND CONVERT(VARCHAR(6), c.changedate, 112) IN ('200204', '200205', '200206')
--  AND c.GroupLabel = 'Hr43234'