Count function is counting wrong

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Count function is counting wrong

Author

Topic

sherrireid
Yak Posting Veteran

58 Posts

Posted - 2013-07-25 : 15:29:34

Hi:

I have a query that is looking at invoices that are discrepant. It requires that I count the discrepant info by discrepant code and in an SSRS report display the code and the discrepant number. Then clicking on a code would show the detail of the invoices that are discrepant.

The problem is that when I count the discrepant codes it comes up with 11 for example, but when I look at the data there are 13 rows of data.

Does anyone have an idea on why I might have my counts off?

Here is my sql:

create table #DISCRInfo (
DISCRCount int,
DISCRCode varchar(20))

insert into #DISCRInfo
(DISCRCount, DISCRCode)

select distinct
COUNT(INV_LINE_DISCR.INV_LINE_DISCR_ID),
INV_LINE_DISCR.INV_DISCR_CD

from INV_LINE_DISCR
where INV_LINE_DISCR.REVIEW_DATE >= '07/01/2013' and
INV_LINE_DISCR.REVIEW_DATE <= '07/05/2013' and
INV_DISCR_RES_CD is not null

group by INV_LINE_DISCR.INV_DISCR_CD

order by INV_LINE_DISCR.INV_DISCR_CD

SELECT DISTINCT
CAST (#DISCRInfo.DISCRCount AS VARCHAR(10)) + '-' + INV_LINE_DISCR.INV_DISCR_CD AS DISCR_CODE,
CODE_TABLE.NAME AS CODE_DESCR,
min(INV_LINE_DISCR.REVIEW_DATE)AS DiscrepancyDate,
max(INV_LINE_DISCR.REVIEW_DATE)AS ResolveDate,
USR.NAME AS AP_TECH,
SUB_ACCT.NAME as BUDGET,
MFR.NAME AS SUPPLIER,
INV.PO_NO,
INV.INV_NO

FROM INV
JOIN INV_LINE
ON INV.INV_ID = INV_LINE.INV_ID

JOIN INV_LINE_DISCR
ON INV_LINE_DISCR.INV_LINE_ID = INV_LINE.INV_LINE_ID

JOIN PO
on INV.PO_NO = PO.PO_NO

JOIN USR
on USR.USR_ID = INV.APPR_USR_ID

JOIN CODE_TABLE
on CODE_TABLE.TYPE_CD = INV_LINE_DISCR.INV_DISCR_CD

JOIN SUB_ACCT
on SUB_ACCT.SUB_ACCT_ID = INV_LINE.SUB_ACCT_ID

Thanks for any suggestions.

SLReid
Forum Newbie
Renton, WA USA

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-07-25 : 23:22:10

Do you have duplicate discrepant codes in you output?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-26 : 00:06:01

you've a series of joins. Are you suer you'll have matching entries in all the tabled for those 13 records? JOIN will filter out unmatched entries which might be why you're missing two. Another possibility is availability of duplicates as per earlier suggestion

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources