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
Can you provide some DB schema information, as it is hard to solve from that query alone. Usually you get duplicates because you are either missing one or more columns in your joins, or you have too many columns in your group by clause. Make sure all you joins use the Primary key column(s).
|||Did that help, or do you need more advice?
No comments:
Post a Comment