0

I want join two temp tables with Full outer join but doesn't work properly and allways just show #RMS values without #RMB !!

where and what's wrong in this code?

( #RMS without null )

create table #RMS 
(
    [Year] int,
    [Month] int,
    sTAccount bigint,
    sRemaining bigint
)
insert into #RMS(Year,Month,sTAccount,sRemaining)
select
    YEAR(Date) [Year],
    DATEPART(MONTH,Date) [Month],
    sum(TAccount) sTAccount,
    sum(Remaining) sRemaining
from
    SaleInvoices
group by YEAR(Date),DATEPART(MONTH,Date)
order by YEAR(Date),DATEPART(MONTH,Date)

( #RMB without null but sometimes #RMB Month Column Value and #RMS Month Column value is Different ) 

create table #RMB 
(
    [Year] int,
    [Month] int,
    bTAccount bigint,
    bRemaining bigint
)
insert into #RMB(Year,Month,bTAccount,bRemaining)
select
    YEAR(Date) [Year],
    DATEPART(MONTH,Date) [Month],
    sum(TAccount) bTAccount,
    sum(Remaining) bRemaining
from
    BuyInvoices
group by YEAR(Date),DATEPART(MONTH,Date)
order by YEAR(Date),DATEPART(MONTH,Date)

select * from #RMS
Full Outer Join #RMB
on #RMS.Year=#RMB.Year and #RMS.Month=#RMB.Month
group by #RMS.Year, #RMS.Month
order by #RMS.Year, #RMS.Month

Thanks For Your Answers

MPERSIA
  • 187
  • 1
  • 15

1 Answers1

0

You have the wrong SELECT list. Replace * with #RMS.*, #RMB.* or (better) an explicit list of the fields you want, prefixing their names with the name of the table they come from. This also allows not to repeat the fields you've joined on.

Dario
  • 2,673
  • 20
  • 24