SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Analysis Server and Reporting Services (2008)
 correct results - but want to change display
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

DanielS
Starting Member

Australia
32 Posts

Posted - 11/30/2013 :  04:50:51  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 11/30/2013 :  05:00:50  Show Profile  Reply with Quote

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


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

Edited by - visakh16 on 11/30/2013 05:06:55
Go to Top of Page

DanielS
Starting Member

Australia
32 Posts

Posted - 11/30/2013 :  05:06:41  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 11/30/2013 :  05:07:32  Show Profile  Reply with Quote
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

Australia
32 Posts

Posted - 11/30/2013 :  05:10:56  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 11/30/2013 :  05:15:27  Show Profile  Reply with Quote
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

Australia
32 Posts

Posted - 11/30/2013 :  05:46:41  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 12/01/2013 :  04:00:31  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000