Outer Join not pulling all data when using where clause 2

[Scrye]

The query below works correctly if I do not add a where clause for date in the history table but once I add it records are skipped from the customer table which is set to a left outer join. My record custnums are sequential so I know that some are being skipped. I am trying to pull a customers balance forward even if there was not activity for a time period. Here is my query:

SELECT c.CUSTNUM AS CustNum, c.CUSTNAME AS CustName, c.BALFWD AS BalFwd, SUM(CASE WHEN h.transtype = '2' AND h.recordflag = 'B' THEN h.dollars ELSE 0 END) AS AdjTicketCash, SUM(CASE WHEN h.transtype = '1' AND h.recordflag = 'B' THEN h.dollars ELSE 0 END) AS AdjTicketChrg, SUM(CASE WHEN h.transtype = '1' AND h.recordflag IN ('A', 'B') THEN h.dollars ELSE 0 END) AS ChrgTicketTot, SUM(CASE WHEN h.recordflag = 'P' THEN (- 1 * h.dollars) ELSE 0 END) AS Payment, c.BALFWD+SUM(CASE WHEN h.transtype = '2' AND h.recordflag = 'B' THEN h.dollars ELSE 0 END)+ SUM(CASE WHEN h.transtype = '1' AND h.recordflag IN ('A', 'B') THEN h.dollars ELSE 0 END)+ SUM(CASE WHEN h.recordflag = 'P' THEN (- 1 * h.dollars) ELSE 0 END) as EndBalance 
FROM Customer AS c LEFT OUTER JOIN History AS h ON c.CUSTNUM = h.CUSTNUM 
WHERE h.DATEIN >= '2008-10-01' and h.DATEIN <= '2008-10-31' 
GROUP BY c.CUSTNUM, c.custname, c.BALFWD 
ORDER BY c.CustNum

Any help will be greatly appreciated.

Thanks in advance.

 

[RiverGuy]

Try this instead. The Where clause you have negates the "Outer" Join.

SELECT c.CUSTNUM AS CustNum, c.CUSTNAME AS CustName, c.BALFWD AS BalFwd, SUM(CASE WHEN h.transtype = '2' AND h.recordflag = 'B' THEN h.dollars ELSE 0 END) AS AdjTicketCash, SUM(CASE WHEN h.transtype = '1' AND h.recordflag = 'B' THEN h.dollars ELSE 0 END) AS AdjTicketChrg, SUM(CASE WHEN h.transtype = '1' AND h.recordflag IN ('A', 'B') THEN h.dollars ELSE 0 END) AS ChrgTicketTot, SUM(CASE WHEN h.recordflag = 'P' THEN (- 1 * h.dollars) ELSE 0 END) AS Payment, c.BALFWD+SUM(CASE WHEN h.transtype = '2' AND h.recordflag = 'B' THEN h.dollars ELSE 0 END)+ SUM(CASE WHEN h.transtype = '1' AND h.recordflag IN ('A', 'B') THEN h.dollars ELSE 0 END)+ SUM(CASE WHEN h.recordflag = 'P' THEN (- 1 * h.dollars) ELSE 0 END) as EndBalance
FROM Customer AS c LEFT OUTER JOIN History AS h [b]ON c.CUSTNUM = h.CUSTNUM
AND h.DATEIN >= '2008-10-01' and h.DATEIN <= '2008-10-31'[/b]
GROUP BY c.CUSTNUM, c.custname, c.BALFWD
ORDER BY c.CustNum

 

[Scrye]

Thank you! That was perfect.
I have not worked very long with SQL and was not sure why it wasn't pulling correctly.

 

[gmmastros]

Scrye,

Do you understand why the change RiverGuy suggested fixed your problem? It's important that you do.

Please, do yourself a favor and spend 20 minutes completely absorbing the explanation in this thread: thread183-1504081

You'll be glad you did.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Scrye]

George,

I searched for threads on left outer joins and did not see your thread but if I did I would not have needed to post.
Thanks for the link to the thread.
I understand completely what RiverGuy did and how it works, I just did not realize that you could continue a selection criteria within the FROM segment. Like I said I am fairly new to SQL.

Thanks!

 

[mary1994]

River guy can you please help me with this. I am experiencing the same issue as Scrye. This query was created by someone in the Microsoft Knowledgebase. But for some reasons the sample i posted pulls the correct amount of data but on production it is skipping some records. Here is the query:
SELECT T1.* INTO jump
FROM Table1 AS T1 INNER JOIN table2 ON T1.ID=table2.ID
WHERE (((T1.amt)<=(SELECT Max(Amt) FROM Table1 As T2 WHERE T2.ID = T1.ID AND (SELECT COUNT(*) FROM Table1 AS T3 WHERE T3.ID = T2.ID AND T3.Amt <= T2.Amt) <= table2.Audit)));


And here is the link that Ken help created this query.

http://www.microsoft.com/office/com...ffice-access&lang=en&cr=US&sloc=en-us&m=1&p=1

I am fine with even rewrite the code.
Thanks a million