|
|
Posted by Erland Sommarskog on 10/01/76 11:42
(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/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|