test2.sql

来自「工厂采购系统,DELPHI+SQL SERVER,三层技术」· SQL 代码 · 共 59 行

		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_List b
		where a.pono = b.pono and a.id=@vendor_id and b.hpn like @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(@customer_id,'')<>'') or(isnull(@hpn,'')<>'')
		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_List b
		where a.pono = b.pono and   b.hpn like @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
		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_List 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