correct results - but want to change display

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 2008 Forums

Analysis Server and Reporting Services (2008)

correct results - but want to change display

Author

Topic

DanielS
Starting Member

32 Posts

Posted - 2013-11-30 : 04:50:51

Hello all, my set of code produces these results. The actual figures are correct, but I would ideally like them all to be displayed in the one row, so the 1M, 3M and 6M results in row 2013-10-31.

PORTFOLIOTO_DATE B_RETURN B_RETURN_1M B_RETURN_3M B_RETURN_6M
EMXXX 2013-04-30 NULL NULL NULL 1.372
EMXXX 2013-07-31 NULL NULL 1.382 NULL
EMXXX 2013-09-30 NULL 1.393 NULL NULL
EMXXX 2013-10-31 1.399 NULL NULL NULL

declare @current date,
@prior_1m date,
@prior_3m date,
@prior_6m date

select @current = '2013-10-31',
@prior_1m = dateadd(month, datediff(month, 0, @current), -1),
@prior_3m = dateadd(month, datediff(month, 0, @current)-2, -1),
@prior_6m = dateadd(month, datediff(month, 0, @current)-5, -1)

select PORTFOLIO,
TO_DATE,
B_RETURN = max(case when TO_DATE = @current then B_RETURN end),
B_RETURN_1M = max(case when TO_DATE = @prior_1m then B_RETURN end),
B_RETURN_3M = max(case when TO_DATE = @prior_3m then B_RETURN end),
B_RETURN_6M = max(case when TO_DATE = @prior_6m then B_RETURN end)

from
(
select PORTFOLIO,
TO_DATE,
DAILY_RETURN,
1+DAILY_RETURN as A_RETURN,
B_RETURN
from PortfolioReturnsDaily A
CROSS APPLY
(
select EXP(SUM(LOG(1+DAILY_RETURN))) as B_RETURN
from PortfolioReturnsDaily x
where x.PORTFOLIO = A.PORTFOLIO
and x.TO_DATE <= A.TO_DATE

) B
) C
where PORTFOLIO = 'EMKARF'
and TO_DATE <= @current
and TO_DATE in (@current, @prior_1m, @prior_3m, @prior_6m)
group by PORTFOLIO, TO_DATE
order by PORTFOLIO, TO_DATE

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-30 : 05:00:50

[code]
declare @current date,
@prior_1m date,
@prior_3m date,
@prior_6m date

select @current = '2013-10-31',
@prior_1m = dateadd(month, datediff(month, 0, @current), -1),
@prior_3m = dateadd(month, datediff(month, 0, @current)-2, -1),
@prior_6m = dateadd(month, datediff(month, 0, @current)-5, -1)

select PORTFOLIO,
TO_DATE=max(case when TO_DATE = @current then TO_DATE end),
B_RETURN = max(case when TO_DATE = @current then B_RETURN end),
B_RETURN_1M = max(case when TO_DATE = @prior_1m then B_RETURN end),
B_RETURN_3M = max(case when TO_DATE = @prior_3m then B_RETURN end),
B_RETURN_6M = max(case when TO_DATE = @prior_6m then B_RETURN end)

from
(
select PORTFOLIO,
TO_DATE,
DAILY_RETURN,
1+DAILY_RETURN as A_RETURN,
B_RETURN
from PortfolioReturnsDaily A
CROSS APPLY
(
select EXP(SUM(LOG(1+DAILY_RETURN))) as B_RETURN
from PortfolioReturnsDaily x
where x.PORTFOLIO = A.PORTFOLIO
and x.TO_DATE <= A.TO_DATE

) B
) C
where PORTFOLIO = 'EMKARF'
and TO_DATE <= @current
and TO_DATE in (@current, @prior_1m, @prior_3m, @prior_6m)
group by PORTFOLIO
order by PORTFOLIO
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

DanielS
Starting Member

32 Posts

Posted - 2013-11-30 : 05:06:41

Aha, I see, you've just taken out the TO_DATE from the select and the group/order statements. Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-30 : 05:07:32

quote:
Originally posted by DanielS

Aha, I see, you've just taken out the TO_DATE from the select and the group/order statements. Thanks.

yep and if you need current date also to be displayed see the modified query

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

DanielS
Starting Member

32 Posts

Posted - 2013-11-30 : 05:10:56

And is there a trick when copying the code into SSRS? Works fine in SQL Mgt Studio, but I get an error when copying across.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-30 : 05:15:27

yep..make it a proc and call it in SSRS
ie like


CREATE PROC GetData
@current	 date
AS
declare	
@prior_1m	 date,
@prior_3m	 date,
@prior_6m	 date

select	@prior_1m = dateadd(month, datediff(month, 0, @current), -1),
@prior_3m = dateadd(month, datediff(month, 0, @current)-2, -1),
@prior_6m = dateadd(month, datediff(month, 0, @current)-5, -1)


select PORTFOLIO,
TO_DATE,
B_RETURN = max(case when TO_DATE = @current then B_RETURN end),
B_RETURN_1M = max(case when TO_DATE = @prior_1m then B_RETURN end),
B_RETURN_3M = max(case when TO_DATE = @prior_3m then B_RETURN end),
B_RETURN_6M = max(case when TO_DATE = @prior_6m then B_RETURN end)

from
(	
select	PORTFOLIO, 
TO_DATE,	
DAILY_RETURN,
1+DAILY_RETURN as A_RETURN, 
B_RETURN
from PortfolioReturnsDaily A
CROSS APPLY
(
select	EXP(SUM(LOG(1+DAILY_RETURN))) as B_RETURN
from	PortfolioReturnsDaily x
where	x.PORTFOLIO	 = A.PORTFOLIO
and	x.TO_DATE	<= A.TO_DATE

) B
) C	
where PORTFOLIO = 'EMKARF'
and TO_DATE <= @current
and TO_DATE in (@current, @prior_1m, @prior_3m, @prior_6m)
group by PORTFOLIO, TO_DATE
order by PORTFOLIO, TO_DATE
GO

Then in SSRS call it as

EXEC GetData @current = '2013-10-31'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

DanielS
Starting Member

32 Posts

Posted - 2013-11-30 : 05:46:41

Cool, Thank you.

Another question on converting data. Is there a way of getting data into a matrix style format. For example, if I have

Date Data
Jan-11 1
Feb-11 3
Mar-11 -2
...etc
Jan-12 4
Feb-12 -1
Mar-12 3
...etc

But want it displayed as
Year Jan Feb Mar etc...
2011 1 3 -2
2012 4 -1 3

What sort of code does something like this?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-01 : 04:00:31

yep possible
use PIVOT operator

http://blogs.msdn.com/b/craigfr/archive/2007/07/03/the-pivot-operator.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources