why when I leave out the Amount fields, does my query stand correct at 15 records? When I add those 2 amouns (dc.amount and p.amount) back in it for some reason adds 3 more unwanted records and the count is then 18?
SELECT m.customer,
c.name,
c.customer,
c.state,
/*(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 [New Old CC],
0 AS [New Old PDC],
'In-House' AS Type,
1 AS Active,
ph.UID,
m.number,
dc.amount CC,
--p.amount AS PDC,
m.original,
CONVERT(money, ph.OverPaidAmt),
0,
0,
''
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 p ON p.number = m.number
LEFT JOIN dbo.Customer c ON c.Customer = m.Customer
GROUP BY m.customer,
c.name,
c.customer,
c.state,
c.FeeSchedule,
m.Branch,
ph.OverPaidAmt,
ph.systemmonth,
ph.systemyear,
ph.datepaid,
ph.totalpaid,
ph.batchtype,
m.desk,
ph.UID,
m.number,
--dc.amount,
--p.amount,
m.original ,
ph.systemmonth,
ph.systemyear,
ph.ForwardeeFee
HAVING ph.systemmonth = 12 AND ph.systemyear = 2005
AND m.customer = '0000002'AND m.branch = '00002'
ORDER BY m.customer, batchtype
If you have different values, then this could imply different groups and thus increase the number of rows returned from the query.
Conor
