x

Combining some columns and keeping some single for sums in a select statement

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.

WITH Annual as
(
SELECT   glm_chart.acct_desc, 
GLM_CHART.acct_code, 
SUM(DEBITS - CREDITS)'Annual_Budget'
FROM GLH_DEPTSUM join GLM_CHART on GLH_DEPTSUM.ACCT_UNO = GLM_CHART.ACCT_UNO 
join hbl_office on glh_deptsum.offc = hbl_office.offc_code
WHERE DATA_SET = 3 
AND PERIOD between 201100 and  201112
and GLM_CHART.acct_code in 
('570000','573000','581000','586000','590000','591000','596000','597000',
'598000','599000','606000','607000','608000','609000','610000','620000',
'625000','630000','636000','637000','645000','674000','670000','681000',
'695000','700000','705000','701000','710000','720000','740000','550000',
'791000','792000','790000')
GROUP BY  GLM_CHART.acct_code, glm_chart.acct_desc
),YTD as
(
SELECT glm_chart.acct_desc, 
GLM_CHART.acct_code, 
SUM(DEBITS - CREDITS)'YTD_Budget'
FROM GLH_DEPTSUM join GLM_CHART on GLH_DEPTSUM.ACCT_UNO = GLM_CHART.ACCT_UNO
join hbl_office on glh_deptsum.offc = hbl_office.offc_code
WHERE DATA_SET = 3 
AND PERIOD between 201100 and  201108
and GLM_CHART.acct_code in
 ('570000','573000','581000','586000','590000','591000','596000','597000',
'598000','599000','606000','607000','608000','609000','610000','620000',
'625000','630000','636000','637000','645000','674000','670000','681000',
'695000','700000','705000','701000','710000','720000','740000','550000',
'791000','792000','790000')
GROUP BY  GLM_CHART.acct_code, glm_chart.acct_desc
), actual as 
(SELECT glm_chart.acct_desc, 
GLM_CHART.acct_code, 
SUM(DEBITS - CREDITS)'Actual'
FROM GLH_DEPTSUM join GLM_CHART on GLH_DEPTSUM.ACCT_UNO = GLM_CHART.ACCT_UNO 
join hbl_office on glh_deptsum.offc = hbl_office.offc_code
WHERE DATA_SET = 1
AND PERIOD between 201100 and  201108
and GLM_CHART.acct_code in 
('570000','573000','581000','586000','590000','591000','596000','597000','598000','599000',
'606000','607000','608000','609000','610000','620000','625000','630000','636000','637000',
'645000','674000','670000','681000','695000','700000','705000','701000','710000','720000',
'740000','550000','791000','792000','790000')
GROUP BY  GLM_CHART.acct_code, glm_chart.acct_desc
)
Select actual.acct_code,
actual.acct_desc,
annual.annual_budget,
ytd.ytd_budget,
actual.actual
from actual join annual on actual.acct_code = annual.acct_code
join ytd on actual.acct_code = ytd.acct_code
order by actual.acct_desc
more ▼

asked Nov 01, 2011 at 12:00 PM in Default

lostinaquery gravatar image

lostinaquery
46 3 3 4

cud u share the table structure and output structure you want..
Nov 02, 2011 at 03:51 AM Sri 1

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.
Nov 02, 2011 at 09:46 AM lostinaquery
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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 :-)
more ▼

answered Nov 02, 2011 at 01:48 AM

WilliamD gravatar image

WilliamD
25.9k 17 19 41

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, 2011 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, 2011 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, 2011 at 09:06 AM lostinaquery
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x40
x24

asked: Nov 01, 2011 at 12:00 PM

Seen: 711 times

Last Updated: Nov 01, 2011 at 01:06 PM