April 19, 2012 at 7:20 pm
creaate table #temp1(account,
MTD float,
ytd float)
insert into #temp1
select account ,mtd,ytd from
account a join fhistory b
on a.accountid = b.accountid
result of temp1
account mtd ytd
1001 23.45 34.32
1003 34.56 43.21
1003 null null.
now for the null value i am using cursor and getting values fro null in temp2#
so for this example ,#temp2
1003 45.32 45.32
now i need to add this data to third #temp3
so
create table #temp3(account,
MTD float,
ytd float)
but in temp3,i need to perform calculation for MTD and ytd
so my calculation will be ,what dat i get from #temp2, so for MTD 45.32 + i (i is the data i getting from another function
so it would be like 45.32 + i1 (this will come from account1(@p1)
and i need to get all this data in temp3
insert into #temp3
select account from #temp1
for MTD it it is null then 45.32 + i1 else MTD,
same for YTD
from #temp1,#temp2,account1(@p1)
#temp1 and #temp2 have 1 common column,but account1(@p1) doesnt have any common column
how do combine all this in temp3
April 19, 2012 at 8:02 pm
You do know that all you are doing is fragmenting any responses you may get to solve your problem for one. Second, you still haven't figured out how to post all the information we need to provide you with the help you are seeking.
Please, read the first article I reference below in my signature block and post everything needed to help you.
April 19, 2012 at 8:25 pm
i have given all the information here ,nothing is left
April 19, 2012 at 8:30 pm
No you haven't. Nothing you have given is going to help us help yopu.
Please, tell me why you won't read that article and follow the instructions it provides?
April 19, 2012 at 9:31 pm
Lynn, i have seen your post to every thread,instead of helping , you just posting your stranded one to everybody.
thats so annoying . and i dont except any response from you,so stay away
April 19, 2012 at 9:38 pm
riya_dave (4/19/2012)
Lynn, i have seen your post to every thread,instead of helping , you just posting your stranded one to everybody.thats so annoying . and i dont except any response from you,so stay away
ok so ill say the same thing that every one else will post the sample data and create table statements the code of your stored procedures functions and any thing else you would need to do what ever it is you are so incoherently trying to get help with.
and dont insult lynn. you will just tick people off (Like me) and we wont help you (like me)
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 19, 2012 at 9:55 pm
I don't quite understand what you are looking to achieve.
Can you post samples of what the data should look like after each table insert?
Do you have sample data that we can start with?
I may have missed it for temp2 and 3, but what is the table create statement for each of those?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 19, 2012 at 10:31 pm
riya_dave (4/19/2012)
Lynn, i have seen your post to every thread,instead of helping , you just posting your stranded one to everybody.thats so annoying . and i dont except any response from you,so stay away
You know, I am trying to get you to provide the information we need to understand your problem so that we can help you solve it. You have continued to show that you are unwilling to help us help you, so fine, I will leave you to try and solve your problem on your own.
April 20, 2012 at 4:13 am
I'm trying to understand, but I just don't get what you're doing. I'm sorry.
How do you get from this result set:
1001 23.45 34.32
1003 34.56 43.21
1003 null null.
To this result set:
1003 45.32 45.32
I just don't see where those numbers are coming from. I don't even begin to understand what the rest of the issue is. I'm very sorry.
Just a suggestion, maybe if you took that exact post and tried it out on stackoverflow.com. It's a very different web site with different groups of people answering. They are absolutely discouraged from asking clarifying questions there, so maybe you'll get someone who understands what it is you're trying to do and will supply you with a solution rather than all these questions from people who don't understand what you're trying to do.
I'm sorry. I just don't get what it is you're doing, so this is my best suggestion to help out.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 20, 2012 at 4:23 am
Ask yourself how you would cope, as a professional SQL Server developer, if you were given the content of your first post as a job specification.
It's awful.
What you are attempting to achieve is almost certainly simple but the crude and sketchy description is so poor that the good hard-working folks here are baffled.
Learn to put a little effort into your specification, it will pay back in spades.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
April 20, 2012 at 6:24 am
here you go
create table #temp1(account int,
MTD float,
ytd float)
insert into #temp1 values(
1001 ,23.45, 34.32,
1003 ,34.56, 43.2,
1003 null, 32.21,
1003 56.76 null)
temp#1
1001 ,23.45, 34.32,
1003 ,34.56, 43.2,
1003 null, 32.21,
1004 56.76 null
now in #table2
i will have values for only null
#temp2
account m1 y1
1003 21.22
1004 - 31.30
now in table 3
creaate table #temp3(account int,
MTD float,
ytd float)
insert into #table3
account = select account from #temp1
----------------------------------------------
MTD = (if is null) then
m1(from #temp2) + i1(from account(p1)(udf which doesnt have any common key with temp1 and temp2)
end
else
select mtd from temp1
---------------
same for ytd
------------------
from #temp1,#temp2 and account(p1)
-----------------------------------------------
April 20, 2012 at 6:48 am
finally we are close to answering one question, can you post your DDL for #Temp2. im not sure how it relates to 1003 and 1004 in temp1. should it fill both nulls or only the null in mtd (1003).
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 20, 2012 at 7:09 am
create table #temp2(account int,
MTD float,
ytd float)
its account in temp1 that is same in temp2
if any value is null for mtd or ytd it will fill
April 20, 2012 at 7:17 am
riya_dave (4/20/2012)
create table #temp2(account int,MTD float,
ytd float)
its account in temp1 that is same in temp2
if any value is null for mtd or ytd it will fill
Does this help from your other thread? I managed to gleen some information from this thread.
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, mtd, qtd, ytd from #temp1
open c1
FETCH NEXT FROM c1 INTO @Portfolio, @mtd2, @qtd, @ytd
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 = @Portfolio,
@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 = @Portfolio,
@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 next from c1 into @Portfolio, @mtd2, @qtd, @ytd
end
close c1
DEALLOCATE c1
April 20, 2012 at 7:19 am
ok something like this may work:
UPDATE #Temp1 SET t1.mtd = ISNULL(t1.mtd, t2.mtd), t1.ytd = ISNULL(t1.ytd,t2.ytd)
FROM #Temp1 t1
LEFT JOIN #Temp2 t2
ON t1.id = t2.id
WHERE t1.mtd IS NULL
OR t1.ytd IS NULL
now do you see what happens when you get back to us with the information we requested. you actually get answers.
EDIT: Lynn that actually looks coherent.
to the OP what ever your name is, now do you see why starting multiple threads is bad. you fragmented your information so much im guessing lynn had to comb through all of your disjointed threads to figure that out.
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]
Viewing 15 posts - 1 through 15 (of 40 total)
You must be logged in to reply to this topic. Login to reply