this is a real complicated one and i'm at a loss. I'm hoping I could get some help here. I have 4 tables(clients, claims, entries,currency). I'm trying to show a summary of the 3 tables on how much $$ was spend for a certain time period. Here's where it gets cray cray. I have to show my info based on all the clients, so:
SELECT clientID,code,cName FROM clients
this will list all the clients
1 aaa Alpha Corp.
2 bbb Beta LLC.
3 ccc Cappa Corp.
My 2nd table is connected to the clients table by the code (didn't use clientID for some stupid reason). The claims table has 5 vital pieces of information needed 1)rID (uniqueID) 2)Client (which is what links to the ClientsTable... client=code) 3) status (only need closed items) 4)dateOn 5)currency (id links to currency table to retrieve currency name).
My 3rd table is basically the children of the claims table. These are ENTRIES and entries make up a claim. The needed columns are 1) rID (associates entry to claim (parent)) 2)refundAmount 3)errorCode. Now here's where it gets crazy still.... I need to find out within the last, say 30 days, how much a company has made with differentiations in currency. Also, if it's a certain errorCode (there's 5 that go to one group, 12 that go to another group), its tallied up to a different total. So my end result looks like this:
Last 30 days Last 31 to 60
code cName Currency #claims/error1 #claims/error2 total #claims/error1...
AAA Alpha Corp USD 5/$200.00 9/$300.10 14/$500.10
AAA Alpha Corp YEN 3/3,000,000.00 2/998,000.00 5/3,998,000.00
So you see, there has to be a differentiation by currency cause yen could be bajillion, meanwhile USD is $1. I hope i made this clear and really hope one of you experts can help. This is blowing my mind! Thanks all!