troubleshooting Question

SQL Query Help

Avatar of pzozulka
pzozulka asked on
Microsoft SQL Server
21 Comments1 Solution201 ViewsLast Modified:
I am a little puzzled by the results of the query below. When I look up the invoice number in our application, it correctly shows the following results:

InvNo         Amount   TransactionDate
393848       $256.07       10/15/2012
393848       $374.32       2/13/2012
393848       $381.00       3/12/2012
393848       $381.00       4/16/2012
393848       $381.00       5/14/2012
393848       $381.00       6/11/2012
393848       $381.00       7/17/2012
393848       $381.00       8/13/2012
393848       $381.00       9/17/2012


Since the transaction date is of no importance to me, I tried running a query without TransDate in the SELECT clause, and am receiving these results:

393848       $256.07
393848       $374.32
393848       $381.00

However, when I do include the TransDate, it shows everything correctly, just like the application shows (all 9 records).

How do I get it to show me the same thing the application is showing without using the TransDate?

Select  DISTINCT I.InvNo AS 'InvNo', PD.Credit AS 'Credit', P.TransDate as 'TransDate'
From Invoices I 
LEFT OUTER JOIN Jobs J					 ON J.JobNo = I.JobNo
LEFT OUTER JOIN Earnings E				 ON E.InvNo = I.InvNo
LEFT OUTER JOIN PaymentsDetail PD		 ON PD.InvNo = I.InvNo
LEFT OUTER JOIN Payments P				 ON P.PmtNo = PD.PmtNo
LEFT OUTER JOIN JobsLoc JL				 ON JL.JobNo = J.JobNo
LEFT OUTER JOIN Firms F					 ON JL.FirmNo = F.FirmNo
LEFT OUTER JOIN Locations L				 ON L.LocNo = JL.LocNo
LEFT OUTER JOIN Resources R				 ON R.RsrcNo = E.RsrcNo
Where datepart(year, P.TransDate) = '2012'
AND I.VoidDate is NULL
AND I.InvNo = 393848
AND
  LEFT(CASE
  WHEN JL.SameLocOrdering = 1						 THEN F.PostCode
  WHEN JL.SameLocOrdering = 0 AND JL.LocLinkType = 0 THEN JL.LocPostCode
  WHEN JL.SameLocOrdering = 0 AND JL.LocLinkType = 1 THEN F.PostCode
  WHEN JL.SameLocOrdering = 0 AND JL.LocLinkType = 2 THEN L.PostCode
  END,5) IN (
'90001',
'90002',
'90003',
'90004',
'90005',
'90005',
'90006',
'90007',
'90008',
'90010',
'90011',
'90012',
'90013',
'90014',
'90015',
'90016',
'90017',
'90018',
'90019',
'90020',
'90021',
'90023',
'90024',
'90025',
'90026',
'90027',
'90028',
'90029',
'90031',
'90032',
'90033',
'90034',
'90035',
'90036',
'90037',
'90038',
'90039',
'90041',
'90042',
'90043',
'90044',
'90045',
'90046',
'90047',
'90048',
'90049',
'90056',
'90057',
'90058',
'90059',
'90061',
'90062',
'90063',
'90064',
'90065',
'90066',
'90067',
'90068',
'90069',
'90071',
'90077',
'90089',
'90094',
'90095',
'90210',
'90211',
'90212',
'90230',
'90232',
'90245',
'90247',
'90248',
'90272',
'90290',
'90291',
'90292',
'90293',
'90302',
'90402',
'90501',
'90502',
'90710',
'90717',
'90731',
'90732',
'90732',
'90744',
'90810',
'91040',
'91042',
'91214',
'91303',
'91304',
'91306',
'91307',
'91311',
'91316',
'91324',
'91325',
'91326',
'91330',
'91331',
'91335',
'91340',
'91342',
'91343',
'91344',
'91345',
'91352',
'91356',
'91364',
'91367',
'91401',
'91402',
'91403',
'91405',
'91406',
'91411',
'91423',
'91436',
'91504',
'91505',
'91601',
'91602',
'91604',
'91605',
'91606',
'91607',
'91608')
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 21 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 21 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros