February 16, 2006 at 8:11 am
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
February 16, 2006 at 8:17 am
If you look at the 2nd $200 one it has a date where the 1st one has a NULL field.....
Good Hunting!
AJ Ahrens
webmaster@kritter.net
February 16, 2006 at 8:24 am
The unique identifyer in payhistory is number. So the 2 $200 has 1186 for m.number...it should only be showing 1 result, not 2..it's duplicating it. Same for customer 000002 with the dups of -478.00
February 16, 2006 at 8:27 am
hold on, you may have something..which date field?
February 16, 2006 at 8:29 am
Thanks so much! I put in '' for the onhold dates because I'm not using them in my where clause so they're not needed
February 16, 2006 at 9:00 am
crap, it hs nothing to do with the date, I am still getting dups
February 16, 2006 at 9:23 am
The date was your clue that it was the table containing the date that you should look at.
The date column in the resultset was pd.OnHold
If you are still joining to the table aliased as pd then the issue likely remains.
And since you know the pd.number value involved (1186), why not look at this join:
>>LEFT JOIN dbo.pdc pd ON pd.number = m.number
... and manually query both tables on number = 1186 to look at what's in your data ?
February 16, 2006 at 9:42 am
It appears that there are multiple rows in the pdc table for number = 1186.
Also, the need to have a DISTINCT clause on a SELECT statement is often a sign that the query is not optimal or all joins haven't been qualified enough to limit the result set sufficiently.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply