Unanswered: How to show all the accounts even if they dont have records for this period.
I have three tables Accounts, History and Dates . What I need to do is display all the accounts from History (900) records and compare them to the accounts in Accounts table pull all the matching records based on a certain date range , but If there is no record in the History table for this period I still need to display the account from Accounts and some text saying that there is no record matching for this period.
4 NO information for this month
SELECT C.ACCOUNT, CASE WHEN C.ACCOUNT = LEFT(H.NUMBER,8)
THEN LEFT(H.NUMBER,8) END FROM ACCTS C
LEFT OUTER JOIN HISTORY H ON C.ACCOUNT = LEFT(H.NUMBER,8)
INNER JOIN DATES D ON h.PERIOD = D.CUR_PERIOD
GROUP BY C.ACCOUNT, H.NUMBER
This will give me all the matching records for the period but I need somehow to show all the accounts even if they don't have records for this period.
I am using case because the number in my history table is 13 characters but I need to match only the first 8 characters which is working fine. I can't figure out how to show all the accounts, because when I select by date range I don't have activities for all the records for this period but I still need to show all the accounts.
,COALESCE(LEFT(H.NUMBER,8),'No History for this Month')
FROM ACCTS C
INNER JOIN DATES D
ON h.PERIOD = D.CUR_PERIOD
LEFT OUTER JOIN HISTORY H
ON C.ACCOUNT = LEFT(H.NUMBER,8)
Last edited by Enigma; 08-05-13 at 14:15.
Reason: correcting query
Thanks Enigma, but this is still pulling only the records for the period which my query is doing. What is happening is that I have lets say 900 records in my Accounts table and in my History table I might have data for only 250 records for that particular month. I need to show all the 900 accounts even if there is no data for them for this month.