quote:

---

Originally posted by avijit111@gmail.com

--MAINGATE_RM 'MEHTASCL','RNV',1,'2009/01/01','2009/01/24'
ALTER PROCEDURE MAINGATE_RM
(
@company_no glcompany,
@location_no gllocn,
@lang_id int,
@date_from datetime,
@date_to datetime
)
as

declare @today datetime, @HEADING varchar(60)

select @today = @date_to
begin

create table #MAINGATE_RM
(
sl_no int,
company GLCOMPANY,
location GLLOCN,
unloading_no varchar(18),
entry_no varchar(3),
material varchar(60),
ul_po_no varchar(18),
ul_date_time varchar(25),
truck varchar(15),
ven_name varchar(100),
mg_entry_check_by varchar(20),
mg_entry_date_time varchar(25),
mg_entry_po varchar(18),
gross_date_time varchar(25),
gross_time_po varchar(18),
tare_date_time varchar(25),
tare_time_po varchar(18),
mg_exit_date_time varchar(25),
mg_exit_po varchar(18),
net_weight numeric(9),
puc varchar(10),
vendor_code cmn_cust_ven_code
)

SELECT @heading = 'MAINGATE REPORT'

insert into #MAINGATE_RM(company,location ,unloading_no,entry_no,material,ul_po_no,ul_date_time,
truck,ven_name,mg_entry_check_by,mg_entry_date_time, mg_entry_po,gross_date_time,gross_time_po,tare_date_time,
tare_time_po,mg_exit_date_time,mg_exit_po,net_weight,puc,vendor_code )

select a.company,a.location,a.unloading_no,a.entry_no,a.material,a.ul_po_no,CONVERT(DATETIME,a.ul_date_time,108),a.truck,
a.ven_name,a.mg_entry_check_by,CONVERT(DATETIME,a.mg_entry_date_time,103),a.mg_entry_po,
CONVERT(DATETIME,a.gross_date_time,103),a.gross_time_po,CONVERT(DATETIME,a.tare_date_time,103),
a.tare_time_po,CONVERT(DATETIME,a.mg_exit_date_time,103),a.mg_exit_po,b.net_weight,a.puc,C.vendor_code

from main_gate_rm_entry_table a inner join weightment_entry_rm b OUTER JOIN pur_po_header C
on a.unloading_no = b.unloading_no
where a.company = @company_no
and a.entry_no = b.entry_no
and convert(datetime,(rtrim(substring(b.tare_time,1,11))),103) between @date_from AND @date_to
order by ul_po_no



declare @row_no int,
@tot_row int

select @tot_row = (select count(*) from #MAINGATE_RM)
select @row_no = 1
set rowcount 1

while (@row_no<= @tot_row)
begin
update #MAINGATE_RM
set sl_no = @row_no
where isnull(sl_no,0) = 0
select @row_no = @row_no + 1
end
set rowcount 0
select *,@date_from[FROM_DATE], @date_to[to_DATE],@HEADING[HEADING] from #MAINGATE_RM

END

THE C TABLE I NEED TO ATTACHED WITH a and b BUT HAVING MATCHING ONLY COMPANY_NO AND LOCATION_NO .......PLS HELP TO FIND THE SOLUTION......

---