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 |
|
callagga
Starting Member
1 Post |
 Posted - 2015-02-24 : 19:55:31
|
How could I create a query that provides a default transaction type for those transactions not categorized?So assuming I have:- Transactions table (with transactions)- Categories table- transactions_categories table - allows to allocate multiple categories (with a percentage)- Usage is such that only non-personal categories have been applied through out data. So there is a lot of transactions with no categories appliedAim:- Want to create a query that creates a list of all the allocated amounts, so would include as columns: transaction.tDate, transaction.tTitle, categories.name, allocatedAmount(calculated from percentage * transaction amount)BUT:- How could I include in the query, the entries that cover all transaction that haven't been allocated, to a default category "personal", where the allocated Amount would be 100% of the transaction value- And also (if it were possible), for transactions that have been categorized but not for the complete transaction value (say only 50% was allocated to a category), how to to cover this off to. Example Transactions 1 XXXX $100 2 YYY $100 3 ZZZ $100 Categories 1 aaa 2 bbb 3 ccc 4 PersonalDefault Transaction-Categories (i.e. allocation) transID catID % 1 1 100% 2 2 50% Query Output I'm After transTitle catTitle AllocatedAmount xxxx aaaaa $100 yyyyy bbbbbb $50 yyyyy PersonalDefault $50 zzzzz PersonalDefault $100 |
|
|
|
|
|
query that provides a default transaction type...