September 23, 2011 at 6:11 am
i want to calculate PreviousNAV, TodaysNAV and want to generate ssrs report
input : Startdate and enddate
now Enddate = anydate for example (todays - 9/23/2011)
and based on that i have one more parameter called comparedate which will give me Startdate
compare date = monthly , daily .....
if i select monthly then start date will be - 8/31/2011
so
now input is:
startdate - 9/23/2011
enddate - 8/31/2011
i have one table called fireball..nav from that i'm taking 'nav' values based on dates
I wrote this SP but this is giving me only 1 values for both 'PreviousNAV', 'TodaysNAV'
as my compare is for a month i need to have more than one value to be printed on report
ALTER PROCEDURE [dbo].[ProgressReport]
(
@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)
DECLARE @NetChange DECIMAL(18,4)
DECLARE @BAMLIndex DECIMAL(18,4)
SELECT Previous.PreviousNAV , Todays.TodaysNAV
FROM
(
SELECT PortfolioId,ISNULL(NAV,0) PreviousNAV
FROM Fireball..NAV
WHERE Date = @StartDate and PortfolioId = @PortfolioId
) Previous
JOIN
(
SELECT PortfolioId,ISNULL(NAV,0) TodaysNAV
FROM Fireball..NAV
WHERE Date = @EndDate and PortfolioId = @PortfolioId
) Todays
ON Previous.PortfolioId = Todays.PortfolioId
END
GO
I TRIED ABOVE QUERY I DONT KNOW IT IS CORRECT OR NOT
PLEASE MODIFY
NOW I WANT TO GENERATE REPORT BASED ON START DATE AND END DATE
PLEASE HELP
September 23, 2011 at 7:20 am
Please stop crossposting!
You original question is here: http://www.sqlservercentral.com/Forums/FindPost1179871.aspx
If you don't get the answer you want probably it's because your question is unclear.
Try rephrasing or adding more detail.
-- Gianluca Sartori
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply