September 23, 2011 at 12:24 am
Hi
I need to generate SSRS report
for that I need to write Stored procedure
I have one table called 'NAV'
fields are NAVid,Date,NAV,PortfolioID
now
My report should have following fields to be calculated..
PreviousNAV , TodaysNAV , (TodaysNAV - PreviousNAV)
Now
I wrote procedure like this...
input parameters are startdate , enddate , portfolioid
I'm taking enddate and calculating startdate
like
DECLARE @StartDate DATETIME =
CASE
WHEN @ComparePeriod = 'Daily' THEN dbo.DateUtility_MakeBusinessDay(DATEADD(DAY, -1, @EndDate), 'LessThanOrEqualTo')
WHEN @ComparePeriod = 'Weekly' THEN dbo.DateUtility_GetRelativeDate(@EndDate, 'LessThan', 'End', 'Week', 1)
WHEN @ComparePeriod = 'MTD' THEN dbo.DateUtility_GetRelativeDate(@EndDate, 'LessThan', 'End', 'Month', 1)
WHEN @ComparePeriod = 'QTD' THEN dbo.DateUtility_GetRelativeDate(@EndDate, 'LessThan', 'End', 'Quarter', 1)
WHEN @ComparePeriod = 'YTD' THEN dbo.DateUtility_GetRelativeDate(@EndDate, 'LessThan', 'End', 'Year', 1)
WHEN @ComparePeriod = 'LTM' THEN DATEADD(MONTH, -12, @EndDate)
WHEN @ComparePeriod = 'LTD' THEN CAST(Fireball_Configuration.dbo.GetConfigurationValue('Fireball', 'Concerto Credit Opportunity Master Fund I - Inception Date') AS DATETIME)
END
now how can i select report fileds
"PreviousNAV , TodaysNAV , (TodaysNAV - PreviousNAV)" ???
help to complete following SP
CREATE PROCEDURE [dbo].[Progreereport]
(
@EndDate DATETIME,
@ComparePeriod VARCHAR(10),
@PortfolioId VARCHAR(50) = '6'
)
AS
BEGIN
DECLARE @StartDate DATETIME =
CASE
WHEN @ComparePeriod = 'Daily' THEN dbo.DateUtility_MakeBusinessDay(DATEADD(DAY, -1, @EndDate), 'LessThanOrEqualTo')
WHEN @ComparePeriod = 'Weekly' THEN dbo.DateUtility_GetRelativeDate(@EndDate, 'LessThan', 'End', 'Week', 1)
WHEN @ComparePeriod = 'MTD' THEN dbo.DateUtility_GetRelativeDate(@EndDate, 'LessThan', 'End', 'Month', 1)
WHEN @ComparePeriod = 'QTD' THEN dbo.DateUtility_GetRelativeDate(@EndDate, 'LessThan', 'End', 'Quarter', 1)
WHEN @ComparePeriod = 'YTD' THEN dbo.DateUtility_GetRelativeDate(@EndDate, 'LessThan', 'End', 'Year', 1)
WHEN @ComparePeriod = 'LTM' THEN DATEADD(MONTH, -12, @EndDate)
WHEN @ComparePeriod = 'LTD' THEN CAST(Fireball_Configuration.dbo.GetConfigurationValue('Fireball', 'Concerto Credit Opportunity Master Fund I - Inception Date') AS DATETIME)
END
DECLARE @PreviousNAV DECIMAL(18,4)
DECLARE @TodaysNAV DECIMAL(18,4)
SELECT ISNULL(@PreviousNAV,0) PreviousNAV
FROM Fireball..NAV
WHERE Date = @StartDate and PortfolioId = @PortfolioId
SELECT ISNULL(@TodaysNAV,0) TodaysNAV
FROM Fireball..NAV
WHERE Date = @EndDate and PortfolioId = @PortfolioId
END
GO
IS above correct ??
September 23, 2011 at 2:10 am
I think it's a duplicate post to this one: http://www.sqlservercentral.com/Forums/FindPost1179871.aspx
Or are you asking a different question?
-- Gianluca Sartori
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply