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
Go to Top of Page

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.
Go to Top of Page

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
Go to Top of Page

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.
Go to Top of Page

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
Go to Top of Page

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?
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -