Compare given period in current year / previous year

[jannoergaard]

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,@T hisYear)
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

[Erland Sommarskog]

(ja**********@h otmail.com) writes:

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'm uncertain on want interface you want on your function (and I am
not sure that you should use a function anyway), but here is a
query for the task:

SELECT AccountNum, LastYearTrouble s =
SUM(CASE WHEN Transdate BETWEEN
dateadd(YEAR, -1,
convert(char(4) , @date, 112) + '0101')) AND
dateadd(YEAR, -1, @date)
THEN AmountMST
ELSE 0
END),
ThisYear =
SUM(CASE WHEN Transdate BETWEEN
convert(char(4) , @date, 112) + '0101')) AND
@date)
THEN AmountMST
ELSE 0
END)
FROM Ledger
WHERE TransDate BETWEEN dateadd(YEAR, -1,
convert(char(4) , @date, 112) + '0101')) AND
@date
GROUP BY AccountNum

As for the date conversion, format 112 is essentail for playing with
dates. This format is YYYYMMDD, and this is one of the formats that
always converts back to date in the same way.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[jannoergaard]

Hi There

Thank you very much. I modified your query a lillte bit, and it worked
as wanted in the function.

CREATE FUNCTION GuruInvoicedPer iodNew (@AccountNum as
VarChar(10),@Pe riod AS Int, @ThisY as DateTime)
RETURNS Float AS
BEGIN

DECLARE @LedgerTrans AS Float
SET @LedgerTrans = 0

IF @Period = 0 /*this year*/
BEGIN
SELECT @LedgerTrans =
SUM(AmountMST) FROM dbo.LedgerTrans
WHERE (DATAAREAID = dbo.GuruDataAre a())
AND (TransDate >= (convert(char(4 ), @ThisY, 112) + '0101')
AND TransDate <= @ThisY)
AND AccountNum = @AccountNum
END

IF @Period = 1 /*previous year*/
BEGIN
SELECT @LedgerTrans = SUM(AMOUNTMST) FROM dbo.LEDGERTRANS
WHERE (DATAAREAID = dbo.GuruDataAre a())
AND Transdate >= (dateadd(YEAR, -1, convert(char(4) , @ThisY,
112) + '0101'))
AND TransDate <= dateadd(YEAR, -1, @ThisY)
AND AccountNum = @AccountNum
END

RETURN @LedgerTrans
END
BR/Jan

[Erland Sommarskog]

(ja**********@h otmail.com) writes:

Thank you very much. I modified your query a lillte bit, and it worked
as wanted in the function.

CREATE FUNCTION GuruInvoicedPer iodNew (@AccountNum as
VarChar(10),@Pe riod AS Int, @ThisY as DateTime)
RETURNS Float AS
BEGIN

Yellow alert! How are you going to use this function? If you are going
to say something like:

SELECT AccountNum, dbo.InvoicedPer iod(AccountNum, 1, getdate()),
dbo.InvoicedPer iod(AccountNum, 0, getdate())
FROM accounts

It's not going to perform well. Scalar UDFs is something you should use
with care, and not the least scalar UDFs that perform table access.
There is quite an overhead for calling a UDF once per row, and when you
do table access, you have essentially created a disguised cursor.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx