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

Hi There

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

CREATE FUNCTION GuruInvoicedPeriodNew (@AccountNum as
VarChar(10),@Period 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.GuruDataArea())
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.GuruDataArea())
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

• Next in forum: Performance
• Prev in forum: Re: Querying a column with brackets in its name
• Thread view: Re: Compare given period in current year / previous year

[Reply to this message]