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
|
