Brtdku

Question

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

INNER JOIN and WHERE will limit records return. I suspect the issue is in the AND CONVERT(VARCHAR(6), c.changedate, 112) part. It's complicated and is implementing some kind of time logic in the formatting. I bet it doesn't work as you expect. Try hard coding some values in the IN part for starters. — Jan 17 at 2:23
This is where the power of CTEs come in. Not for technical processing power, rather developer readability power. Create a CTE of the flat data (i.e. no GROUP BY), applying your CASE statements and extracting the DATEPARTs for MONTH and YEAR, aliasing your field names, etc. Then, when all that ugly work is done, select the aggregates from that CTE. — Jan 18 at 21:43

score 2 · Accepted Answer · 2019-01-23 17:28:52Z

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

I made changes to those areas that you pointed out, but still, I get no results. This is really stumping me... — Jan 17 at 2:16
Well it’s one of the areas I pointed out. There is no other place for filtering. Remove them all and add them back one by one until you find the culprit — Jan 17 at 2:23
@suffa i made an edit to my post. Read from EDIT downward and let me know if you have any questions. — 2 days ago

score 1 · Accepted Answer · 2019-01-18 20:28:04Z

1

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

edited Jan 18 at 20:28

answered Jan 18 at 20:22

Xabi

1365

I will try that again. When adding above, I may have copied and pasted erroneously when posting. But, I was careful to pay careful attention to that (formatting). I've changed it some umpteen times, trying everything to return data.

– suffa
Jan 18 at 22:28

Still did not return any data...

– suffa
Jan 18 at 22:42

Next attempt in further answer

– Xabi
Jan 19 at 14:34

add a comment |

Alexander VolokAlexander Volok 917513 · Accepted Answer · 2019-01-23 08:59:54Z

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

suffa 1,19153355 · Accepted Answer · 2019-01-21 20:43:53Z

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

it yields the same result that I'm getting: no data! This is strange; that convert block works as expected when run individually. — Jan 20 at 2:42

KumarHarshKumarHarsh 3,64411218 · Accepted Answer · 2019-01-22 05:29:26Z

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

Check for space (LTRIM and RTRIM) in columns which is use in predicate.

Avoid division by 0 in your case statement

If it is really,INNER JOIN then use EXISTS clause coz you don't require spitems sp columns .

What is ORDER BY 1 ? GroupLabel ? then you don't need them in Order clause coz all rows will be 'HR'+'43234'

ABOVE all,you don't need Order by at all,coz Group By will sort it for you and that is the only requirement.

Thoroughly check #tempperiod data,is the format same as

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

scsimon 22k51536 · Accepted Answer · 2019-01-23 19:36:51Z

0

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'

edited 2 days ago

scsimon

22k51536

answered Jan 19 at 0:13

donPablo

9661611

In the Schema, f.Quarter is defined as varchar(3), not null

– suffa
Jan 20 at 2:22

Could you try in the script to add the trailing blank SET @qrt = 'Q2 ';

– donPablo
Jan 20 at 2:28

Yes, yielded the same result...

– suffa
Jan 20 at 2:46

Thanks. I've also noticed that sometimes HR is all caps, and sometimes Hr is cap and lower. Is that correct? Also, what is the count of rows (and some sample data) for the inner Select... From FY ? When just that part is run separately.

– donPablo
Jan 20 at 5:53

Aha. AND CONVERT(VARCHAR(6), c.changedate, 112) IN gives YYYYMM. But the inner select delivers YYYY-MM with a hyphen. Ergo, not found in it.

– donPablo
Jan 20 at 6:03

|
show 5 more comments

score 2 · Accepted Answer · 2019-01-23 17:28:52Z

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

I made changes to those areas that you pointed out, but still, I get no results. This is really stumping me... — Jan 17 at 2:16
Well it’s one of the areas I pointed out. There is no other place for filtering. Remove them all and add them back one by one until you find the culprit — Jan 17 at 2:23
@suffa i made an edit to my post. Read from EDIT downward and let me know if you have any questions. — 2 days ago

score 1 · Accepted Answer · 2019-01-18 20:28:04Z

1

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

edited Jan 18 at 20:28

answered Jan 18 at 20:22

Xabi

1365

I will try that again. When adding above, I may have copied and pasted erroneously when posting. But, I was careful to pay careful attention to that (formatting). I've changed it some umpteen times, trying everything to return data.

– suffa
Jan 18 at 22:28

Still did not return any data...

– suffa
Jan 18 at 22:42

Next attempt in further answer

– Xabi
Jan 19 at 14:34

add a comment |

Alexander VolokAlexander Volok 917513 · Accepted Answer · 2019-01-23 08:59:54Z

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

suffa 1,19153355 · Accepted Answer · 2019-01-21 20:43:53Z

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

it yields the same result that I'm getting: no data! This is strange; that convert block works as expected when run individually. — Jan 20 at 2:42

KumarHarshKumarHarsh 3,64411218 · Accepted Answer · 2019-01-22 05:29:26Z

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

Check for space (LTRIM and RTRIM) in columns which is use in predicate.

Avoid division by 0 in your case statement

If it is really,INNER JOIN then use EXISTS clause coz you don't require spitems sp columns .

What is ORDER BY 1 ? GroupLabel ? then you don't need them in Order clause coz all rows will be 'HR'+'43234'

ABOVE all,you don't need Order by at all,coz Group By will sort it for you and that is the only requirement.

Thoroughly check #tempperiod data,is the format same as

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

scsimon 22k51536 · Accepted Answer · 2019-01-23 19:36:51Z

0

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'

edited 2 days ago

scsimon

22k51536

answered Jan 19 at 0:13

donPablo

9661611

In the Schema, f.Quarter is defined as varchar(3), not null

– suffa
Jan 20 at 2:22

Could you try in the script to add the trailing blank SET @qrt = 'Q2 ';

– donPablo
Jan 20 at 2:28

Yes, yielded the same result...

– suffa
Jan 20 at 2:46

Thanks. I've also noticed that sometimes HR is all caps, and sometimes Hr is cap and lower. Is that correct? Also, what is the count of rows (and some sample data) for the inner Select... From FY ? When just that part is run separately.

– donPablo
Jan 20 at 5:53

Aha. AND CONVERT(VARCHAR(6), c.changedate, 112) IN gives YYYYMM. But the inner select delivers YYYY-MM with a hyphen. Ergo, not found in it.

– donPablo
Jan 20 at 6:03

|
show 5 more comments

搜尋此網誌

Brtdku

What can I use in a SQL query to help me determine why my query is not returning any data results

6 Answers
6

Your Answer

Post as a guest

6 Answers
6

6 Answers
6

Post as a guest

Popular posts from this blog

Callistus III

Plistias Cous

Index Sanctorum

What can I use in a SQL query to help me determine why my query is not returning any data results

6 Answers 6

Your Answer

Sign up or log in

Post as a guest

Post as a guest

6 Answers 6

6 Answers 6

Sign up or log in

Post as a guest

Post as a guest

Sign up or log in

Post as a guest

Sign up or log in

Post as a guest

Sign up or log in

Post as a guest

Popular posts from this blog

Callistus III

Plistias Cous

Index Sanctorum

6 Answers
6

6 Answers
6

6 Answers
6