April 19, 2012 at 12:46 pm
this is my ddl
create table #temp2(account nvarchar(32),
MTDTWR float,
QTDTWR FLOAT,
YTDTWR FLOAT)
this is data i am getting
accountMTDTWRQTDTWRYTDTWR
case-0.8755148870.5430860920.784486036
circle-0.8755148870.5430860920.784486036
hargrov-0.8755148870.5430860920.784486036
hurwitz-0.8755148870.5430860920.784486036
usbal1-0.8755148870.5430860920.784486036
the data should be unique in each field,what else u need let me know
April 19, 2012 at 12:50 pm
riya_dave (4/19/2012)
this is my ddlcreate table #temp2(account nvarchar(32),
MTDTWR float,
QTDTWR FLOAT,
YTDTWR FLOAT)
this is data i am getting
accountMTDTWRQTDTWRYTDTWR
case-0.8755148870.5430860920.784486036
circle-0.8755148870.5430860920.784486036
hargrov-0.8755148870.5430860920.784486036
hurwitz-0.8755148870.5430860920.784486036
usbal1-0.8755148870.5430860920.784486036
the data should be unique in each field,what else u need let me know
We need you to follow the instructions in the dead horse article we keep telling you to read.
April 19, 2012 at 1:10 pm
thats what i did right now,i removed variavel i declare inside cursor,sill not working
my question can understand by whatever i have posted
April 19, 2012 at 1:20 pm
riya_dave (4/19/2012)
thats what i did right now,i removed variavel i declare inside cursor,sill not workingmy question can understand by whatever i have posted
What you posted is partial code. It is full of syntax errors. The "code" you posted will not compile. If it won't compile, I don't think you will get the results you are looking for.
It should be obvious at this point that it is not clear by whatever you have posted. If it was obvious you would have had an answer 3 dozen posts ago. Yes there is a language barrier but that is a small barrier. You on the other hand continue to be the barrier to your solution. You are the one and only thing standing between you and a vast depth of sql knowledge from people willing and wanting to share. Swallow your pride and post something useful. You have so many useless posts it would be nice to see you make an effort, just once, at posting something that is clear and concise.
_______________________________________________________________
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/
April 19, 2012 at 1:29 pm
ok.
this is my complete code
declare @Portfolio nvarchar(32)
declare @mtd2 float
DECLARE @qtd float
declare @maxdate datetime
select @maxdate = MAX(PeriodThrudate) from fPerformanceHistoryDetail(@i1)
declare @ytd float
declare c1 CURSOR FOR
select account from #temp1
open c1
FETCH NEXT FROM c1 INTO @Portfolio
WHILE @@FETCH_STATUS = 0
begin
--------month----------
if(@mtd2 IS NULL )
begin
EXEC pPerformance
@ReportData = @i3 out,
@Portfolios = @Portfolios,
@FromDate = @fromMTD,
@ToDate = @ToDate
select @mtd2 = IRR from APXUSER.fPerformance(@i3)
select @mtd2 last1month
end
if(@ytd is null)
begin
EXEC pPerformance
@ReportData = @ReportData3 out,
@Portfolios = @Portfolios,
@FromDate = @maxdate,
@ToDate = @ToDate
select @ytd = IRR from APXUSER.fPerformance(@i3)
select @ytd as y21
end
---------------------------------------------------------
------quatertodate---------------
if(@qtd is null)
begin
declare @maxdate1 datetime
select @maxdate1 = MAX(PeriodThrudate) from fPerformanceHistoryDetail(@i1)
if(@maxdate1 >= @fromQTD)
begin
select @FromQTD = @maxdate1
end
-- -- select @maxdate
EXEC pPerformance
@ReportData = @i3 out,
@Portfolios = @Portfolios,
@FromDate = @fromQTD,
@ToDate = @ToDate
select @qtd = IRR from fPerformance(@i3)
select @qtd as qtd1
end
INSERT INTO #temp2(account,MTDTWR,QTDTWR,YTDTWR) values(@Portfolio,@mtd2,@qtd,@ytd)
fetch previous from c1 into @Portfolio
end
close c1
DEALLOCATE c1
why i am getting same value in all the field
April 19, 2012 at 1:41 pm
Reformatted your code (see how much easier it is to read?):
declare @Portfolio nvarchar(32)
declare @mtd2 float
DECLARE @qtd float
declare @maxdate datetime
select @maxdate = MAX(PeriodThrudate) from fPerformanceHistoryDetail(@i1)
declare @ytd float
declare c1 CURSOR FOR
select account from #temp1
open c1
FETCH NEXT FROM c1 INTO @Portfolio
WHILE @@FETCH_STATUS = 0
begin
--------month----------
if(@mtd2 IS NULL )
begin
EXEC pPerformance
@ReportData = @i3 out,
@Portfolios = @Portfolios,
@FromDate = @fromMTD,
@ToDate = @ToDate
select @mtd2 = IRR from APXUSER.fPerformance(@i3)
select @mtd2 last1month
end
if(@ytd is null)
begin
EXEC pPerformance
@ReportData = @ReportData3 out,
@Portfolios = @Portfolios,
@FromDate = @maxdate,
@ToDate = @ToDate
select @ytd = IRR from APXUSER.fPerformance(@i3)
select @ytd as y21
end
---------------------------------------------------------
------quatertodate---------------
if(@qtd is null)
begin
declare @maxdate1 datetime
select @maxdate1 = MAX(PeriodThrudate) from fPerformanceHistoryDetail(@i1)
if(@maxdate1 >= @fromQTD)
begin
select @FromQTD = @maxdate1
end
-- -- select @maxdate
EXEC pPerformance
@ReportData = @i3 out,
@Portfolios = @Portfolios,
@FromDate = @fromQTD,
@ToDate = @ToDate
select @qtd = IRR from fPerformance(@i3)
select @qtd as qtd1
end
INSERT INTO #temp2(account,MTDTWR,QTDTWR,YTDTWR) values(@Portfolio,@mtd2,@qtd,@ytd)
fetch previous from c1 into @Portfolio
end
close c1
DEALLOCATE c1
Everything isn't here though.
What is the definition of the table #temp1?
What is some sample data to populate this table?
What is the table valued function: APXUSER.fPerformance?
What is the DDL (CREATE FUNCTION statement) for it?
What table(s) does it query?
What are their definitions and some sample data for them?
What is the DDL for this procedure: pPerformance?
What is the table valued function: fPerformanceHistoryDetail?
What is the DDL (CREATE FUNCTION statement) for it?
What table(s) does it query?
What are their definitions and some sample data for them?
What is the table valued function: fPerformance?
Is it different than this one: APXUSER.fPerformance?
What is the DDL (CREATE FUNCTION statement) for it?
What table(s) does it query?
What are their definitions and some sample data for them?
I have just asked you 16 questions that need answering. You have only given us the tip, a very small tip, of great big iceberg. We need the iceberg.
April 19, 2012 at 1:42 pm
Because your "if" conditions don't make any sense. You declare them outside the cursor (this is good). Then you set all 3 of them the first trip through your cursor. Probably just remove the "if" checks.
I won't even go into the semantics again that there is absolutely no need for a cursor here. You could accomplish this whole mess in a single easy to read select statement.
I also would be remiss if I did not mention "fetch previous"???? What is that???
_______________________________________________________________
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/
April 19, 2012 at 1:46 pm
In addition you declare the following, but never populate them unitl after you use them inside your loop as part of an IF condition:
declare @mtd2 float
DECLARE @qtd float
declare @ytd float
April 19, 2012 at 1:52 pm
i ma getting values in all three variables from temp1
April 19, 2012 at 1:54 pm
riya_dave (4/19/2012)
i ma getting values in all three variables from temp1
Yes they get set the first pass through, then they are not null so none of your code will execute more than once. Get rid of the if checks, they don't make sense.
_______________________________________________________________
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/
April 19, 2012 at 1:54 pm
give me sample example,how to get rid
April 19, 2012 at 1:56 pm
do you know what an if statement is? Remove them. They are not needed.
_______________________________________________________________
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/
April 19, 2012 at 1:56 pm
they are null in temp1
April 19, 2012 at 1:58 pm
riya_dave (4/19/2012)
they are null in temp1
What???? Do you mean there are columns in temp1 for the variables you are checking for null???
_______________________________________________________________
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/
April 19, 2012 at 1:59 pm
riya_dave (4/19/2012)
they are null in temp1
Your point? The only thing you are pulling from #temp1 is what appears to be an Account. Nothing else.
Viewing 15 posts - 46 through 60 (of 67 total)
You must be logged in to reply to this topic. Login to reply