Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > Reporting Services
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Reporting Services SQL Server Reporting Services. Please specify which version.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Reporting Services section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old February 16th, 2006, 10:13 AM
Authorized User
 
Join Date: Feb 2006
Location: , , .
Posts: 16
Thanks: 0
Thanked 0 Times in 0 Posts
Default Query producing duplicate results

I swear I had this fixed but when I looked again this morning, I noticed that this query is producing dup records in the results. Something is not right with my query here:

SELECT DISTINCT m.customer,
          c.name,
          c.customer,
          (SELECT Top 1 fd.Fee1 FROM FeeScheduleDetails fd
                    where c.feeSchedule = fd.code)
          AS FeeSchedule,
          m.Branch,
          CASE WHEN ph.batchtype = 'PUR' OR ph.batchtype = 'PAR' OR ph.batchtype = 'PCR' OR ph.batchtype = 'DUR' OR ph.batchtype = 'DAR' Then
                      (-ph.totalpaid + ph.ForwardeeFee)
               WHEN ph.batchtype = 'PU' OR ph.batchtype = 'PC' OR ph.batchtype = 'PA' OR ph.batchtype = 'DC' OR ph.batchtype = 'DA' Then
                         (ph.totalpaid + ph.ForwardeeFee)
          END AS [Posted Amount],
          ph.systemmonth,
          ph.systemyear,
          ph.datepaid,
          ph.totalpaid,
          ph.batchtype,
          m.desk,
          0 AS [NewCC_Amount],
          0 AS [OldCC_Amount],
          0 AS [NewPDC_Amount],
          0 AS [OldPDC_Amount],
          'In-House' AS Type,
          1 AS Active,
          m.number,
          0 AS CC,
          0 AS PDC,
          m.original,
          CONVERT(money, ph.OverPaidAmt),
          0,
          0,
          '',
          0,
          0,
          dc.OnHoldDate,
          pd.OnHold,
          (SELECT TotalPostingDays from TotalPostingDays),
          (SELECT CurrentPostingDAy from CurrentPostingDay)

FROM dbo.Master m (NOLOCK)
INNER JOIN dbo.payhistory ph ON m.number = ph.number
LEFT JOIN dbo.DebtorCreditCards dc ON dc.number = m.number
LEFT JOIN dbo.pdc pd ON pd.number = m.number
INNER JOIN dbo.Customer c ON c.Customer = m.Customer
WHERE ph.systemmonth = datepart(mm, getdate()) AND ph.systemyear = datepart(yy, getdate())
               AND ph.batchtype <> 'DA'
               AND ph.batchtype <> 'DAR'
ORDER BY m.customer

Output:

Check out 00001, I am getting 2 $200, only should be getting one:

0000001 AD 0000001 25 00001 144.34 2 2006 2006-02-10 00:00:00.000 144.34 PU C0159 0 0 0 0 In-House 1 1259 0 0 5144.34 0.00 0 0 0 0 NULL NULL 20 12
0000001 AD 0000001 25 00001 200.00 2 2006 2006-02-06 00:00:00.000 200.00 PU C0101 0 0 0 0 In-House 1 1186 0 0 12067.88 0.00 0 0 0 0 NULL NULL 20 12
0000001 AD 0000001 25 00001 200.00 2 2006 2006-02-06 00:00:00.000 200.00 PU C0101 0 0 0 0 In-House 1 1186 0 0 12067.88 0.00 0 0 0 0 NULL 2005-04-05 00:00:00.000 20 12
0000002 MB 0000002 25 00001 -2500.00 2 2006 2006-01-31 00:00:00.000 2500.00 PUR C0137 0 0 0 0 In-House 1 713617 0 0 5131.47 0.00 0 0 0 0 NULL NULL 20 12
0000002 MB 0000002 25 00001 -1800.00 2 2006 2006-01-30 00:00:00.000 1800.00 PUR C0130 0 0 0 0 In-House 1 604096 0 0 2362.20 0.00 0 0 0 0 NULL NULL 20 12
0000002 MB 0000002 25 00001 -1000.00 2 2006 2006-01-31 00:00:00.000 1000.00 PUR C0136 0 0 0 0 In-House 1 572560 0 0 10651.37 0.00 0 0 0 0 NULL NULL 20 12
0000002 MB 0000002 25 00001 -565.00 2 2006 2006-01-30 00:00:00.000 565.00 PUR C0136 0 0 0 0 In-House 1 671991 0 0 19471.85 0.00 0 0 0 0 NULL NULL 20 12
0000002 MB 0000002 25 00001 -500.00 2 2006 2006-01-30 00:00:00.000 500.00 PUR C0137 0 0 0 0 In-House 1 685926 0 0 14825.85 0.00 0 0 0 0 NULL NULL 20 12
0000002 MB 0000002 25 00001 -478.00 2 2006 2006-01-31 00:00:00.000 478.00 PUR C0136 0 0 0 0 In-House 1 713497 0 0 4788.80 0.00 0 0 0 0 NULL NULL 20 12
0000002 MB 0000002 25 00001 -478.00 2 2006 2006-01-31 00:00:00.000 478.00 PUR C0136 0 0 0 0 In-House 1 713497 0 0 4788.80 0.00 0 0 0 0 NULL 2006-02-09 17:33:02.360 20 12
0000002 MB 0000002 25 00001 -411.94 2 2006 2006-01-19 00:00:00.000 411.94 PUR C0137 0 0 0 0 In-House 1 604705 0 0 9886.63 0.00 0 0 0 0 NULL NULL 20 12
0000002 MB 0000002 25 00001 -350.00 2 2006 2006-01-30 00:00:00.000 350.00 PUR C0137 0 0 0 0 In-House 1 558059 0 0 7040.92 0.00 0 0 0 0 NULL NULL 20 12
0000002 MB 0000002 25 00001 -328.61 2 2006 2006-02-09 00:00:00.000 328.61 PUR C0137 0 0 0 0 In-House 1 713542 0 0 6572.34 0.00 0 0 0 0 NULL NULL 20 12
0000002 MB 0000002 25 00001 -300.00 2 2006 2006-01-19 00:00:00.000 300.00 PUR C0136 0 0 0 0 In-House 1 662978 0 0 12041.96 0.00 0 0 0 0 NULL NULL 20 12
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Edit Query Results in Results Grid druid2112 SQL Server 2005 1 June 28th, 2007 08:49 AM
different results were given for the same query madhusrp SQL Server 2000 2 May 9th, 2006 01:54 AM
SQL query to get duplicate entries from 2 colums EGDDaley SQL Server 2000 1 January 28th, 2005 03:41 PM
Eliminate duplicate entries - sql query nlicata SQL Server 2000 2 September 17th, 2003 10:37 AM



All times are GMT -4. The time now is 06:29 AM.


Powered by vBulletin® Version 3.7.0
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.