returning monthly performance data

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

returning monthly performance data

Author

Topic

jrogers
Starting Member

34 Posts

Posted - 2008-03-31 : 05:43:30

I have a table fundperformance

CREATE TABLE [dbo].[FundPerformance](
	[MonthDate] [datetime] NOT NULL,
	[NAV] [float] NOT NULL,
 CONSTRAINT [PK_fpDate] PRIMARY KEY CLUSTERED 
(
	[MonthDate] ASC
))

which holds the date as the first of the month, and the NAV - the net asset value.
What I am trying to produce is a crosstab table where the columns are Jan, Feb, Mar etc.. and at the end Ytd (year to date)
the rows will have data from year 2005, 2006, 2007, 2008 etc.. showing the NAV, and also the monthly return, so far I have

select 
		cast(datepart(year,fp.monthdate) as char(4)) + ' Nav' 'Year',
		sum(case when datepart(month,fp.MonthDate) = 1 then fp.NAV end) 'Jan',
		sum(case when datepart(month,fp.MonthDate) = 2 then fp.NAV end) 'Feb',
		sum(case when datepart(month,fp.MonthDate) = 3 then fp.NAV end) 'Mar',
		sum(case when datepart(month,fp.MonthDate) = 4 then fp.NAV end) 'Apr',
		sum(case when datepart(month,fp.MonthDate) = 5 then fp.NAV end) 'May',
		sum(case when datepart(month,fp.MonthDate) = 6 then fp.NAV end) 'Jun',
		sum(case when datepart(month,fp.MonthDate) = 7 then fp.NAV end) 'Jul',
		sum(case when datepart(month,fp.MonthDate) = 8 then fp.NAV end) 'Aug',
		sum(case when datepart(month,fp.MonthDate) = 9 then fp.NAV end) 'Sep',
		sum(case when datepart(month,fp.MonthDate) = 10 then fp.NAV end) 'Oct',
		sum(case when datepart(month,fp.MonthDate) = 11 then fp.NAV end) 'Nov',
		sum(case when datepart(month,fp.MonthDate) = 12 then fp.NAV end) 'Dec'
	 from fundperformance fp left join fundperformance fp1 on fp.monthdate = dateadd(m,+1,fp1.monthdate)
	group by cast(datepart(year,fp.monthdate) as char(4))+ ' Nav'
	union ALL
	select 
		cast(datepart(year,fp.monthdate) as char(4)) + ' % Return' 'Year',
		sum(case when datepart(month,fp.MonthDate) = 1 then (fp.nav-coalesce(fp1.nav,100))/coalesce(fp1.nav,100)*100  end) 'Jan',
		sum(case when datepart(month,fp.MonthDate) = 2 then (fp.nav-coalesce(fp1.nav,100))/coalesce(fp1.nav,100)*100 end) 'Feb',
		sum(case when datepart(month,fp.MonthDate) = 3 then (fp.nav-coalesce(fp1.nav,100))/coalesce(fp1.nav,100)*100 end) 'Mar',
		sum(case when datepart(month,fp.MonthDate) = 4 then (fp.nav-coalesce(fp1.nav,100))/coalesce(fp1.nav,100)*100 end) 'Apr',
		sum(case when datepart(month,fp.MonthDate) = 5 then (fp.nav-coalesce(fp1.nav,100))/coalesce(fp1.nav,100)*100 end) 'May',
		sum(case when datepart(month,fp.MonthDate) = 6 then (fp.nav-coalesce(fp1.nav,100))/coalesce(fp1.nav,100)*100 end) 'Jun',
		sum(case when datepart(month,fp.MonthDate) = 7 then (fp.nav-coalesce(fp1.nav,100))/coalesce(fp1.nav,100)*100 end) 'Jul',
		sum(case when datepart(month,fp.MonthDate) = 8 then (fp.nav-coalesce(fp1.nav,100))/coalesce(fp1.nav,100)*100 end) 'Aug',
		sum(case when datepart(month,fp.MonthDate) = 9 then (fp.nav-coalesce(fp1.nav,100))/coalesce(fp1.nav,100)*100 end) 'Sep',
		sum(case when datepart(month,fp.MonthDate) = 10 then (fp.nav-coalesce(fp1.nav,100))/coalesce(fp1.nav,100)*100 end) 'Oct',
		sum(case when datepart(month,fp.MonthDate) = 11 then (fp.nav-coalesce(fp1.nav,100))/coalesce(fp1.nav,100)*100 end) 'Nov',
		sum(case when datepart(month,fp.MonthDate) = 12 then (fp.nav-coalesce(fp1.nav,100))/coalesce(fp1.nav,100)*100 end) 'Dec'
	 from fundperformance fp left join fundperformance fp1 on fp.monthdate = dateadd(m,+1,fp1.monthdate)
	group by cast(datepart(year,fp.monthdate) as char(4))+ ' % Return'

but I do not have the Ytd column - and I am a bit stuck with this one.. Also is there an easier way to do this..

jrogers
Starting Member

34 Posts

Posted - 2008-03-31 : 07:09:40

I have a solution, but it is a bit messy..

	Select 
		 [Year], Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, [Dec],ytd.Returns Ytd

	from
    (select 
		cast(datepart(year,fp.monthdate) as char(4)) + ' Nav' 'Year',
		sum(case when datepart(month,fp.MonthDate) = 1 then fp.NAV end) 'Jan',
		sum(case when datepart(month,fp.MonthDate) = 2 then fp.NAV end) 'Feb',
		sum(case when datepart(month,fp.MonthDate) = 3 then fp.NAV end) 'Mar',
		sum(case when datepart(month,fp.MonthDate) = 4 then fp.NAV end) 'Apr',
		sum(case when datepart(month,fp.MonthDate) = 5 then fp.NAV end) 'May',
		sum(case when datepart(month,fp.MonthDate) = 6 then fp.NAV end) 'Jun',
		sum(case when datepart(month,fp.MonthDate) = 7 then fp.NAV end) 'Jul',
		sum(case when datepart(month,fp.MonthDate) = 8 then fp.NAV end) 'Aug',
		sum(case when datepart(month,fp.MonthDate) = 9 then fp.NAV end) 'Sep',
		sum(case when datepart(month,fp.MonthDate) = 10 then fp.NAV end) 'Oct',
		sum(case when datepart(month,fp.MonthDate) = 11 then fp.NAV end) 'Nov',
		sum(case when datepart(month,fp.MonthDate) = 12 then fp.NAV end) 'Dec'
	 from fundperformance fp left join fundperformance fp1 on fp.monthdate = dateadd(m,+1,fp1.monthdate)
	group by cast(datepart(year,fp.monthdate) as char(4))+ ' Nav'
	union ALL
	select 
		cast(datepart(year,fp.monthdate) as char(4)) + ' % Return' 'Year',
		sum(case when datepart(month,fp.MonthDate) = 1 then (fp.nav-coalesce(fp1.nav,100))/coalesce(fp1.nav,100)*100  end) 'Jan',
		sum(case when datepart(month,fp.MonthDate) = 2 then (fp.nav-coalesce(fp1.nav,100))/coalesce(fp1.nav,100)*100 end) 'Feb',
		sum(case when datepart(month,fp.MonthDate) = 3 then (fp.nav-coalesce(fp1.nav,100))/coalesce(fp1.nav,100)*100 end) 'Mar',
		sum(case when datepart(month,fp.MonthDate) = 4 then (fp.nav-coalesce(fp1.nav,100))/coalesce(fp1.nav,100)*100 end) 'Apr',
		sum(case when datepart(month,fp.MonthDate) = 5 then (fp.nav-coalesce(fp1.nav,100))/coalesce(fp1.nav,100)*100 end) 'May',
		sum(case when datepart(month,fp.MonthDate) = 6 then (fp.nav-coalesce(fp1.nav,100))/coalesce(fp1.nav,100)*100 end) 'Jun',
		sum(case when datepart(month,fp.MonthDate) = 7 then (fp.nav-coalesce(fp1.nav,100))/coalesce(fp1.nav,100)*100 end) 'Jul',
		sum(case when datepart(month,fp.MonthDate) = 8 then (fp.nav-coalesce(fp1.nav,100))/coalesce(fp1.nav,100)*100 end) 'Aug',
		sum(case when datepart(month,fp.MonthDate) = 9 then (fp.nav-coalesce(fp1.nav,100))/coalesce(fp1.nav,100)*100 end) 'Sep',
		sum(case when datepart(month,fp.MonthDate) = 10 then (fp.nav-coalesce(fp1.nav,100))/coalesce(fp1.nav,100)*100 end) 'Oct',
		sum(case when datepart(month,fp.MonthDate) = 11 then (fp.nav-coalesce(fp1.nav,100))/coalesce(fp1.nav,100)*100 end) 'Nov',
		sum(case when datepart(month,fp.MonthDate) = 12 then (fp.nav-coalesce(fp1.nav,100))/coalesce(fp1.nav,100)*100 end) 'Dec'
	 from fundperformance fp left join fundperformance fp1 on fp.monthdate = dateadd(m,+1,fp1.monthdate)
	group by cast(datepart(year,fp.monthdate) as char(4))+ ' % Return') PerformanceTable
	left join
	(select 
	datepart(year,fp.monthdate) NavDate,
	sum((fp.nav-fp1.nav)/fp1.nav*100) returns
from fundperformance fp inner join 
		(select max(monthdate) EndDate
		from fundperformance
		group by datepart(year,monthdate)) fpMax on fp.monthdate = fpmax.enddate ,
	fundperformance fp1 inner join 
		(select monthdate  StartDate
		from fundperformance
		where datepart(Month,monthdate)=12) fpmin on fp1.monthdate = fpmin.StartDate
where datepart(year,fp.monthdate) = datepart(year,fp1.monthdate)+1
group by
datepart(year,fp.monthdate)) YTD on cast(ytd.navdate as char(4)) + ' % Return' = performancetable.[Year]
	where substring([year],1,4) <>2004
order by substring([year],1,6)

I had to 'cheet' by entering a value into the fundperformance for dec 2004, so that my SQL for YTD performace would work..

If anyone has any comments which could help me inmprove this I would be most gratefull

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources