<?xml version="1.0"?>

<report name="cash-report" pdf-renderer="UmsatzZeitverlauf.xsl">
	<title>Umsatz im Zeitverlauf</title>

	<report-parameter name="TENANT" type="integer" hidden="true">
		<preset>@TENANT_NO</preset>
	</report-parameter>
	<report-parameter name="COMPANY" type="integer"
		hidden="true">
		<preset>@COMPANY_NO</preset>
	</report-parameter>
	<report-parameter name="TODATE" type="date" label="Von Datum:">
		<preset>@TODAY</preset>
		<input type="datepicker" />
	</report-parameter>
	<report-parameter name="FROMDATE" type="date" label="Von Datum:">
		<preset>@TODAY</preset>
		<input type="datepicker" />
	</report-parameter>
	<report-parameter name="BUSINESSUNIT" type="integer">
		<label>Filiale:</label>
		<input type="selectlist">
			<source type="dataset" name="businessunits">
			</source>
		</input>
	</report-parameter>


	<report-replacement name="Time">
		<replacement-selection name="Month" default="true">
			<replacement-parameter name="Label">
					case when month_value &lt; 10 then '0' + cast((month_value) as
					CHAR(1)) + '/' + cast((year_value) as CHAR(4))
					else cast((month_value) as CHAR(2)) + '/' + cast((year_value) as
					CHAR(4)) end
			</replacement-parameter>
			<replacement-parameter name="Sort">
					year_value * 10000 + month_value * 100
			</replacement-parameter>
			<replacement-parameter name="Selection">
					Year(h.sales_inv_ts) year_value,
					month(h.sales_inv_ts) month_value,
			</replacement-parameter>
			<replacement-parameter name="Group">
					Year(h.sales_inv_ts) ,
					month(h.sales_inv_ts)
			</replacement-parameter>
		</replacement-selection>
		
		<replacement-selection name="Quarter" default="false">
			<replacement-parameter name="Label">
					'Q' + cast(quarter_value as CHAR(1)) + '/' + cast(year_value as CHAR(4)) 
			</replacement-parameter>
			<replacement-parameter name="Sort">
					year_value*10 + quarter_value
			</replacement-parameter>
			<replacement-parameter name="Selection">
					Year(h.sales_inv_ts) year_value,
					(month(h.sales_inv_ts)+2)/ 3 quarter_value,
			</replacement-parameter>
			<replacement-parameter name="Group">
					Year(h.sales_inv_ts),
					(month(h.sales_inv_ts)+2)/ 3
			</replacement-parameter>
		</replacement-selection>
		
		
		<replacement-selection name="Tertial" default="false">
			<replacement-parameter name="Label">
					'T' + cast(tertial_value as CHAR(1)) + '/' + cast(year_value as CHAR(4)) 
			</replacement-parameter>
			<replacement-parameter name="Sort">
					year_value*10 + tertial_value
			</replacement-parameter>
			<replacement-parameter name="Selection">
					Year(h.sales_inv_ts) year_value,
					(month(h.sales_inv_ts)+3)/ 4 tertial_value,
			</replacement-parameter>
			<replacement-parameter name="Group">
					Year(h.sales_inv_ts),
					(month(h.sales_inv_ts)+3)/ 4
			</replacement-parameter>
		</replacement-selection>
		
		<replacement-selection name="HalfYear" default="false">
			<replacement-parameter name="Label">
					'HJ.' + cast(halfyear_value as CHAR(1)) + '/' + cast(year_value as CHAR(4)) 
			</replacement-parameter>
			<replacement-parameter name="Sort">
					year_value*10 + halfyear_value
			</replacement-parameter>
			<replacement-parameter name="Selection">
					Year(h.sales_inv_ts) year_value,
					(month(h.sales_inv_ts)+5)/ 6 halfyear_value,
			</replacement-parameter>
			<replacement-parameter name="Group">
					Year(h.sales_inv_ts),
					(month(h.sales_inv_ts)+5)/ 6
			</replacement-parameter>
		</replacement-selection>
		
		<replacement-selection name="FullYear" default="false">
			<replacement-parameter name="Label">
					cast(year_value as CHAR(4)) 
			</replacement-parameter>
			<replacement-parameter name="Sort">
					year_value*10 + fullyear_value
			</replacement-parameter>
			<replacement-parameter name="Selection">
					Year(h.sales_inv_ts) year_value,
					(month(h.sales_inv_ts)+11)/ 12 fullyear_value,
			</replacement-parameter>
			<replacement-parameter name="Group">
					Year(h.sales_inv_ts),
					(month(h.sales_inv_ts)+11)/ 12
			</replacement-parameter>
		</replacement-selection>
		
		
		

	</report-replacement>


	<report-result report-dataset="businessunits">
	</report-result>
	<report-result report-dataset="timeRevenue">
	</report-result>
	
	<report-result report-dataset="employee">
	</report-result>

	<report-result report-dataset="sumRevenue">
	</report-result>

	<report-result report-dataset="departmentCurrency">
	</report-result>


	<report-dataset name="businessunits">
		<sql>
			select * from businessunits where tenant_no=@TENANT_NO and
			company_no=@COMPANY_NO and department_no=@DEPARTMENT_NO and
			businessunit_no=@BUSINESSUNIT_NO
		</sql>
	</report-dataset>

	<report-dataset name="departmentCurrency">
		<sql>
			select * from currencies c join departments d on c.tenant_no =
			d.tenant_no AND c.currency_cd = d.currency_cd where
			d.tenant_no=@TENANT_NO and d.company_no=@COMPANY_NO and
			d.department_no =@DEPARTMENT_NO
		</sql>
	</report-dataset>
	
	
	<report-dataset name="employee">
		<sql>
			Select distinct case when @EMPLOYEE_NO is null then 'Alle' else employee_nm end as employee_nm from EMPLOYEES e where
			e.tenant_no=@TENANT_NO and e.company_no=@COMPANY_NO and
			e.employee_no=coalesce(@EMPLOYEE_NO, e.employee_no)
		</sql>
	</report-dataset>



	<report-dataset name="timeRevenue">
		<sql>
				SELECT
			@_Time_Label  label,
			@_Time_Sort  sort,
			(r_table.type1AmountCustomer - r_table.type2AmountCustomer) r_amountCustomer,
			(r_table.type1AmountGoup1 - r_table.type2AmountGoup1) r_amountGoup1,
			(r_table.type1AmountGoup2 - r_table.type2AmountGoup2) r_amountGoup2,
			(r_table.type1AmountGoup3 - r_table.type2AmountGoup3) r_amountGoup3,
			r_table.r_total_g1_services r_total_g1_services,
			r_table.r_total_g2_services r_total_g2_services,
			r_table.r_total_g3_services r_total_g3_services,
			r_table.r_total_g1 r_total_g1,
			r_table.r_total_g2 r_total_g2,
			r_table.r_total_g3 r_total_g3,
			r_table.r_total r_total,
			r_table.r_total_services r_total_services,
			r_table.r_total_products r_total_products,
			(r_table.r_total/r_table.r_cnt_dt) r_total_per_day,
			r_table.r_posCntDL as r_count_services,
			(case when (r_table.type1AmountCustomer - r_table.type2AmountCustomer) = 0 then 0 else case when (r_table.type1AmountCustomer - r_table.type2AmountCustomer) &lt; 0 and r_table.r_posCntDL &lt; 0 then -r_table.r_posCntDL/cast((r_table.type1AmountCustomer - r_table.type2AmountCustomer) as float) else  r_table.r_posCntDL/cast((r_table.type1AmountCustomer - r_table.type2AmountCustomer) as float) end end) r_services_per_cus,

			(case when (r_table.type1AmountGoup1 - r_table.type2AmountGoup1) = 0 then 0 else case when (r_table.type1AmountGoup1 - r_table.type2AmountGoup1) &lt; 0 and r_table.r_posCntDL_G1 &lt; 0 then -r_table.r_posCntDL_G1/cast((r_table.type1AmountGoup1 - r_table.type2AmountGoup1) as float) else  r_table.r_posCntDL_G1/cast((r_table.type1AmountGoup1 - r_table.type2AmountGoup1) as float) end end) r_services_per_g1,
			(case when (r_table.type1AmountGoup2 - r_table.type2AmountGoup2) = 0 then 0 else case when (r_table.type1AmountGoup2 - r_table.type2AmountGoup2) &lt; 0 and r_table.r_posCntDL_G2 &lt; 0 then -r_table.r_posCntDL_G2/cast((r_table.type1AmountGoup2 - r_table.type2AmountGoup2) as float) else  r_table.r_posCntDL_G2/cast((r_table.type1AmountGoup2 - r_table.type2AmountGoup2) as float) end end) r_services_per_g2,
			(case when (r_table.type1AmountGoup3 - r_table.type2AmountGoup3) = 0 then 0 else case when (r_table.type1AmountGoup3 - r_table.type2AmountGoup3) &lt; 0 and r_table.r_posCntDL_G3 &lt; 0 then -r_table.r_posCntDL_G3/cast((r_table.type1AmountGoup3 - r_table.type2AmountGoup3) as float) else  r_table.r_posCntDL_G3/cast((r_table.type1AmountGoup3 - r_table.type2AmountGoup3) as float) end end) r_services_per_g3,

			(case when (r_table.type1AmountGoup1 - r_table.type2AmountGoup1) = 0 then 0 else case when (r_table.type1AmountGoup1 - r_table.type2AmountGoup1) &lt; 0 and r_table.r_total_g1 &lt; 0 then -r_table.r_total_g1/cast((r_table.type1AmountGoup1 - r_table.type2AmountGoup1) as float) else  r_table.r_total_g1/cast((r_table.type1AmountGoup1 - r_table.type2AmountGoup1) as float) end end) r_total_per_g1,
			(case when (r_table.type1AmountGoup2 - r_table.type2AmountGoup2) = 0 then 0 else case when (r_table.type1AmountGoup2 - r_table.type2AmountGoup2) &lt; 0 and r_table.r_total_g2 &lt; 0 then -r_table.r_total_g2/cast((r_table.type1AmountGoup2 - r_table.type2AmountGoup2) as float) else  r_table.r_total_g2/cast((r_table.type1AmountGoup2 - r_table.type2AmountGoup2) as float) end end) r_total_per_g2,
			(case when (r_table.type1AmountGoup3 - r_table.type2AmountGoup3) = 0 then 0 else case when (r_table.type1AmountGoup3 - r_table.type2AmountGoup3) &lt; 0 and r_table.r_total_g3 &lt; 0 then -r_table.r_total_g3/cast((r_table.type1AmountGoup3 - r_table.type2AmountGoup3) as float) else  r_table.r_total_g3/cast((r_table.type1AmountGoup3 - r_table.type2AmountGoup3) as float) end end) r_total_per_g3,

			r_table.r_cnt_dt r_cnt_dt
			FROM(
			select
			@_Time_Selection
			COUNT ( DISTINCT (cast(sales_inv_ts AS DATE)))
			r_cnt_dt,
			COUNT (  DISTINCT   (case WHEN coalesce(h.sales_inv_type, 1) = 1 THEN p.sales_inv_id  ELSE NULL END ) ) type1AmountCustomer,
			COUNT (  DISTINCT   (case WHEN coalesce(h.sales_inv_type, 1) = 2 THEN p.sales_inv_id  ELSE NULL END ) ) type2AmountCustomer,
            COUNT (  DISTINCT (case WHEN coalesce(h.sales_inv_type, 1) = 1 and coalesce(h.customer_group_no, 1) = 1 THEN p.sales_inv_id  ELSE NULL END )) type1AmountGoup1,
			COUNT (  DISTINCT (case WHEN coalesce(h.sales_inv_type, 1) = 1 and coalesce(h.customer_group_no, 1) = 2 THEN p.sales_inv_id  ELSE NULL END )) type1AmountGoup2,
			COUNT (  DISTINCT (case WHEN coalesce(h.sales_inv_type, 1) = 1 and coalesce(h.customer_group_no, 1) = 3 THEN p.sales_inv_id  ELSE NULL END)) type1AmountGoup3,
			COUNT (  DISTINCT (case WHEN coalesce(h.sales_inv_type, 1) = 2 and coalesce(h.customer_group_no, 1) = 1 THEN p.sales_inv_id  ELSE NULL END )) type2AmountGoup1,
			COUNT (  DISTINCT (case WHEN coalesce(h.sales_inv_type, 1) = 2 and coalesce(h.customer_group_no, 1) = 2 THEN p.sales_inv_id  ELSE NULL END )) type2AmountGoup2,
			COUNT (  DISTINCT (case WHEN coalesce(h.sales_inv_type, 1) = 2 and coalesce(h.customer_group_no, 1) = 3 THEN p.sales_inv_id  ELSE NULL END)) type2AmountGoup3,
		  
			sum(case when h.sales_inv_type=2 then
			-p.position_gross_price else p.position_gross_price end *
			wr.operating_employee_factor) r_total,
			sum(case when
			coalesce(p.stockable, 0)=0 then case when h.sales_inv_type=2 then
			-p.position_gross_price else p.position_gross_price end else 0 end *
			wr.operating_employee_factor) r_total_services,
			sum(case when
			coalesce(p.stockable, 0)=1 then case when h.sales_inv_type=2 then
			-p.position_gross_price else p.position_gross_price end else 0 end *
			wr.operating_employee_factor) r_total_products,
			sum(case when
			coalesce(h.customer_group_no, 1) = 1 and coalesce(p.stockable, 0)=0
			then case when h.sales_inv_type=2 then -p.position_gross_price else
			p.position_gross_price end else 0 end * wr.operating_employee_factor)
			r_total_g1_services,
			sum(case when coalesce(h.customer_group_no, 1) =
			2 and coalesce(p.stockable, 0)=0 then case when h.sales_inv_type=2
			then -p.position_gross_price else p.position_gross_price end else 0
			end * wr.operating_employee_factor) r_total_g2_services,
			sum(case when
			coalesce(h.customer_group_no, 1) = 3 and coalesce(p.stockable, 0)=0
			then case when h.sales_inv_type=2 then -p.position_gross_price else
			p.position_gross_price end else 0 end * wr.operating_employee_factor)
			r_total_g3_services,
			sum(case when coalesce(h.customer_group_no, 1) =
			1 then case when h.sales_inv_type=2 then -p.position_gross_price else
			p.position_gross_price end else 0 end * wr.operating_employee_factor)
			r_total_g1,
			sum(case when coalesce(h.customer_group_no, 1) = 2 then
			case when h.sales_inv_type=2 then -p.position_gross_price else
			p.position_gross_price end else 0 end * wr.operating_employee_factor)
			r_total_g2,
			sum(case when coalesce(h.customer_group_no, 1) = 3 then
			case when h.sales_inv_type=2 then -p.position_gross_price else
			p.position_gross_price end else 0 end * wr.operating_employee_factor)
			r_total_g3,
			sum(case when coalesce(p.stockable, 0)=0 then case when
			h.sales_inv_type=2 then -1 else 1 end else 0 end *
			wr.operating_employee_factor) r_posCntDL,
			sum(case when
			coalesce(p.stockable, 0)=0 then case when
			coalesce(h.customer_group_no, 1) = 1 then case when
			h.sales_inv_type=2 then -1 else 1 end else 0 end else 0 end *
			wr.operating_employee_factor) r_posCntDL_G1,
			sum(case when
			coalesce(p.stockable, 0)=0 then case when
			coalesce(h.customer_group_no, 1) = 2 then case when
			h.sales_inv_type=2 then -1 else 1 end else 0 end else 0 end *
			wr.operating_employee_factor) r_posCntDL_G2,
			sum(case when
			coalesce(p.stockable, 0)=0 then case when
			coalesce(h.customer_group_no, 1) = 3 then case when
			h.sales_inv_type=2 then -1 else 1 end else 0 end else 0 end *
			wr.operating_employee_factor) r_posCntDL_G3
			from sales_inv_positions p
			left join sales_invs h on h.tenant_no = p.tenant_no and h.pos_cd =
			p.pos_cd and h.sales_inv_id = p.sales_inv_id
			left join customer_roles cr on cr.tenant_no = h.tenant_no and
			h.CUSTOMER_NO = cr.CONTACT_NO
			join sales_dln_pos_work_records wr on p.tenant_no = wr.tenant_no and
			p.pos_cd = wr.pos_cd and p.sales_dln_id = wr.sales_dln_id and
			p.sales_inv_position_id = wr.sales_dln_position_id
			where h.tenant_no=@TENANT_NO and h.pos_cd=@POS_CD and sales_inv_ts &gt;= cast(@DATE as DATE) AND sales_inv_ts &lt;= DATEADD(DD,1,CAST(@TODATE AS DATE))
			AND coalesce(wr.operating_profession_no,-1222) =
			coalesce(@PROFESSION_NO,coalesce(wr.operating_profession_no,-1222) )
			AND wr.operating_employee_no =
			coalesce(@EMPLOYEE_NO,wr.operating_employee_no )
			GROUP BY @_Time_Group
			) r_table
			order by @_Time_Sort
		</sql>
	</report-dataset>

	<report-dataset name="sumRevenue">
		<sql>
			SELECT
				(r_table.type1AmountCustomer - r_table.type2AmountCustomer) r_amountCustomer,
			(r_table.type1AmountGoup1 - r_table.type2AmountGoup1) r_amountGoup1,
			(r_table.type1AmountGoup2 - r_table.type2AmountGoup2) r_amountGoup2,
			(r_table.type1AmountGoup3 - r_table.type2AmountGoup3) r_amountGoup3,
			r_table.r_total_g1_services r_total_g1_services,
			r_table.r_total_g2_services r_total_g2_services,
			r_table.r_total_g3_services r_total_g3_services,
			r_table.r_total_g1 r_total_g1,
			r_table.r_total_g2 r_total_g2,
			r_table.r_total_g3 r_total_g3,
			r_table.r_total r_total,
			r_table.r_total_services r_total_services,
			r_table.r_total_products r_total_products,
			(r_table.r_total/r_table.r_cnt_dt) r_total_per_day,
			r_table.r_posCntDL as r_count_services,
			(case when (r_table.type1AmountCustomer - r_table.type2AmountCustomer) = 0 then 0 else case when (r_table.type1AmountCustomer - r_table.type2AmountCustomer) &lt; 0 and r_table.r_posCntDL &lt; 0 then -r_table.r_posCntDL/cast((r_table.type1AmountCustomer - r_table.type2AmountCustomer) as float) else  r_table.r_posCntDL/cast((r_table.type1AmountCustomer - r_table.type2AmountCustomer) as float) end end) r_services_per_cus,

			(case when (r_table.type1AmountGoup1 - r_table.type2AmountGoup1) = 0 then 0 else case when (r_table.type1AmountGoup1 - r_table.type2AmountGoup1) &lt; 0 and r_table.r_posCntDL_G1 &lt; 0 then -r_table.r_posCntDL_G1/cast((r_table.type1AmountGoup1 - r_table.type2AmountGoup1) as float) else  r_table.r_posCntDL_G1/cast((r_table.type1AmountGoup1 - r_table.type2AmountGoup1) as float) end end) r_services_per_g1,
			(case when (r_table.type1AmountGoup2 - r_table.type2AmountGoup2) = 0 then 0 else case when (r_table.type1AmountGoup2 - r_table.type2AmountGoup2) &lt; 0 and r_table.r_posCntDL_G2 &lt; 0 then -r_table.r_posCntDL_G2/cast((r_table.type1AmountGoup2 - r_table.type2AmountGoup2) as float) else  r_table.r_posCntDL_G2/cast((r_table.type1AmountGoup2 - r_table.type2AmountGoup2) as float) end end) r_services_per_g2,
			(case when (r_table.type1AmountGoup3 - r_table.type2AmountGoup3) = 0 then 0 else case when (r_table.type1AmountGoup3 - r_table.type2AmountGoup3) &lt; 0 and r_table.r_posCntDL_G3 &lt; 0 then -r_table.r_posCntDL_G3/cast((r_table.type1AmountGoup3 - r_table.type2AmountGoup3) as float) else  r_table.r_posCntDL_G3/cast((r_table.type1AmountGoup3 - r_table.type2AmountGoup3) as float) end end) r_services_per_g3,

			(case when (r_table.type1AmountGoup1 - r_table.type2AmountGoup1) = 0 then 0 else case when (r_table.type1AmountGoup1 - r_table.type2AmountGoup1) &lt; 0 and r_table.r_total_g1 &lt; 0 then -r_table.r_total_g1/cast((r_table.type1AmountGoup1 - r_table.type2AmountGoup1) as float) else  r_table.r_total_g1/cast((r_table.type1AmountGoup1 - r_table.type2AmountGoup1) as float) end end) r_total_per_g1,
			(case when (r_table.type1AmountGoup2 - r_table.type2AmountGoup2) = 0 then 0 else case when (r_table.type1AmountGoup2 - r_table.type2AmountGoup2) &lt; 0 and r_table.r_total_g2 &lt; 0 then -r_table.r_total_g2/cast((r_table.type1AmountGoup2 - r_table.type2AmountGoup2) as float) else  r_table.r_total_g2/cast((r_table.type1AmountGoup2 - r_table.type2AmountGoup2) as float) end end) r_total_per_g2,
			(case when (r_table.type1AmountGoup3 - r_table.type2AmountGoup3) = 0 then 0 else case when (r_table.type1AmountGoup3 - r_table.type2AmountGoup3) &lt; 0 and r_table.r_total_g3 &lt; 0 then -r_table.r_total_g3/cast((r_table.type1AmountGoup3 - r_table.type2AmountGoup3) as float) else  r_table.r_total_g3/cast((r_table.type1AmountGoup3 - r_table.type2AmountGoup3) as float) end end) r_total_per_g3,

			r_table.r_cnt_dt r_cnt_dt,
			r_table.r_cnt_employee r_cnt_employee
			FROM(
			select
			p.tenant_no ,
			p.pos_cd,
			COUNT ( DISTINCT (cast(sales_inv_ts AS DATE))) r_cnt_dt,
			COUNT (  DISTINCT    wr.operating_employee_no  ) r_cnt_employee,
			COUNT (  DISTINCT   (case WHEN coalesce(h.sales_inv_type, 1) = 1 THEN p.sales_inv_id  ELSE NULL END ) ) type1AmountCustomer,
			COUNT (  DISTINCT   (case WHEN coalesce(h.sales_inv_type, 1) = 2 THEN p.sales_inv_id  ELSE NULL END ) ) type2AmountCustomer,
            COUNT (  DISTINCT (case WHEN coalesce(h.sales_inv_type, 1) = 1 and coalesce(h.customer_group_no, 1) = 1 THEN p.sales_inv_id  ELSE NULL END )) type1AmountGoup1,
			COUNT (  DISTINCT (case WHEN coalesce(h.sales_inv_type, 1) = 1 and coalesce(h.customer_group_no, 1) = 2 THEN p.sales_inv_id  ELSE NULL END )) type1AmountGoup2,
			COUNT (  DISTINCT (case WHEN coalesce(h.sales_inv_type, 1) = 1 and coalesce(h.customer_group_no, 1) = 3 THEN p.sales_inv_id  ELSE NULL END)) type1AmountGoup3,
			COUNT (  DISTINCT (case WHEN coalesce(h.sales_inv_type, 1) = 2 and coalesce(h.customer_group_no, 1) = 1 THEN p.sales_inv_id  ELSE NULL END )) type2AmountGoup1,
			COUNT (  DISTINCT (case WHEN coalesce(h.sales_inv_type, 1) = 2 and coalesce(h.customer_group_no, 1) = 2 THEN p.sales_inv_id  ELSE NULL END )) type2AmountGoup2,
			COUNT (  DISTINCT (case WHEN coalesce(h.sales_inv_type, 1) = 2 and coalesce(h.customer_group_no, 1) = 3 THEN p.sales_inv_id  ELSE NULL END)) type2AmountGoup3,
		  
			sum(case when h.sales_inv_type=2 then
			-p.position_gross_price else p.position_gross_price end *
			wr.operating_employee_factor) r_total,
			sum(case when
			coalesce(p.stockable, 0)=0 then case when h.sales_inv_type=2 then
			-p.position_gross_price else p.position_gross_price end else 0 end *
			wr.operating_employee_factor) r_total_services,
			sum(case when
			coalesce(p.stockable, 0)=1 then case when h.sales_inv_type=2 then
			-p.position_gross_price else p.position_gross_price end else 0 end *
			wr.operating_employee_factor) r_total_products,
			sum(case when
			coalesce(h.customer_group_no, 1) = 1 and coalesce(p.stockable, 0)=0
			then case when h.sales_inv_type=2 then -p.position_gross_price else
			p.position_gross_price end else 0 end * wr.operating_employee_factor)
			r_total_g1_services,
			sum(case when coalesce(h.customer_group_no, 1) =
			2 and coalesce(p.stockable, 0)=0 then case when h.sales_inv_type=2
			then -p.position_gross_price else p.position_gross_price end else 0
			end * wr.operating_employee_factor) r_total_g2_services,
			sum(case when
			coalesce(h.customer_group_no, 1) = 3 and coalesce(p.stockable, 0)=0
			then case when h.sales_inv_type=2 then -p.position_gross_price else
			p.position_gross_price end else 0 end * wr.operating_employee_factor)
			r_total_g3_services,
			sum(case when coalesce(h.customer_group_no, 1) =
			1 then case when h.sales_inv_type=2 then -p.position_gross_price else
			p.position_gross_price end else 0 end * wr.operating_employee_factor)
			r_total_g1,
			sum(case when coalesce(h.customer_group_no, 1) = 2 then
			case when h.sales_inv_type=2 then -p.position_gross_price else
			p.position_gross_price end else 0 end * wr.operating_employee_factor)
			r_total_g2,

			sum(case when coalesce(h.customer_group_no, 1) = 3 then
			case when h.sales_inv_type=2 then -p.position_gross_price else
			p.position_gross_price end else 0 end * wr.operating_employee_factor)
			r_total_g3,
			sum(case when coalesce(p.stockable, 0)=0 then case when
			h.sales_inv_type=2 then -1 else 1 end else 0 end *
			wr.operating_employee_factor) r_posCntDL,
			sum(case when
			coalesce(p.stockable, 0)=0 then case when
			coalesce(h.customer_group_no, 1) = 1 then case when
			h.sales_inv_type=2 then -1 else 1 end else 0 end else 0 end *
			wr.operating_employee_factor) r_posCntDL_G1,
			sum(case when
			coalesce(p.stockable, 0)=0 then case when
			coalesce(h.customer_group_no, 1) = 2 then case when
			h.sales_inv_type=2 then -1 else 1 end else 0 end else 0 end *
			wr.operating_employee_factor) r_posCntDL_G2,
			sum(case when
			coalesce(p.stockable, 0)=0 then case when
			coalesce(h.customer_group_no, 1) = 3 then case when
			h.sales_inv_type=2 then -1 else 1 end else 0 end else 0 end *
			wr.operating_employee_factor) r_posCntDL_G3
			from sales_inv_positions p
			left join sales_invs h on h.tenant_no = p.tenant_no and h.pos_cd =
			p.pos_cd and h.sales_inv_id = p.sales_inv_id
			left join customer_roles
			cr on cr.tenant_no = h.tenant_no and h.CUSTOMER_NO = cr.CONTACT_NO
			join sales_dln_pos_work_records wr on h.tenant_no = wr.tenant_no and
			h.pos_cd = wr.pos_cd and h.sales_dln_id = wr.sales_dln_id and
			p.sales_inv_position_id = wr.sales_dln_position_id 
			where h.tenant_no=@TENANT_NO and
			h.pos_cd=@POS_CD and cast
			(sales_inv_ts as DATE) &gt;= cast (@DATE as DATE) AND cast
			(sales_inv_ts as DATE) &lt;= cast (@TODATE as DATE) 
			AND coalesce(wr.operating_profession_no,-1222) =
			coalesce(@PROFESSION_NO,coalesce(wr.operating_profession_no,-1222) )
			AND wr.operating_employee_no =
			coalesce(@EMPLOYEE_NO,wr.operating_employee_no )

			GROUP BY p.tenant_no , p.pos_cd

			) r_table

		</sql>
	</report-dataset>


</report>
