April 20, 2012 at 7:36 am
INSERT #temp3 (account,MTD,ytd)
SELECT t1.account,
COALESCE(t1.MTD,t1.m1 + account(@p1)),
COALESCE(t1.ytd,t1.y1 + account(@p1))
FROM #temp1 t1
LEFT JOIN #temp2 t2 ON t2.account = t1.account
Far away is close at hand in the images of elsewhere.
Anon.
April 20, 2012 at 7:44 am
I am making an honest attempt to help out here.
First, I had to change your code because it won't run. Here are the updates:
CREATE TABLE #temp1 (
account INT,
MTD FLOAT,
ytd FLOAT
);
INSERTINTO #temp1
VALUES(1001, 23.45, 34.32),
(1003, 34.56, 43.2),
(1003, NULL, 32.21),
(1003, 56.76, NULL);
The results from #temp1 then look like this:
100123.4534.32
100334.5643.2
1003NULL32.21
100356.76NULL
There is no code for creating #temp2 and again, I have no understanding at all how you're arriving at the values that you're telling me need to be in #temp2. Again, my apologies for my lack of understanding, but you're not supplying enough information to make that part clear. So, I've substituted this simple code in order to have my #temp2 have the values that you say yours does:
CREATE TABLE #temp2 (account INT, mtd FLOAT);
INSERTINTO #temp2
(account, mtd)
VALUES(1003, 21.22),
(1004, 31.30);
I'm not going to bother creating a #temp3 because if I can write the appropriate SELECT statement, turning that into an INSERT statement is a trivial exercise. I'm taking the psuedo-code you have defined and I get, I think, this:
SELECT t1.account,
COALESCE(t1.mtd,(t2.mtd+i1(t1.account)))
from #temp1 AS t1
LEFT JOIN #temp2 AS t2
ON t1.account = t2.account;
But I can't possibly test it becaue i1 is blank.
Based on what you've supplied here, that has to be a viable answer. If it's not, again, apologies, but it's based on the information that you have provided. I've had to build my own T-SQL because yours won't work. And I've had to assume logical leaps because you're not providing them. But, based on what you've posted, this should be at least viable.
"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 7:45 am
David Burrows (4/20/2012)
INSERT #temp3 (account,MTD,ytd)
SELECT t1.account,
COALESCE(t1.MTD,t1.m1 + account(@p1)),
COALESCE(t1.ytd,t1.y1 + account(@p1))
FROM #temp1 t1
LEFT JOIN #temp2 t2 ON t2.account = t1.account
I agree. Based on what's provided, that seems about right.
"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 8:27 am
no.
account(@p1) is udf and i need to get value i1 from udf.
how can i join this ,it doesn have any common column
April 20, 2012 at 8:34 am
and this udf returns table,not scalar
April 20, 2012 at 8:52 am
riya_dave (4/20/2012)
and this udf returns table,not scalar
Not defined in the original sets of posts. See why so many people are having trouble understanding what's going on?
How about something like this:
JoinedTable.account,
COALESCE(JoinedTable.mtd1,(JoinedTable.mtd2+i1.VALUE)
(SELECT t1.account,
t1.mtd AS mtd1
t2.mtd AS mtd2
from #temp1 AS t1
LEFT JOIN #temp2 AS t2
ON t1.account = t2.account) AS JoinedTable
CROSS APPLY i1(JoinedTable.account) AS i1
;
The idea is to take the queries that have a clear join, the two temp tables, and make them into a single derived table, JoinedTable. Then, for each row in that derived table you apply the function. Should work. Again, as before, I can't test this for you because you haven't supplied sufficient code or structures for me to build out a viable, testable, T-SQL statement.
"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 10:20 am
INSERT #temp3 (account,MTD,ytd)
SELECT t1.account,
COALESCE(t1.MTD,t1.m1 + a.i1),
COALESCE(t1.ytd,t1.y1 + a.i1)
FROM #temp1 t1
LEFT JOIN #temp2 t2 ON t2.account = t1.account
CROSS APPLY account(@p1) AS a
Far away is close at hand in the images of elsewhere.
Anon.
April 20, 2012 at 10:47 am
ok.got solve,but i have another query.
i need to create 1 temp table
and i need to add whatever i got from temp2 and from account function.
so in temp2 i have column like
account , MTD,YTD.
from account function i have just i1,this i1 contains value for all the period .
i can do like this
create table #temp4(monthirr FLOAT)
INSERT INTO #temp4
select irrreal from account(i1) phd
union
select mtdtwr from #temp2
create table #temp5(ytdirr FLOAT)
INSERT INTO #temp5
select irrreal from account(i1) phd
union
select ytdtwr from #temp2
---------------
coz for mtd , I NEED TO ONLY ADD MTD values from table2
and i can add in my formula,by join each of table.
Is there any other way, just wondering
April 20, 2012 at 10:56 am
can i do something like this
create table #temp4(account INT,
monthirr FLOAT)
it gives syntax error
INSERT INTO #temp4
(select account ,(select i1 from account(p1) phd
union
select MTD from #temp2) as monthirr from #temp2)
April 21, 2012 at 2:44 am
riya_dave (4/20/2012)
can i do something like thiscreate table #temp4(account INT,
monthirr FLOAT)
it gives syntax error
INSERT INTO #temp4
(select account ,(select i1 from account(p1) phd
union
select MTD from #temp2) as monthirr from #temp2)
You want to see what happens when you provide all the information needed to answer a question, here is one example, http://www.sqlservercentral.com/Forums/Topic1287584-391-1.aspx.
Check it out.
April 21, 2012 at 4:27 am
Wait, what was the solution?
"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 23, 2012 at 9:04 am
i didnt get solution yet.
kindly help me
i want insert
insert into #temp3
select I1,account,id from account(p1)
union
select I1 from #temp2
i cant do this coz of union,what are other ways of i can add both I1 into temp3
April 23, 2012 at 9:09 am
riya_dave (4/23/2012)
i didnt get solution yet.kindly help me
i want insert
insert into #temp3
select I1,account,id from account(p1)
union
select I1 from #temp2
i cant do this coz of union,what are other ways of i can add both I1 into temp3
How about
SELECT I1,account,id from account(p1)
UNION
SELECT i1,null as account,null as id from #temp2
Remember that UNION is an aggregate function which means you'll get distinct values.
Also, just on a side note, asking additional questions within a single thread means that only those people who are currently following the thread will see your questions. For a new question, you'd be better off starting a new thread.
"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 23, 2012 at 9:12 am
thanks grant.i will start another thread,but this should work as well.
thanks a lot
April 23, 2012 at 1:47 pm
ok.this is simple question but nee dhelp.
i need something like untill there is value the loop should go.
i cant give any condition
suppose files
item1
item2
item3
the loop should work untill there is value
how to do that
Viewing 15 posts - 16 through 30 (of 40 total)
You must be logged in to reply to this topic. Login to reply