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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 query that provides a default transaction type...

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 applied

Aim:

- 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

   

- Advertisement -