creaate table #temp1(account,MTD float,ytd float)insert into #temp1select account ,mtd,ytd from account a join fhistory bon a.accountid = b.accountidresult of temp1 account mtd ytd1001 23.45 34.321003 34.56 43.211003 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.32now i need to add this data to third #temp3socreaate table #temp3(account,MTD float,ytd float)but in temp3,i need to perform calculation for MTD and ytdso my calculation will be ,what dat i get from #temp2, so for MTD 45.32 + i (i is the data i getting from another functionso it would be like 45.32 + i1 (this will come from account1(@p1)and i need to get all this data in temp3insert into #temp3(select account from #temp1for MTD it it is null then 45.32 + i1 else MTD,same for YTDfrom #temp1,#temp2,account1(@p1)#temp1 and #temp2 have 1 common column,but account1(@p1) doesnt have any common columnhow 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
--Here I will get 1 row right?
select SUM(MTD)+i total
--Here you will get all rows from t1 right?
--if you want only account 1003 just remove "left"
from #temp1 t1 left join #temp2 t2 on (t1.account=t2.account),CTE
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)
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.