joining 2 tabel into third

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

creaate 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

Hi ssrsnew, I would like to help you out solving your problems. But I'm sorry to tell you that all your posts are getting too big and confusing.

In this threat I cant understand what you want and what are doing with temp3. Can you simplifie your text and tell us what you need to achieve?

I will post the solution I believe you need, based on what I've understood

WITH CTE
AS
(
	--Here I will get 1 row right?
	select SUM(MTD)+i total
	FROM #temp2
)
--Here you will get all rows from t1 right?
--if you want only account 1003 just remove "left"
select t1.account,CTE.total
  from #temp1 t1 left join #temp2 t2 on (t1.account=t2.account),CTE

ok.

 i am giving you example.

i have data in temp1

account mtd ytd

1002       34.56     23.21

1004        23.21      45.32

1007      null             56.43

1008           32.21      null

now here see null values for MTD and ytd

instead of null my final table would have formula like this(temp3)

if (mtd is null then)( m1 + i1)

else

mtd

now this m1 i am getting from temp2  so i have that values.i can join that with temp1 and insert into temp3

but for i1 ,i a, getting this value from udf,this function doesnt have any key common to temp1 and temp2.hhwo can i join with temp3

so my temp1 3 will be

insert into temp3

(select account ,

mtd= if this is null then (m1 + i1)(getting m1 from temp2 and i1 from udf), else mtd

--------- from temp1# join #temp2)

how can i join i1 here and get value in this formula

how many rows udf returns?

Do you know code behind the function? This is, is it possible to bring it to this query you want to build?

If you do not have any key so you cant join, you must do a FULL JOIN. This is done using FULL JOIN and no ON CLAUSE, or you can use comma (,) as my previous T-SQL shows.