SQL Query ... How Do I ...

Visitors can check out the Forum FAQ by clicking this link. You have to register before you can post: click the REGISTER link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. View our Forum Privacy Policy.
Want to receive the latest contracting news and advice straight to your inbox? Sign up to the ContractorUK newsletter here. Every sign up will also be entered into a draw to WIN £100 Amazon vouchers!

You are not logged in or you do not have permission to access this page. This could be due to one of several reasons:

You are not logged in. If you are already registered, fill in the form below to log in, or follow the "Sign Up" link to register a new account.
You may not have sufficient privileges to access this page. Are you trying to edit someone else's post, access administrative features or some other privileged system?
If you are trying to post, the administrator may have disabled your account, or it may be awaiting activation.

Guest replied

3 April 2005, 12:05
Cant be bothered to write it out in full, but look up SUM and DISTINCT and maybe GROUP BY
Leave a comment:
Guest started a topic SQL Query ... How Do I ...

1 April 2005, 17:56
SQL Query ... How Do I ...

Below are two tables, one contains the interest rate on a given day.
The columns Settle and Account provide a unique key for the Rate.
The second table Item contains no unique key.

RATES
Settle&nbsp &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp Account&nbsp &nbsp &nbsp &nbsp Rate
01/06/1998&nbsp &nbsp &nbsp &nbsp Pipe_A&nbsp &nbsp &nbsp &nbsp 5
02/06/1998&nbsp &nbsp &nbsp &nbsp Pipe_A&nbsp &nbsp &nbsp &nbsp 6
03/06/1998&nbsp &nbsp &nbsp &nbsp Pipe_A&nbsp &nbsp &nbsp &nbsp 7
04/06/1998&nbsp &nbsp &nbsp &nbsp Pipe_A&nbsp &nbsp &nbsp &nbsp 8
01/06/1998&nbsp &nbsp &nbsp &nbsp Pipe_B&nbsp &nbsp &nbsp &nbsp 1
02/06/1998&nbsp &nbsp &nbsp &nbsp Pipe_B&nbsp &nbsp &nbsp &nbsp 2
03/06/1998&nbsp &nbsp &nbsp &nbsp Pipe_B&nbsp &nbsp &nbsp &nbsp 3
04/06/1998&nbsp &nbsp &nbsp &nbsp Pipe_B&nbsp &nbsp &nbsp &nbsp 4

ITEMS
Today&nbsp &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp Account&nbsp &nbsp &nbsp &nbsp Amount
01/06/1998&nbsp &nbsp &nbsp &nbsp Pipe_A&nbsp &nbsp &nbsp &nbsp 1
03/06/1998&nbsp &nbsp &nbsp &nbsp Pipe_A&nbsp &nbsp &nbsp &nbsp 3
01/06/1998&nbsp &nbsp &nbsp &nbsp Pipe_B&nbsp &nbsp &nbsp &nbsp 10
03/06/1998&nbsp &nbsp &nbsp &nbsp Pipe_B&nbsp &nbsp &nbsp &nbsp 11
03/06/1998&nbsp &nbsp &nbsp &nbsp Pipe_B&nbsp &nbsp &nbsp &nbsp 11
05/06/1998&nbsp &nbsp &nbsp &nbsp Pipe_B&nbsp &nbsp &nbsp &nbsp -2

Below is the result of the SQL that I would like
Note Total_Amount is a cumulative to date balance
So [03/06/1998&nbsp &nbsp &nbsp &nbsp Pipe_A&nbsp &nbsp &nbsp &nbsp 4] is 1+3
Note there is no Rate for 05/06/1998

JOIN
Today&nbsp &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp Account&nbsp &nbsp &nbsp &nbsp Total_Amount&nbsp &nbsp &nbsp &nbsp Rate
01/06/1998&nbsp &nbsp &nbsp &nbsp Pipe_A&nbsp &nbsp &nbsp &nbsp 1&nbsp &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp 5
03/06/1998&nbsp &nbsp &nbsp &nbsp Pipe_A&nbsp &nbsp &nbsp &nbsp 4&nbsp &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp 7
01/06/1998&nbsp &nbsp &nbsp &nbsp Pipe_B&nbsp &nbsp &nbsp &nbsp 10&nbsp &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp 1
03/06/1998&nbsp &nbsp &nbsp &nbsp Pipe_B&nbsp &nbsp &nbsp &nbsp 22&nbsp &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp 3
05/06/1998&nbsp &nbsp &nbsp &nbsp Pipe_B&nbsp &nbsp &nbsp &nbsp 20&nbsp &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp 4

Anybody know who to do this ?
Cheers.
Tags: None