|
I have a requirement to create a query to include certain budget items with an ultimate goal being a report for distribution to the responsible personnel. I have a table for the account code and description and another table which holds the debits and credits. They are linked on the account uno. The problem I have is that for the report some accounts need to appear individually, and some need to appear combined. For example, we may have individual accounts for business taxes, payroll taxes, city taxes but on the query we just want one line item that says Taxes whereas rent is a single account and that is what we want to appear on the query. Some of the combined items have contiguous accout codes, so a range will work, but there are some others that do not. And, not all of the accounts in the table need to be included. I thought that perhaps I could use CASE statements to group the account codes and do the SUM(debits-credits) but that isn't working for me. Am I going to have to create a temp table and manipulate the data in there to get what I need? Or is there some other method to get what I need? Before I knew about the additional accounts (the combined ones) I had this, which does what I need for the listed accounts.
(comments are locked)
|
|
I think the simple addition of a grouping key would help you out here. You add an extra CTE to assign each of your accounts a group key (so the taxes all have a group id, whilst rent gets another group id). You then modify your sum to use the new group id. Having a slow brain day, so my explanation may be a little poor. If this makes no sense, let me know and I'll try again :-) The theory works for me - it's just the application that's a problem. When I try to write the SELECT my brain just says 'Huh?' I tried using case statements to set my groups (case when acct code between 5 and 10 then Maint, etc.) but then I get befuddled. It's pushing the envelope of my t-sql knowledge. :-)
Nov 02 '11 at 11:51 AM
lostinaquery
you could simplify your thinking by just having a bunch of selects that have group and account and UNION them all together in a CTE. That way you ignore the CASE stuff and just list each account and the group it belongs to. You then join that CTE to the aggregation and drive the SUM based on the grouping instead of the accounts directly. Throw up some CREATE TABLEs and test data and what output you expect. I (or someone else on here) will happily give you a hand sorting it out.
Nov 02 '11 at 12:10 PM
WilliamD
William, thanks for the response. For some reason I didn't get notified that something else had been posted. I'm not sure how soon I'll get to try your suggestion; in the meantime a co-worker reworked this and created a temp table and dumped it all in there. It works but it seems there should be a more elegant solution. I'm waiting for more data (seems I don't have the correct accounts, or all the accounts, or something along those lines) and then I can get back to work on this.
Nov 14 '11 at 09:06 AM
lostinaquery
(comments are locked)
|


cud u share the table structure and output structure you want..
The output should be similar to this:
Account Desc Annual Budget YTD Budget Actual Amt.
Maintenance 12,000 11,000 10,750 Taxes 50,000 45,800 47,000 Dues 32,000 24,000 26,450 Insurance 75,000 67,000 67,000 Etc.