So, recently I obtained a horrid assignment from lé boss, and I have yet to figure out how to do this.
The idea is to select the ID(db_medlem) of all customers that have purchased for over 1000, in the year 2010. (I know its a funny syntax, but its all I have to work with.)
kunder = the table with all client information
faklin = table with all invoice lines
So I figured, first I'd search all customers through ( I've limited myself to 5 to make it fast to run while developing it). Anything inside of [founditems] and [/founditems] will run for those 5 results.
So in the second SQL, i chose the one customer that has an invoice date larger than 2010, and where the db_medlemsnr(clientIDnr) is the one found in the first SQL.
Again anything inside of [foundItems][/founditems] will run for any results of this sql
Finally, I select the combined price of all invoices sum(db_pris) as totalPrice where the clientId is a clientID that Ifound in the most outerloop. But this doesnt work.
[SQL dsn=[unurl][dsn][/unurl]&statement=select top 5 * from kunder] Found [NumFound] items<br> [FoundItems] [SQL dsn=[unurl][dsn][/unurl]&statement=select top 1 * from kunder where '2010' > (select max(LEFT(db_bestiltdato,4)) from faklin where db_medlemsnr = '[db_medlem]') and db_medlem ='[db_medlem]'] [foundItems] [SQL dsn=[unurl][dsn][/unurl]&statement=select sum(db_pris) as totalPrice from faklin where db_medlemsnr = [db_medlem]] [foundItems] Client nr: [db_medlem] purchased for more than : ([totalPrice])<br /> [/foundItems] [/sql] [/foundItems] [/sql] [/FoundItems] [/SQL]
Anyone have any ideas as to how I get this to work, I though about selecting all invoice lines first, but I require a date, and a clientID for that, which I wont have unless I run the outerloop first and select the clientIDs. In other words, Im completely stuck, aside from the grotesque syntax, anyone have any ideas?