JeffJ (2/24/2009)
GSquared,Here they are:)
CREATE FUNCTION [Perf].[udf_GenerateTimeSeries](
@periodStart SMALLDATETIME,
@periodEnd SMALLDATETIME,
@interval VARCHAR(2)
)
RETURNS @tbl TABLE
(
ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
Begin_Date SMALLDATETIME,
End_Date SMALLDATETIME
)
AS
BEGIN
DECLARE @start SMALLDATETIME,
@end SMALLDATETIME
IF @interval ='d'
BEGIN
INSERT INTO @tbl
SELECT a.dte Begin_Date, b.dte End_Date
FROM(
SELECT DATEADD(d, 1, DATEADD(d, -1*(N+1), @periodEnd)) dte,
RANK() OVER (PARTITION BY 1 ORDER BY DATEADD(d, N, @periodStart)) ID
FROM tools.dbo.tally
WHERE N <=DATEDIFF(d, @periodStart, @periodEnd)
) a
INNER JOIN (
SELECT DATEADD(d, -1*N, @periodEnd) dte,
RANK() OVER (PARTITION BY 1 ORDER BY DATEADD(d, N, @periodStart)) ID
FROM tools.dbo.tally
WHERE N <=DATEDIFF(d, @periodStart, @periodEnd)
) b ON a.ID = b.ID
END
ELSE IF @interval = 'wk'
BEGIN
SELECT @start = DATEADD(wk, -1, DATEADD(d, 7-DATEPART(dw, @periodStart), @periodStart))
SELECT @end = DATEADD(d, 6-DATEPART(dw, @periodEnd), @periodEnd)
INSERT INTO @tbl
SELECT a.dte, b.dte
FROM (
SELECT CASE WHEN DATEADD(wk, N, @start) < @periodStart THEN @periodStart ELSE DATEADD(wk, N, @start) END dte,
RANK() OVER (PARTITION BY 1 ORDER BY DATEADD(wk, N, @start)) ID
FROM tools.dbo.tally
WHERE N < DATEDIFF(wk, @Start, @End)
) a
INNER JOIN (
SELECT CASE WHEN DATEADD(wk, -1*N, @end) > @periodEnd THEN @periodEnd ELSE DATEADD(wk, -1*N, @end) END dte,
RANK() OVER (PARTITION BY 1 ORDER BY DATEADD(wk, -1*N, @end)) ID
FROM tools.dbo.tally
WHERE N < DATEDIFF(wk, @Start, @End)
)b ON a.ID = b.ID
END
ELSE IF @interval = 'm'
BEGIN
SELECT @start = DATEADD(m, DATEDIFF(m, 0, @periodStart),0)
SELECT @end = DATEADD(d, -1, DATEADD(m, DATEDIFF(m, 0, @periodEnd)+1,0))
INSERT INTO @tbl
SELECT a.dte, b.dte
FROM (
SELECT CASE WHEN DATEADD(m, N, @start) < @periodStart THEN @periodStart ELSE DATEADD(m, N, @start) END dte,
RANK() OVER (PARTITION BY 1 ORDER BY DATEADD(m, N, @start)) ID
FROM tools.dbo.tally
WHERE N <= DATEDIFF(m, @Start, @End)
) a
INNER JOIN (
SELECT CASE WHEN DATEADD(m, -1*N, @end) > @periodEnd THEN @periodEnd ELSE DATEADD(m, -1*N, @end) END dte,
RANK() OVER (PARTITION BY 1 ORDER BY DATEADD(m, -1*N, @end)) ID
FROM tools.dbo.tally
WHERE N <= DATEDIFF(m, @Start, @End)
)b ON a.ID = b.ID
END
ELSE IF @interval = 'q'
BEGIN
SELECT @start = DATEADD(q, DATEDIFF(q, 0, @periodStart),0)
SELECT @end = DATEADD(q, DATEDIFF(q, 0, @periodEnd)+1,0)--)
INSERT INTO @tbl
SELECT a.dte, b.dte
FROM (
SELECT CASE WHEN DATEADD(q, N, @start) < @periodStart THEN @periodStart ELSE DATEADD(q, N, @start) END dte,
RANK() OVER (PARTITION BY 1 ORDER BY DATEADD(q, N, @start)) ID
FROM tools.dbo.tally
WHERE N <= DATEDIFF(q, @Start, @End)
) a
INNER JOIN (
SELECT CASE WHEN DATEADD(d,-1,DATEADD(q, -1*N, @end)) > @periodEnd THEN @periodEnd ELSE DATEADD(d,-1,DATEADD(q, -1*N, @end)) END dte,
RANK() OVER (PARTITION BY 1 ORDER BY DATEADD(d,-1,DATEADD(q, -1*N, @end))) ID
FROM tools.dbo.tally
WHERE N < DATEDIFF(q, @Start, @End)
)b ON a.ID = b.ID
END
ELSE IF @interval = 'yy'
BEGIN
SELECT @start = DATEADD(yy, DATEDIFF(yy, 0, @periodStart),0)
SELECT @end = DATEADD(d, -1, DATEADD(yy, DATEDIFF(yy, 0, @periodEnd)+1,0))
INSERT INTO @tbl
SELECT a.dte, b.dte
FROM (
SELECT CASE WHEN DATEADD(yy, N, @start) < @periodStart THEN @periodStart ELSE DATEADD(yy, N, @start) END dte,
RANK() OVER (PARTITION BY 1 ORDER BY DATEADD(yy, N, @start)) ID
FROM tools.dbo.tally
WHERE N <= DATEDIFF(yy, @Start, @End)
) a
INNER JOIN (
SELECT CASE WHEN DATEADD(yy, -1*N, @end) > @periodEnd THEN @periodEnd ELSE DATEADD(yy, -1*N, @end) END dte,
RANK() OVER (PARTITION BY 1 ORDER BY DATEADD(yy, -1*N, @end)) ID
FROM tools.dbo.tally
WHERE N <= DATEDIFF(yy, @Start, @End)
)b ON a.ID = b.ID
END
RETURN
END
Whew~! And if that wasn't enough... here's the second
Alter FUNCTION perf.udf_Portfolio_Indicators(
@periodStart SMALLDATETIME,
@periodEnd SMALLDATETIME,
@portfolio varchar(MAX),
@subbusunit varchar(MAX)
)
RETURNS TABLE
AS RETURN
(
SELECT p.PNL, p.LMV, p.PNL/p.LMV ROLMV,
((CAST(p.Total AS DECIMAL(10,5))/p.Avg_Num)-1) * (365.0/(DATEDIFF(d, @periodStart, @periodEnd) + 1)) Turnover,
CAST(p.Winners AS DECIMAL(10,5))/CAST(p.Total AS DECIMAL(10,5)) BA,
p.Win_PNL/p.Win_LMV ROLMV_W,
p.Los_PNL/p.Los_LMV ROLMV_L,
p.[>50%], p.[40% to 50%], p.[30% to 40%], p.[20% to 30%], p.[10% to 20%], p.[0% to 10%],
p.[-10% to 0%], p.[-20% to -10%], p.[-30% to -20%], p.[-40% to -30%], p.[-50% to -40%], p.[< -50%]
FROM (
SELECT SUM(PNL) PNL,
SUM(Long_MV)/(DATEDIFF(d, MIN(begin_date), MAX(end_date))+1) LMV,
SUM(Total) Total,
SUM(Winner) Winners,
SUM(CASE WHEN winner = 1 THEN PNL ELSE 0 END) Win_PNL,
SUM(CASE WHEN winner = 1 THEN Long_MV ELSE 0 END)/(DATEDIFF(d, MIN(begin_date), MAX(end_date))+1) Win_LMV,
SUM(Loser) Losers,
SUM(CASE WHEN loser = 1 THEN PNL ELSE 0 END) Los_PNL,
SUM(CASE WHEN loser = 1 THEN Long_MV ELSE 0 END)/(DATEDIFF(d, MIN(begin_date), MAX(end_date))+1) Los_LMV,
DATEDIFF(d, MIN(begin_date), MAX(end_date))+1 days,
SUM(CASE WHEN Tier = 0 THEN 1 ELSE 0 END) [9998],
SUM(CASE WHEN Tier = 1 THEN 1 ELSE 0 END) [>50%],
SUM(CASE WHEN Tier = 2 THEN 1 ELSE 0 END) [40% to 50%],
SUM(CASE WHEN Tier = 3 THEN 1 ELSE 0 END) [30% to 40%],
SUM(CASE WHEN Tier = 4 THEN 1 ELSE 0 END) [20% to 30%],
SUM(CASE WHEN Tier = 5 THEN 1 ELSE 0 END) [10% to 20%],
SUM(CASE WHEN Tier = 6 THEN 1 ELSE 0 END) [0% to 10%],
SUM(CASE WHEN Tier = 7 THEN 1 ELSE 0 END) [-10% to 0%],
SUM(CASE WHEN Tier = 8 THEN 1 ELSE 0 END) [-20% to -10%],
SUM(CASE WHEN Tier = 9 THEN 1 ELSE 0 END) [-30% to -20%],
SUM(CASE WHEN Tier = 10 THEN 1 ELSE 0 END)[-40% to -30%],
SUM(CASE WHEN Tier = 11THEN 1 ELSE 0 END) [-50% to -40%],
SUM(CASE WHEN Tier = 12 THEN 1 ELSE 0 END)[< -50%],
SUM(CASE WHEN Total = 1 THEN Strategy_Days/(DATEDIFF(d, @periodStart, @periodEnd)+1) ELSE 0 End) Avg_Num
FROM perf.udf_portfolio_indicators_strategy (@periodStart, @periodEnd, @portfolio) p
INNER JOIN tools.dbo.udf_parselist(@subbusunit, ',') list ON p.subbus_unit_code = list.token
) p
CROSS JOIN (
SELECT Sum(Capital)/COUNT(DISTINCT rundate) Capital
FROM perf.Allocated_Capital_View ac
INNER JOIN tools.dbo.udf_parselist(@portfolio, ',') p ON ac.portfolio = p.token
INNER JOIN tools.dbo.udf_parselist(@subbusunit, ',') s ON ac.subbusunit = s.token
WHERE Rundate BETWEEN @periodStart AND @periodEnd
) ac
)
Thanks for taking the time to take a look!
~Jeff
You should seriously need to look into a "CALENDAR" Table!
* Noel