SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Count function is counting wrong
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sherrireid
Yak Posting Veteran

USA
58 Posts

Posted - 07/25/2013 :  15:29:34  Show Profile  Reply with Quote
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

547 Posts

Posted - 07/25/2013 :  23:22:10  Show Profile  Reply with Quote
Do you have duplicate discrepant codes in you output?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/26/2013 :  00:06:01  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000