Summarize amount per account per period and ytd

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

SQL Server 2005 Forums

Transact-SQL (2005)

Summarize amount per account per period and ytd

Author

Topic

sue1127
Starting Member

23 Posts

Posted - 2008-09-26 : 16:36:50

I'm creating a temporary table(#transactions) in a Sql 2005 stored procedure that contains the transaction amount entered in a period <= the period the user enters.

Then I create a temporary table that contains budget information. I join these 2 tables to create a 3rd temporary table(#crystal). In this table I separate out the amount in the current period that the user has entered. I need to do this because I need a total per account per current period, as well as a total per account year-to-date.

Originally I thought I was going to put the detail into Sql Server Reporting Services. But now I want to sum the period amount per account and the year to date amount per account. I want to put only these summary lines into Reporting Services, including as well the description and the budgeted amount(budamount) per account.

I’m fairly new to SQL, and have been trying to teach myself as I go along. I’ve tried a variety of ways but nothing has worked.

Can anyone help?

Thanks very much.

Sue

insert into #transactions

SELECT
t.gl7accountsid,
a.accountnumber,
a.description,
a.category,
t.POSTDATE,
t.poststatus,
t.TRANSACTIONTYPE,
t.AMOUNT,
case
when t.transactiontype=2 then amount * (-1)
else amount
end as transamount,
t.ENCUMBRANCESTATUS,
t.gl7fiscalperiodsid

FROM
UC.dbo.gl7accounts a

join
UC.dbo.gl7transactions t on
a.gl7accountsid=t.gl7accountsid

where
(t.gl7fiscalperiodsid >= 109
And
t.gl7fiscalperiodsid<=111)
And poststatus in (2,3)

order by a.accountnumber

insert into #crystal
select
b.gl7accountsid,
b.accountnumber,
t.description,
b. bud_description,
t.category,
t.postdate,
t.poststatus,
t.transactiontype,
t.transamount,
coalesce(t.transamount,0) as TransactionAmount,
t.encumbrancestatus,
b.budamount,
t.gl7fiscalperiodsid,
current_period_id=@getcurrperiod,
period_amount=
Case
When t.gl7fiscalperiodsid=@getcurrperiod Then t.transamount
Else 0
End

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-27 : 02:30:46

illustrate the output you want by giving some sample data. refer the below link for details of how to post a question

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

sue1127
Starting Member

23 Posts

Posted - 2008-10-07 : 16:12:11

Hi visakh16,

Thank you for responding to my question. I will be out of the office for a week, and won't have a chance to try it until I get back. I definitely want to try it then.

Thanks again,

Sue

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources