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 12Destinct will select distinct rows, and your rows are all distinct. Check
the last few columns in your results the date is null in one but not in the
other. If one column returns different results, then it will return a
seperate row. Duplicates, when using distinct, are rows where every column
has the same value.
"dba123" <d_schinkel@.hotmail.com> wrote in message
news:1140102940.858179.314400@.f14g2000cwb.googlegroups.com...
> 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
>|||It's impossible to answer your question without seeing the table DDL and
sample data.
ML
http://milambda.blogspot.com/