How to separate Period Amount from YTD Amount

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)

How to separate Period Amount from YTD Amount

Author

Topic

sue1127
Starting Member

23 Posts

Posted - 2008-03-18 : 11:36:24

I'm creating a temporary table in a Sql 2005 stored procedure that contains the transaction amount entered in a period <= the period the user enters.
I can return that amount in my result set. But I also need to separate out by account the amounts just in the period = the period the user enters. There can be many entries or no entries in any period. I populate the temporary table this way:

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
UrsinusCollege.dbo.gl7accounts a

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

where
(t.gl7fiscalperiodsid >= 97
And
t.gl7fiscalperiodsid<=@FiscalPeriod_identifier)
And poststatus in (2,3)
and left(a.accountnumber,5) between '2-110' and '2-999'
And right(a.accountnumber,4) > 7149
And not(right(a.accountnumber,4)) in ('7171','7897')

order by a.accountnumber

Later I create a temporary table that contains budget information. I join these 2 temporary tables to produce my result set. But I don't know how to get the information for just one period. For example, if the user enters 99 as the FiscalPeriod_identifier, I need a separate field that contains only those amounts(if any) that were entered for each account in Period 99.

Can anyone help? It may be that I am not seeing the forest for the trees, but I can't figure it out.

Thanks very much.

Sue

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-18 : 11:48:02

How do youwant the output if there are more than one transactions in the entered period? sum of all those transactions?

sue1127
Starting Member

23 Posts

Posted - 2008-03-18 : 11:53:43

I'll probably need to have the detail of the transactions, and sum them in Sql Server Reporting Services, which will call the procedure. I think the users will want the capability to see the detail if they want to, as well as the sum of the transactions.

Thanks,
Sue

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-18 : 12:11:53

Then it will be easier for you to get the whole detail onto reporting services and do the extra filterations inside reporting service.

sue1127
Starting Member

23 Posts

Posted - 2008-03-18 : 12:19:26

The problem is that I'm a newbie to Reporting Services(even newer than I am to Sql). I've tried to separate out the current period in SSRS, but I haven't been able to make that work either.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-18 : 12:32:53

Its always easier to group them and display result at SSRS rather than in SQL. Just refer any basic SSRS help article to get a start.

sue1127
Starting Member

23 Posts

Posted - 2008-03-18 : 13:20:49

Ok, I'll give it a try.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources