Using input parameters differently for different values

30 Dec 2005 12:09 PM

Mark Rae

Hi,

I have the following table in SQL Server 2000 which contains accounting
ledger data:

ACCNT_CODE varchar(10)
ACCNT_NAME varchar(50)
ACCNT_TYPE char(1)
TRANS_DATE smalldatetime
PERIOD int
D_C char(1)
AMOUNT numeric

The PERIOD field indicates which accounting period the record belongs to -
e.g. 1991001 is year 1991, period 1.

The "D_C" field contains either "D" or "C" to indicate whether the
corresponding "AMOUNT" value is a debit or a credit.

The data has been imported from an antiquated version of Sun Accounts for
DOS which my client has *finally* got round to replacing with a more modern
system. However, they don't want to import the old data into their new
package because it's "too old" - they simply want to keep an archive of it
in SQL Server in case they ever need to refer to it.

To that end, I've been asked to develop a stored procedure which will be
called from a web page on the client's intranet. The users will supply a
beginning period and ending period and the SP will return summarised data
for all the transactions recorded between the two periods.

On the assumption that I use a SP, let's assume it begins thus:

CREATE PROCEDURE AccountsSummary
@FROM_PERIOD int,
@TO_PERIOD int
AS
............

However, the client wants five summary fields to be returned, all from the
AMOUNT field, as follows:

SUM(AMOUNT) AS OPENING_BALANCE
WHERE PERIOD < @FROM_PERIOD

SUM(AMOUNT) AS PERIOD_DEBITS
WHERE PERIOD >= @FROM_PERIOD
AND PERIOD <= @TO_PERIOD
AND D_C = 'D'

SUM(AMOUNT) AS PERIOD_CREDITS
WHERE PERIOD >= @FROM_PERIOD
AND PERIOD <= @TO_PERIOD
AND D_C = 'C'

SUM(AMOUNT) AS PERIOD_BALANCE
WHERE PERIOD >= @FROM_PERIOD
AND PERIOD <= @TO_PERIOD

SUM(AMOUNT) AS CLOSING_BALANCE
WHERE PERIOD <= @TO_PERIOD

Each of these is easy enough to do invidually, of course. However, I'm
looking for some advice now as to the best way to return all five summarised
calculations to the client. It is even possible to do this in a single
stored procedure? Should I be looking table functions / views etc?

Any assistance gratefully received.

Mark

30 Dec 2005 12:30 PM

Christian Donner

It works just as it is! A client program will call the stored procedure and
store the result(s) in an ADO recordset object. To evaluate all of the result
sets the client only has to issue the following command (assuming that the
name of his recordset object is 'rs'):
set rs = rs.NextRecordset()
after each result set. That's all ...

30 Dec 2005 1:17 PM

Mark Rae

"Christian Donner" <ChristianDon***@discussions.microsoft.com> wrote in
message news:59024640-A436-452C-8D78-5B0EE2E2C8F6@microsoft.com...

> It works just as it is! A client program will call the stored procedure
> and
> store the result(s) in an ADO recordset object. To evaluate all of the
> result
> sets the client only has to issue the following command (assuming that the
> name of his recordset object is 'rs'):
> set rs = rs.NextRecordset()
> after each result set. That's all ...

That has nothing to do with my original post, but thanks for the reply
anyway.

30 Dec 2005 12:46 PM

David Portas

Mark Rae wrote:
Show quote

> Hi,
>
> I have the following table in SQL Server 2000 which contains accounting
> ledger data:
>
> ACCNT_CODE varchar(10)
> ACCNT_NAME varchar(50)
> ACCNT_TYPE char(1)
> TRANS_DATE smalldatetime
> PERIOD int
> D_C char(1)
> AMOUNT numeric
>
> The PERIOD field indicates which accounting period the record belongs to -
> e.g. 1991001 is year 1991, period 1.
>
> The "D_C" field contains either "D" or "C" to indicate whether the
> corresponding "AMOUNT" value is a debit or a credit.
>
> The data has been imported from an antiquated version of Sun Accounts for
> DOS which my client has *finally* got round to replacing with a more modern
> system. However, they don't want to import the old data into their new
> package because it's "too old" - they simply want to keep an archive of it
> in SQL Server in case they ever need to refer to it.
>
> To that end, I've been asked to develop a stored procedure which will be
> called from a web page on the client's intranet. The users will supply a
> beginning period and ending period and the SP will return summarised data
> for all the transactions recorded between the two periods.
>
> On the assumption that I use a SP, let's assume it begins thus:
>
> CREATE PROCEDURE AccountsSummary
> @FROM_PERIOD int,
> @TO_PERIOD int
> AS
> ...........
>
> However, the client wants five summary fields to be returned, all from the
> AMOUNT field, as follows:
>
> SUM(AMOUNT) AS OPENING_BALANCE
> WHERE PERIOD < @FROM_PERIOD
>
> SUM(AMOUNT) AS PERIOD_DEBITS
> WHERE PERIOD >= @FROM_PERIOD
> AND PERIOD <= @TO_PERIOD
> AND D_C = 'D'
>
> SUM(AMOUNT) AS PERIOD_CREDITS
> WHERE PERIOD >= @FROM_PERIOD
> AND PERIOD <= @TO_PERIOD
> AND D_C = 'C'
>
> SUM(AMOUNT) AS PERIOD_BALANCE
> WHERE PERIOD >= @FROM_PERIOD
> AND PERIOD <= @TO_PERIOD
>
> SUM(AMOUNT) AS CLOSING_BALANCE
> WHERE PERIOD <= @TO_PERIOD
>
> Each of these is easy enough to do invidually, of course. However, I'm
> looking for some advice now as to the best way to return all five summarised
> calculations to the client. It is even possible to do this in a single
> stored procedure? Should I be looking table functions / views etc?
>
> Any assistance gratefully received.
>
> Mark

Try this:

SELECT opening_balance, period_debits, period_credits,
period_debits + period_credits AS period_total,
opening_balance + period_debits + period_credits AS closing_balance
FROM
(SELECT
   SUM(CASE WHEN period < @from_period THEN amount END),
   SUM(CASE WHEN period >= @from_period AND d_c = 'D' THEN amount END),
   SUM(CASE WHEN period >= @from_period AND d_c = 'C' THEN amount END)
   FROM ledger
   WHERE period <= @to_period) AS
T(opening_balance, period_debits, period_credits);

--
David Portas
SQL Server MVP
--

30 Dec 2005 1:22 PM

Mark Rae

"David Portas" <REMOVE_BEFORE_REPLYING_dpor***@acm.org> wrote in message
news:1135946809.675331.279430@g47g2000cwa.googlegroups.com...

> Try this:

Excellent! Thanks very much.