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(jannoergaard@.hotmail.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, LastYearTroubles =
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, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||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|||(jannoergaard@.hotmail.com) writes:
> 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
Yellow alert! How are you going to use this function? If you are going
to say something like:
SELECT AccountNum, dbo.InvoicedPeriod(AccountNum, 1, getdate()),
dbo.InvoicedPeriod(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, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
Jason92 wrote: