April 24, 2012 at 8:05 am
if(@qtd is null)
begin
---------------------------------------------------------
------quatertodate---------------
declare @qtd1 float
declare @maxdate1 datetime
select @maxdate1 = MAX(PeriodThrudate) from fPerformanceHistoryDetail(@ReportData1)
-- select @FromQTD as quatertodate
if(@maxdate1 >= @fromQTD)
begin
select @FromQTD = @maxdate1
end
select @FromQTD as max
-- select @FromQTD as qtd
-- select @maxdate
EXEC pPerformance
@ReportData = @ReportData4 out,
@Portfolios = @Portfolio,
@FromDate = @fromQTD,
@ToDate = @ToDate,
@ClassificationID = -9
select @qtd = IRR from fPerformance(@ReportData4)
select @qtd
end
INSERT INTO #temp5
SELECT @Portfoliobaseid,@qtd
after this i am getting same value in qtd, the last one only.
how to get each value of qtd
help?
April 24, 2012 at 8:45 am
What do you mean by "getting same value in qtd"?
You're setting and selecting single @qtd and you're getting the one selected!
You don't have any loop to expect "each value".
April 24, 2012 at 8:46 am
got it
thanks
April 24, 2012 at 8:47 am
riya_dave (4/24/2012)
if(@qtd is null)begin
---------------------------------------------------------
------quatertodate---------------
declare @qtd1 float
declare @maxdate1 datetime
select @maxdate1 = MAX(PeriodThrudate) from fPerformanceHistoryDetail(@ReportData1)
-- select @FromQTD as quatertodate
if(@maxdate1 >= @fromQTD)
begin
select @FromQTD = @maxdate1
end
select @FromQTD as max
-- select @FromQTD as qtd
-- select @maxdate
EXEC pPerformance
@ReportData = @ReportData4 out,
@Portfolios = @Portfolio,
@FromDate = @fromQTD,
@ToDate = @ToDate,
@ClassificationID = -9
select @qtd = IRR from fPerformance(@ReportData4)
select @qtd
end
INSERT INTO #temp5
SELECT @Portfoliobaseid,@qtd
after this i am getting same value in qtd, the last one only.
how to get each value of qtd
help?
Points here:
1) You have 1 insert statement without a loop - how many rows do you expect???
2) This is a subset of the other post code. Just because the you did not get your work done for you in the other post why do you believe openng a new post will help.
3) Same as your last 3 posts - give the full unedited DDL for your procedure including the DDL for all called functions, stored procedures, tables used. Then add sample data (yes I know your work is super-secret and even non-realistic sample data is a problem). The add the result you want in the form of a table with DDL and data.
4) Follow all the links that you are seeming to ignore regularly from forum member such as Lynn, Gail and Jeff (sorry all the other I missed out) on how to ask questions and provide DDL
5) Get some training. Asking a forum for the answer will not cover that you are out of your depth with this product.
Rant over
Fitz
--------
Reference any post by Lynn Pettis (yes those you have been ignoring)
For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs
--------------------------------------------------------------------------------
April 24, 2012 at 8:52 am
You can find more of the code here: http://www.sqlservercentral.com/Forums/Topic1286816-392-1.aspx#bm1288517.
The code above is an excerpt from the code in the other thread.
Instead of fragmenting your questions all over the place, why not just stick to one thread? Your questions are all related to same piece of code.
April 24, 2012 at 10:19 am
my select statement
insert into #temp7
select a.shortname , a.portfoliobaseid , a.account ,a.InceptionDate,
a.DTDTWR,
MTDTWR = case when (a.MTDTWR is null) then
(exp(sum(LOG(CASE WHEN b.monthirr = 0.0 THEN 1 WHEN b.monthirr IS NULL THEN 1 WHEN b.monthirr = -100 THEN 1 ELSE 1+b.monthirr/100 END)))-1)* 100
else a.MTDTWR
end,
YTDTWR = case when (d.YTDTWR is null) then
(exp(sum(LOG(CASE WHEN c.yearirr = 0.0 THEN 1 WHEN c.yearirr IS NULL THEN 1 WHEN c.yearirr = -100 THEN 1 ELSE 1+c.yearirr/100 END)))-1)* 100
else d.YTDTWR
end
from #temp1 a
left join #temp3 b on b.portfoliobaseid = a.portfoliobaseid
join #temp4 c on c.portfoliobaseid = a.portfoliobaseid
my values of mtd is chagning if i join with join #temp4 c on c.portfoliobaseid = a.portfoliobaseid
how to avoid that?
April 25, 2012 at 2:53 am
The values keep changing because of the JOIN itself. The values must be getting filtered further because of the JOIN with #table4.
If you want all the data from the result set before the JOIN and from #table4 then you need to use a Full Outer Join.
April 25, 2012 at 3:06 am
riya_dave (4/24/2012)
my select statementinsert into #temp7
select a.shortname , a.portfoliobaseid , a.account ,a.InceptionDate,
a.DTDTWR,
MTDTWR = case when (a.MTDTWR is null) then
(exp(sum(LOG(CASE WHEN b.monthirr = 0.0 THEN 1 WHEN b.monthirr IS NULL THEN 1 WHEN b.monthirr = -100 THEN 1 ELSE 1+b.monthirr/100 END)))-1)* 100
else a.MTDTWR
end,
YTDTWR = case when (d.YTDTWR is null) then
(exp(sum(LOG(CASE WHEN c.yearirr = 0.0 THEN 1 WHEN c.yearirr IS NULL THEN 1 WHEN c.yearirr = -100 THEN 1 ELSE 1+c.yearirr/100 END)))-1)* 100
else d.YTDTWR
end
from #temp1 a
left join #temp3 b on b.portfoliobaseid = a.portfoliobaseid
join #temp4 c on c.portfoliobaseid = a.portfoliobaseid
my values of mtd is chagning if i join with join #temp4 c on c.portfoliobaseid = a.portfoliobaseid
how to avoid that?
Riya_Dave,
I have not seen this done before so please can you enlighten me as to what this does?
(exp(sum(LOG(CASE WHEN b.monthirr = 0.0 THEN 1 WHEN b.monthirr IS NULL THEN 1 WHEN b.monthirr = -100 THEN 1 ELSE 1+b.monthirr/100 END)))-1)* 100
Fitz
April 25, 2012 at 8:09 am
Mark Fitzgerald-331224 (4/25/2012)
riya_dave (4/24/2012)
my select statementinsert into #temp7
select a.shortname , a.portfoliobaseid , a.account ,a.InceptionDate,
a.DTDTWR,
MTDTWR = case when (a.MTDTWR is null) then
(exp(sum(LOG(CASE WHEN b.monthirr = 0.0 THEN 1 WHEN b.monthirr IS NULL THEN 1 WHEN b.monthirr = -100 THEN 1 ELSE 1+b.monthirr/100 END)))-1)* 100
else a.MTDTWR
end,
YTDTWR = case when (d.YTDTWR is null) then
(exp(sum(LOG(CASE WHEN c.yearirr = 0.0 THEN 1 WHEN c.yearirr IS NULL THEN 1 WHEN c.yearirr = -100 THEN 1 ELSE 1+c.yearirr/100 END)))-1)* 100
else d.YTDTWR
end
from #temp1 a
left join #temp3 b on b.portfoliobaseid = a.portfoliobaseid
join #temp4 c on c.portfoliobaseid = a.portfoliobaseid
my values of mtd is chagning if i join with join #temp4 c on c.portfoliobaseid = a.portfoliobaseid
how to avoid that?
Riya_Dave,
I have not seen this done before so please can you enlighten me as to what this does?
(exp(sum(LOG(CASE WHEN b.monthirr = 0.0 THEN 1 WHEN b.monthirr IS NULL THEN 1 WHEN b.monthirr = -100 THEN 1 ELSE 1+b.monthirr/100 END)))-1)* 100
Fitz
exp(sum(log())) is an aggregate product function programed in tsql built in functions. why the case and the the -1)*100 would be his internal business logic.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
April 25, 2012 at 8:42 am
thanks got answer
April 26, 2012 at 12:26 am
April 26, 2012 at 1:49 am
vinu512 (4/26/2012)
What was the answer??....:w00t:
4th or 5th OP post that has been "solved". The rest were coding errors so probably the join as was pointed out in the suggestions.:-)
Table1 left join Table2 join Table3
So more rows add due to 1:M join or Table3 did not have all the rows from Table1 whatever.
Fitz
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply