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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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
52326 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
52326 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
52326 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
52326 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  
 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.14 seconds. Powered By: Snitz Forums 2000