create function _tmp_test(@par int)
    returns char(1)
    as
    begin
    	return 'Z'
    end
    
    go
    
    if object_id('tempdb..#tmp1') is not null drop table #tmp1
    if object_id('tempdb..#tmp2') is not null drop table #tmp2
    
    create table #tmp1
    (
    	lineid		int			not null,
    	docid		char(9)			not null,
    	doctype		int			not null,
    	prop1_id	char(9)			not null,
    	value		int			not null
    
    	primary key(lineid)
    )
    
    create table #tmp2
    (
    	propid		char(9)		not null,
    	prop_1		char(1)		not null
    
    	primary key(propid)
    )
    
    
    insert into #tmp1 values(1,'A',1157,'prop_id1',39500)
    insert into #tmp1 values(2,'A',1157,'prop_id2',7500)
    
    insert into #tmp2 values('prop_id1','A')
    insert into #tmp2 values('prop_id2','B')
    
    
    select
    	prop1_id
    	,doc
    	,Sum(value) value
    from
    	(
    	select
    		prop1_id = tmp2.prop_1,
    		doc 	 = dbo._tmp_test(tmp1.doctype) +  tmp1.docid,
    		value 	 = tmp1.value
    	from
    		#tmp1 tmp1
    	left join
    		#tmp2 tmp2 on tmp2.propid = tmp1.prop1_id
    	)vt
    group by
    	prop1_id
    	,doc
    having
    	Sum(value)<>0
    
    go

    drop function _tmp_test

    select
    	prop1_id
    	,doc
    	,Sum(value) value
    from
    	(
    	select
    		prop1_id = tmp2.prop_1,
    		doc 	 = dbo._tmp_test(tmp1.doctype) +  tmp1.docid,
    		value 	 = tmp1.value
    	from
    		#tmp1 tmp1
    	left join
    		#tmp2 tmp2 on tmp2.propid = tmp1.prop1_id
    	)vt
    group by
    	prop1_id
    	,doc

    select
    	prop1_id
    	,doc
    	,Sum(value) value
    from
    	(
    	select
    		prop1_id = tmp2.prop_1,
    		doc 	 = /*dbo._tmp_test(tmp1.doctype)*/ 'Z' +  tmp1.docid,
    		value 	 = tmp1.value
    	from
    		#tmp1 tmp1
    	left join
    		#tmp2 tmp2 on tmp2.propid = tmp1.prop1_id
    	)vt
    group by
    	prop1_id
    	,doc
    having
    	Sum(value)<>0

doc 	 = dbo._tmp_test(tmp1.doctype+'') +  tmp1.docid,