March 27, 2014 at 7:09 am
hello all
I have a function something like this ...
create Function fGetIndexReturn_sma_ver2(@Portfolios nvarchar(max), @ReportingCurrencyCode varchar(2), @StartDate datetime, @EndDate datetime, @RowNo Nvarchar(12))
returns @IndexReturnTable table (PortfolioBaseID [int] NULL,
IndexName [nvarchar](255) NULL,
IndexDesc [nvarchar](255) NULL,
Fisindex [int] Null,
IndexRate [float] NULL,
MTD[nvarchar](255) NULL,
QTD[nvarchar](255) NULL,
YTD[nvarchar](255) NULL,
ThreeYTD[nvarchar](255) NULL,
FiveYTD[nvarchar](255) NULL,
MTDFromDate datetime,
QTDFromDate datetime,
YTDFromDate datetime,
ThreeYTDFromDate datetime,
FiveYTDFromDate datetime
)
with execute as caller
begin
insert @IndexReturnTable
SELECT DISTINCT
c.PortfolioBaseID
,m.IndexName
,m.IndexDesc
,1 as FisIndex
,1 AS RATE,1 AS MTD,0 as QTD,0 as YTD,0 as ThreeYTD, 0 as FiveYTD,DATEADD(MONTH, -1, @EndDate) as MTDFromDate,null,null,null,null
FROM
vPortfolioBaseCustomLabels C LEFT OUTER JOIN vMarketIndex m ON C.Value = m.IndexName
LEFT OUTER JOIN IndexRate sr on m.IndexID = sr.IndexID
LEFT OUTER JOIN tIndexRate er on m.IndexID = er.IndexID
Where
--C.Label = @RowNo
sr.AsOfDate = DATEADD(MONTH, -1, @EndDate)
AND er.AsOfDate =@EndDate
AND C.PortfolioBaseID = @portfolios
UNION
SELECT DISTINCT
c.PortfolioBaseID
,m.IndexName
,m.IndexDesc
,1 as FisIndex
,1 AS RATE,0 AS MTD,1 as QTD,0 as YTD,0 as ThreeYTD, 0 as FiveYTD,null, DATEADD(MONTH, -3, @EndDate) as QTDFromDate,null,null,null
FROM
vPortfolioBaseCustomLabels C LEFT OUTER JOIN vMarketIndex m ON C.Value = m.IndexName
LEFT OUTER JOIN IndexRate sr on m.IndexID = sr.IndexID
LEFT OUTER JOIN tIndexRate er on m.IndexID = er.IndexID
Where
--C.Label = @RowNo
sr.AsOfDate = DATEADD(MONTH, -1, @EndDate)
AND er.AsOfDate =@EndDate
AND C.PortfolioBaseID = @portfolios
union ... so on for ytd and 3yrs and 5 yrs
and i am getting a out put like below
pbaseidindnameinddescfindxratemtdqtdytd3ytdMTDFromDateqTDFromDateyTDFromDate ...so on
abcsp500xyz111000datenullnull
abcsp500xyz110100nulldatenull
abcsp500xyz110010nullnulldate
zyxsp900rtg111000datenullnull
zyxsp500rtg110100nulldatenull
zyxsp500rtg110010nullnulldate
i am trying to find a way to achive following result
pbaseidindnameinddescfindxratemtdqtdytd3ytdMTDFromDateqTDFromDateyTDFromDate ...so on
abcsp500xyz111111datedatedate
zyxsp900rtg111111datedatedate
i want to achieve a single line item for a given index and portfolio... please guide me in the right direction
thanx in advance .....
abhi
March 27, 2014 at 7:50 am
Hi and welcome to the forums. It looks like maybe you want a crosstab here. You can read about the technique by following the link in my signature.
If you need more specific coding help you should take a look at the first link in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 27, 2014 at 8:02 am
thanks sean ... i will give it a try .....
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply