Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
asderex
Starting Member
10 Posts |
 Posted - 2008-04-07 : 19:39:26
|
| Hi,I have two tables. One holds "Account" information and the second holds "Transaction" information. They are related by a common field AccountID. I am trying to write a query that pulls out info from (1)the Accounts table and information from (2) the Transaction table. It is the second step that I am having problems with. I want to pull from the Transaction table the summed Transaction.Amount's occuring before a certain date and then in a seperate field the summed Transaction.Amount's occuring on a certain month. The problem I have is that to try and do it in a single select query I have two conflicting WHERE clauses. i.e. "SELECT ACCOUNTID, SUM(TRANSACTION.AMOUNT)FROM TRANSACTIONWHERE TRANSACTION.DATE<'31/03/07'" to get all transactions before a certain date and "WHERE TRANSACTION.DATE<'31/03/07' AND TRANSACTION.DATE>'01/03/07'" to get all transactions for a certain month. Does anyone know how I can combine these into a query that will return both results in seperate fields? I was thinking maybe some kind of join between two different select statements but if so how do you join them? e.g. "SELECT ACCOUNTID, SUM(TRANSACTION.AMOUNT)FROM TRANSACTION FULL OUTER JOIN (SELECT STATEMENT 2) ON ????WHERE TRANSACTION.DATE<'31/03/07'"I could be way of the mark. Anyone got any suggestions? |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-04-08 : 01:05:14
|
To be absolutely sure that all transactions for month of march are fetched, useWHERE Transaction.Date >= '20070301' AND Transaction.Date < '20070401'
E 12°55'05.25"N 56°04'39.16" |
 |
|
|
|
|
|
Query Help Please