How to merge mY query 2 temp tables on procedure mssql2000

Question

Hi Friends,

as example:

create table Customers

(

companyname varchar(10),

invoice_locn varchar(20),

invoice_no varchar(10),

model_ref varchar(20),

item_type char(20),

item_no int,

invoice_qty int

)

insert Customers values ('VB','chennai ','tn/002/13-14','#','K','6000320','25')

insert Customers values ('VB','chennai','tn/002/13_14','6000320','X','500320','30')

insert Customers values ('VB','chennai','tn/002/13_14','6000320','X','500300','05')

insert Customers values ('VB','chennai','tn/002/13_14','#','I','500340','25')

like i ve many records......

my expecting o/p is:

--------------------

company invoice_no invoice_locn item Sales Free

VB tn/002/13-14 Chennai 500340 25 0

VB tn/002/13-14 Chennai 500320 25 5

VB tn/002/13-14 Chennai 500300 0 5

alter procedure tr

(

@x datetime,

@y datetime

)

as

begin

select

a.companY_No,

a.invoice_locn,

a.invoice_type,

xx.invoice_no,

xx. Kit_Ref_group,

xx.item_no,

xx.sales,

xx.ge

into

#temp

from

dms_t_invoice_hdr a

inner join

(

select

a.company_no,

a.invoice_no,

a.invoice_locn,

a.item_no as Kit_Ref_group,

b.item_no ,

max(CASE WHEN A.invoice_qty>B.invoice_qty THEN 0 ELSE A.invoice_qty END) as sales ,

b.invoice_qty,

a.invoice_type,

b.invoice_qty - (CASE WHEN A.invoice_qty>B.invoice_qty THEN 0 ELSE A.invoice_qty END) as ge

from

dms_t_invoice_item a

inner join

dms_t_invoice_item b

on

a.invoice_no= b.invoice_no

where

a.item_no=b.model_ref

and

a.company_no=b.company_no

and

a.invoice_type=b.invoice_type

and

a.invoice_locn=b.invoice_locn

--and

-- a.invoice_no='INVH/000193/12-13 '

group by

a.company_no,

a.invoice_no,

a.item_no ,

b.item_no ,

a.invoice_qty,

b.invoice_qty,

a.invoice_type,

a.invoice_locn

)xx

on

xx.company_no=a.company_no

where

a.invoice_no=xx.invoice_no

and

xx.invoice_type=a.invoice_type

and

xx.invoice_locn=a.invoice_locn

and

a.invoice_dt between @x and @y

--and a.invoice_no='BLR/001653/12-13'

and

xx.ge >='0'

group by

a.companY_No,

a.invoice_locn,

xx.invoice_no,

xx. Kit_Ref_group,

xx.item_no,

xx.sales,

xx.ge,

a.invoice_type

select

b.company_no,

b.invoice_locn,

a.invoice_type,

a.invoice_no,

a.item_no,

sales=sum(case when left(item_no,1)='5'and item_type ='I' then invoice_qty else 0 end),

ge= '.00000000'

into

#temp1

from

dms_t_invoice_hdr b

inner join

dms_t_invoice_item a

on

a.company_no=b.company_no

where

a.invoice_no=b.invoice_no

and

a.invoice_type=b.invoice_type

and

a.invoice_locn=b.invoice_locn

and

b.invoice_dt between @x and @y

--and a.invoice_no='BLR/001653/12-13'

group by

a.invoice_no,

b.companY_no,

b.invoice_locn,

a.item_no,

a.invoice_type

---------- my coding for combine------

select

Q.company_no,

Q.invoice_locn,

W.invoice_type,

W.invoice_no,

Q.item_no,

Q.sales as sales,

Q.ge

from

#ter Q

left outer join

#der W

on

Q.company_no=W.company_no

where

Q.invoice_locn=W.invoice_locn

and

W.invoice_no=Q.invoice_no

and

W.invoice_type=Q.invoice_type

and

w.item_no=q.item_no

end

Finally i wanna combine both #temp and #temp1 .

in #temp contains sales & free for ITEM_TYPE='x'(i.e group products which was referred on item)

#temp1 contains sales & free for ITEM_TYPE='I' which was directly billed now i wanna display both results on my o/p with out duplicate how to do