Posted by jannoergaard on 10/01/60 11:42

Hi
I want to write a function that can return a sum for a given date
range. The same function should be able to return the sum for the same
period year before.

Let me give an example:
The Table LedgerTrans consist among other of the follwing fields
AccountNum (Varchar)
Transdate
AmountMST (Real)

The sample data could be
1111, 01-01-2005, 100 USD
1111, 18-01-2005, 125 USD
1111, 15-03-2005, 50 USD
1111,27-06-2005, 500 USD
1111,02-01-2006, 250 USD
1111,23-02-2006,12 USD

If the current day is 16. march 2006 I would like to have a function
which called twice could retrive the values.
Previus period (for TransDate >= 01-01-2005 AND TransDate <=
16-03-2005) = 275 USD
Current period (for TransDate >= 01-01-2006 AND TransDate <=
16-03-2006) = 262 USD
The function should be called with the AccountNum and current date
(GetDate() ?) and f.ex. 0 or 1 for this year / previous year.
How can I create a function that dynamically can do this ?

I have tried f.ex. calling the function with
@ThisYear as GetDate()
SET @DateStart = datepart(d,0) + '-' + datepart(m,0) +
'-'+datepart(y,@ThisYear)
But the value for @dateStart is something like 12-07-1905 so this
don't work.

I Would appreciate any help on this.
BR / Jan

[Back to original message]