test.sql

工厂采购系统,DELPHI+SQL SERVER,三层技术

declare
@action_cd  int ,				---0 by delivery date,--2 by creation date --3 by gr date
@vendor_id  varchar(10),
@customer_id varchar(5) ,
@hpn         varchar(18),
@start_dt datetime ,
@end_dt   datetime
	
	select @action_cd =0
	select @vendor_id =''
	select @customer_id =''
	select @hpn = ''
	select @start_dt ='2004-01-01'
	select @end_dt = '2004-10-01'
				
		create  table #tmp_gtr_Record
		(
		vendor_id varchar(50),
		vendor_nm varchar(255),
		item      int,
		Order_Amount numeric(18,5),
		Currency   varchar(50),
		delivery_dt varchar(10),
		GR_Amount  numeric(18,5),
		payment    varchar(120)
		)

		if(isnull(@vendor_id,'')<>'') and (isnull(@hpn,'')<>'') 
		begin
		insert into #tmp_gtr_Record(vendor_id,order_amount,Delivery_dt)
		select 
		a.id,
		sum(b.in_quantity*b.unit_Price) as Order_Amount,
		(convert(char(4),datepart(yy,b.created_dt))+'/'+convert(char(2),datepart(mm,b.created_dt)))as delivery_dt
		from ord_mst a,gtr_item b
		where a.pono = b.pono and a.id=@vendor_id and b.hpn = @hpn+'%'
		and (datepart(yy,b.created_dt) >=datepart(yy,@start_dt) 
		and datepart(mm,b.created_dt)>=datepart(mm,@start_dt))
		and (datepart(yy,b.created_dt) <=datepart(yy,@end_dt) 
		and datepart(mm,b.created_dt)<=datepart(mm,@end_dt)) 
		group by 
		a.id,(convert(char(4),datepart(yy,b.created_dt))+'/'+convert(char(2),datepart(mm,b.created_dt)))
		order by (convert(char(4),datepart(yy,b.created_dt))+'/'+convert(char(2),datepart(mm,b.created_dt)))
		end
		else if(isnull(@vendor_id,'')<>'') and (isnull(@customer_id,'')<>'') 
		begin
			insert into #tmp_gtr_Record(vendor_id,order_amount,Delivery_dt,
		GR_Amount)
		select 
		a.id,
		sum(b.in_quantity*b.unit_Price) as Order_Amount,
		(convert(char(4),datepart(yy,b.created_dt))+'/'+convert(char(2),datepart(mm,b.created_dt)))as delivery_dt,
		sum(b.in_quantity*b.unit_Price) as GR_Amount
		from ord_mst a,gtr_item b
		where a.pono = b.pono --and a.id=@vendor_id
		and (datepart(yy,b.created_dt) >=datepart(yy,@start_dt) 
		and datepart(mm,b.created_dt)>=datepart(mm,@start_dt))
		and (datepart(yy,b.created_dt) <=datepart(yy,@end_dt) 
		and datepart(mm,b.created_dt)<=datepart(mm,@end_dt)) 
		group by 
		a.id,(convert(char(4),datepart(yy,b.created_dt))+'/'+convert(char(2),datepart(mm,b.created_dt)))
		order by (convert(char(4),datepart(yy,b.created_dt))+'/'+convert(char(2),datepart(mm,b.created_dt)))

		end
		else
		begin
			insert into #tmp_gtr_Record(vendor_id,order_amount,Delivery_dt,
		GR_Amount)
		select 
		a.id,
		sum(b.in_quantity*b.unit_Price) as Order_Amount,
		(convert(char(4),datepart(yy,b.created_dt))+'/'+convert(char(2),datepart(mm,b.created_dt)))as delivery_dt,
		sum(b.in_quantity*b.unit_Price) as GR_Amount
		from ord_mst a,gtr_item b
		where a.pono = b.pono --and a.id=@vendor_id
		and (datepart(yy,b.created_dt) >=datepart(yy,@start_dt) 
		and datepart(mm,b.created_dt)>=datepart(mm,@start_dt))
		and (datepart(yy,b.created_dt) <=datepart(yy,@end_dt) 
		and datepart(mm,b.created_dt)<=datepart(mm,@end_dt)) 
		group by 
		a.id,(convert(char(4),datepart(yy,b.created_dt))+'/'+convert(char(2),datepart(mm,b.created_dt)))
		order by (convert(char(4),datepart(yy,b.created_dt))+'/'+convert(char(2),datepart(mm,b.created_dt)))
		
		end


update #tmp_gtr_Record
	set vendor_nm = b.vendor_nm,
	    Currency  = b.deft_Currency,
	    payment  = b.payterms
from #tmp_gtr_Record a,po_vendor_mstr b
where a.vendor_id = b.vendor_id


declare @sql varchar(8000)

set @sql = 'select vendor_id,Vendor_nm,Currency,payment,'

 

select @sql = @sql + 'sum(case delivery_dt when '''+delivery_dt+''' 

                          then Order_Amount else 0 end) as '''+delivery_dt+''','


  from (select distinct delivery_dt from #tmp_gtr_Record ) as a

 

select @sql = left(@sql,len(@sql)-1)  + ' 
from #tmp_gtr_Record 
group by 
vendor_id,
vendor_nm,
currency,
payment

order by vendor_id
'

--print(@sql)
exec(@sql)


drop table #tmp_gtr_Record