different results weird i feel

Question

Hi I have a below query

with vcr as (

select b.CustNo,isnull(c.AMLRating,1) aml ,e.OrgType,d.ProfCd,
b.AcctType,
case
when b.AcctType in (43,41,47,85,80,81,66,44,11) then 'AOP'
when b.AcctType in (67,18,55,13,21,88,46,14,69,11,20,52,90,42,61,58,74,83,5,37,76,22,15,25,35,91,19,56) then 'Others'
when b.AcctType in (32,84,60,24,12) then 'Corporate'
when b.AcctType in (50,30,4,70,87,82) then 'Trust'
when b.AcctType in (8) then 'HUF'
when b.AcctType in (53) then 'NPO'
when b.AcctType in (71) then 'PEP'
WHEN b.AcctType IN (42,62,68,59,56,53,73,72,81,41,9,1,3,80,11,10,2,52,55,63,51,87) THEN 'IND'
end r1,
a.DrCr,a.FcyTrnAmt,x.CodeDesc org,y.CodeDesc profession,z.CodeDesc actype
from D009040 a inner join D009022 b on a.LBrCode=b.LBrCode and substring(a.MainAcctId,1,24)=substring(b.PrdAcctId,1,24)
left join D009012 c on b.CustNo=c.CustNo
left join D009013 e on b.CustNo=e.CustNo
left join D009012 d on b.CustNo=d.CustNo
left join D001002 x on e.OrgType=x.Code and x.CodeType=1162
left join D001002 y on d.ProfCd=y.Code and y.CodeType=1062
left join D001002 z on b.AcctType=z.Code and z.CodeType=1067
where a.CanceledFlag<>'C' and a.EntryDate between '17-sep-2018' AND '29-apr-2019'
--and b.CustNo=864838
and b.AcctType=8 and c.AMLRating=1
--and a.DrCr in ('D','C')
--order by a.DrCr

--and a.DrCr='D'

)

select r1,
count(distinct case when aml=1 then CustNo else 0 end) low_count,
count(distinct case when (aml=1 and DrCr='D') then CustNo else 0 end) low_debit_count

from vcr group by r1

if I run select * from vcr
I get 6 records
but when I run the above query I get 7 records
why ??
Please help

Answer 1

Hi,@Hemant Karnik
Below are some speculations I made based on your issue description：
1）Please check whether the ‘CustNo’ columns in each table you join are the same data type.
2）How about trying this and check if you still get the same result after query

SUM(case when aml=1 then 1 else 0 end) low_count,  
SUM(case when (aml=1 and DrCr='D') then 1 else 0 end) low_debit_count  

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

We don't see your tables, we don't see your data, so we cannot give any direct answer. I would assume that there is something indeterministic in the query, although I don't see something directly.

But what you can do is to dig in more to the data. If the straight SELECT returns six rows and the GROUP BY query returns seven rows, this means that there is at least one value of R1 that does not appear in the simple query. And rather than grouping by R1, you can group on the source column for R1, to wit AcctType, so see exactly which AcctType that comes out of the blue. And then you can dig backwards to see where it may come from, and whether it actually qualifies for the query.

Also, share the output of SELECT @@version.

Answer 3

By the way, here are two things that looks suspicious:

left join D009012 c on b.CustNo=c.CustNo
left join D009013 e on b.CustNo=e.CustNo
left join D009012 d on b.CustNo=d.CustNo

Why are you joining to the same table twice? Is d actually meant to be yet another table? Or are you having one LEFT JOIN too many?

In the WHERE clause:

and b.AcctType=8 and c.AMLRating=1

You have a left join to D009013, as if you want to retain rows from D009040 a inner join D009022 b and have NULL in the SELECT list for the column(s) from c when there is no match. However, this condition in the WHERE clause effectively transforms the join to D009012 to an inner join. Maybe it should be:

and b.AcctType=8 and isnull(c.AMLRating, 1) = 1

And again, keep in mind that I don't anything about your table and data; I'm only making observations of things that you may want to check further.

Answer 4

@@version is Microsoft SQL Server 2017 (RTM-GDR) (KB4583456) - 14.0.2037.2 (X64) Nov 2 2020 19:19:59 Copyright (C) 2017 Microsoft Corporation Developer Edition (64-bit) on Windows 10 Pro 10.0 <X64> (Build 19042: )

This is the release version of SQL 2017. You should download and install the most recent Cumulative Update: https://www.microsoft.com/en-us/download/details.aspx?id=56128. I don't think it is likely, but you could be victim to a bug that has been fixed. In any case, there is zero reason to run with the RTM version.

Please tell me how should I send you data so you can test the same at your end

We have a standard recommendation that when people want help with a query, they should provide CREATE TABLE statements for their tables, INSERT statements with sample data and the desired result given the sample data. We also need a short description of the business rules so that we understand why you want that particular result.

The tacit assumption is that the amount of data is fairly small, at most 20-30 rows. And the problem cannot really require us to learn a whole lot about the business. Please keep in mind that people who answer questions do so in our free time, so you cannot expect someone to spend hours on writing a query for you.

I think in your case, you should first work with your CTE to make sure that it produces the correct result. Once you have that going, you can start working with the final GROUP BY query. Also, since the GROUP BY only works on the single data set from the CTE, it would be a lot easier for us to help you with that part, since you only have to post CREATE TABLE + INSERT for the sample data.