<?xml version="1.0"?>

<report name="cash-report" csv-renderer="KurzberichtAusfMitarbeiterCSV.xsl">
	<title>Umsatz nach ausführenden Mitarbeitern</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-result report-dataset="businessunits">
	</report-result>
	<report-result report-dataset="employeeRevenue">
	</report-result>
	<report-result report-dataset="sumEmployeeRevenue">
	</report-result>
	<report-result report-dataset="sumPrevYearEmployeeRevenue">
	</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="employeeRevenue">
		<sql>
				SELECT 

r_table.r_employee_no r_employee_no,
r_table.r_employee_nm r_employee_nm,
(r_table.type1AmountCustomer - r_table.type2AmountCustomer) r_amount_customer,
(r_table.type1AmountGoup1 - r_table.type2AmountGoup1) r_amount_group1,
(r_table.type1AmountGoup2 - r_table.type2AmountGoup2) r_amount_group2,
(r_table.type1AmountGoup3 - r_table.type2AmountGoup3) r_amount_group3,
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_products r_total_g1_products,
r_table.r_total_g2_products r_total_g2_products,
r_table.r_total_g3_products r_total_g3_products,
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_total_services/r_table.r_cnt_dt) r_total_services_per_day,
(r_table.r_total_products/r_table.r_cnt_dt) r_total_product_per_day,
((r_table.type1AmountCustomer - r_table.type2AmountCustomer)/r_table.r_cnt_dt) r_amount_customer_per_day,
(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_total &lt; 0 then -r_table.r_total/cast((r_table.type1AmountCustomer - r_table.type2AmountCustomer) as float) else  r_table.r_total/cast((r_table.type1AmountCustomer - r_table.type2AmountCustomer) as float) end end) r_total_per_customer,
(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_total_services &lt; 0 then -r_table.r_total_services/cast((r_table.type1AmountCustomer - r_table.type2AmountCustomer) as float) else  r_table.r_total_services/cast((r_table.type1AmountCustomer - r_table.type2AmountCustomer) as float) end end) r_total_services_per_customer,
(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_total_products &lt; 0 then -r_table.r_total_products/cast((r_table.type1AmountCustomer - r_table.type2AmountCustomer) as float) else  r_table.r_total_products/cast((r_table.type1AmountCustomer - r_table.type2AmountCustomer) as float) end end) r_total_product_per_customer,

r_table.r_cnt_dt r_cnt_dt,
r_table.r_planed_week_working_hours r_planed_week_working_hours
	
FROM(
select
            
			wr.operating_employee_no r_employee_no,
			e.employee_nm r_employee_nm,
			
			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 and 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_g1_products,
			sum(case when coalesce(h.customer_group_no, 1) = 2 and 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_g2_products,
			sum(case when coalesce(h.customer_group_no, 1) = 3 and 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_g3_products,
			
			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,
			max (tmpContracts.planed_week_working_hours) r_planed_week_working_hours
			
			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
			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 
			left join employees e on e.tenant_no = h.tenant_no and h.company_no = e.company_no and wr.operating_employee_no= e.employee_no
			left join (SELECT 	tenant_no, company_no, department_no, businessunit_no, employee_no, max(planed_week_working_hours)  planed_week_working_hours 
					from 	employee_contracts 
					where 	cast (valid_from as DATE) &lt;= cast (CURRENT_TIMESTAMP as DATE) AND  cast (coalesce(valid_to,CURRENT_TIMESTAMP) as DATE) &gt;= cast (CURRENT_TIMESTAMP as DATE)  
					group by tenant_no, company_no, department_no, businessunit_no, employee_no 
				) tmpContracts
			on tmpContracts.tenant_no = h.tenant_no and tmpContracts.company_no = h.company_no and tmpContracts.department_no = h.department_no  and tmpContracts.businessunit_no = h.businessunit_no and wr.operating_employee_no = tmpContracts.employee_no
			where  h.tenant_no=@TENANT_NO and h.pos_cd=@POS_CD and  h.sales_inv_id in (select sales_inv_id from sales_invs where tenant_no=@TENANT_NO and 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) )
			 
			   GROUP BY  wr.operating_employee_no,e.employee_nm
			   ) r_table
			   order by r_table.r_total desc

		</sql>
	</report-dataset>

	
	
		<report-dataset name="sumEmployeeRevenue">
		<sql>
	SELECT 
sub_table.r_tenant_no r_tenant_no,

SUM(sub_table.r_amountCustomer) r_amount_customer,
SUM(sub_table.r_amountGoup1) r_amount_group1,
SUM(sub_table.r_amountGoup2) r_amount_group2,
SUM(sub_table.r_amountGoup3) r_amount_group3,
SUM(sub_table.r_total_g1_services) r_total_g1_services,
SUM(sub_table.r_total_g2_services) r_total_g2_services,
SUM(sub_table.r_total_g3_services) r_total_g3_services,
SUM(sub_table.r_total_g1_products) r_total_g1_products,
SUM(sub_table.r_total_g2_products) r_total_g2_products,
SUM(sub_table.r_total_g3_products) r_total_g3_products,
SUM(sub_table.r_total_g1) r_total_g1,
SUM(sub_table.r_total_g2) r_total_g2,
SUM(sub_table.r_total_g3) r_total_g3,
SUM(sub_table.r_total) r_total,
SUM(sub_table.r_total_services) r_total_services,
SUM(sub_table.r_total_products) r_total_products,
SUM(sub_table.r_total_per_day) r_total_per_day,
SUM(sub_table.r_total_services_per_day) r_total_services_per_day,
SUM(sub_table.r_total_product_per_day) r_total_product_per_day,
SUM(sub_table.r_amount_customer_per_day) r_amount_customer_per_day,
SUM(sub_table.r_total_per_customer) r_total_per_customer,
SUM(sub_table.r_total_services_per_customer) r_total_services_per_customer,
SUM(sub_table.r_total_product_per_customer) r_total_product_per_customer,
COUNT(*) r_cnt_rows

	
FROM(
		SELECT 
r_table.r_tenant_no r_tenant_no,
r_table.r_employee_no r_employee_no,
r_table.r_employee_nm r_employee_nm,
(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_products r_total_g1_products,
r_table.r_total_g2_products r_total_g2_products,
r_table.r_total_g3_products r_total_g3_products,
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_total_services/r_table.r_cnt_dt) r_total_services_per_day,
(r_table.r_total_products/r_table.r_cnt_dt) r_total_product_per_day,
((r_table.type1AmountCustomer - r_table.type2AmountCustomer)/r_table.r_cnt_dt) r_amount_customer_per_day,
(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_total &lt; 0 then -r_table.r_total/cast((r_table.type1AmountCustomer - r_table.type2AmountCustomer) as float) else  r_table.r_total/cast((r_table.type1AmountCustomer - r_table.type2AmountCustomer) as float) end end) r_total_per_customer,
(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_total_services &lt; 0 then -r_table.r_total_services/cast((r_table.type1AmountCustomer - r_table.type2AmountCustomer) as float) else  r_table.r_total_services/cast((r_table.type1AmountCustomer - r_table.type2AmountCustomer) as float) end end) r_total_services_per_customer,
(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_total_products &lt; 0 then -r_table.r_total_products/cast((r_table.type1AmountCustomer - r_table.type2AmountCustomer) as float) else  r_table.r_total_products/cast((r_table.type1AmountCustomer - r_table.type2AmountCustomer) as float) end end) r_total_product_per_customer,

r_table.r_cnt_dt r_cnt_dt

	
FROM(
select
            p.tenant_no r_tenant_no,
			wr.operating_employee_no r_employee_no,
			e.employee_nm r_employee_nm,
			
			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 and 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_g1_products,
			sum(case when coalesce(h.customer_group_no, 1) = 2 and 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_g2_products,
			sum(case when coalesce(h.customer_group_no, 1) = 3 and 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_g3_products,
			
			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
			


			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
			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 
			left join employees e on e.tenant_no = h.tenant_no and h.company_no = e.company_no and wr.operating_employee_no= e.employee_no
			where  h.tenant_no=@TENANT_NO and h.pos_cd=@POS_CD and  h.sales_inv_id in (select sales_inv_id from sales_invs where tenant_no=@TENANT_NO and 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) )
			 
			   GROUP BY  p.tenant_no, wr.operating_employee_no,e.employee_nm
			   ) r_table
			   ) sub_table
			   
			   GROUP BY  sub_table.r_tenant_no
		</sql>
	</report-dataset>
	
	
			<report-dataset name="sumPrevYearEmployeeRevenue">
		<sql>
	SELECT 
sub_table.r_tenant_no r_tenant_no,

SUM(sub_table.r_amountCustomer) r_amount_customer,
SUM(sub_table.r_amountGoup1) r_amount_group1,
SUM(sub_table.r_amountGoup2) r_amount_group2,
SUM(sub_table.r_amountGoup3) r_amount_group3,
SUM(sub_table.r_total_g1_services) r_total_g1_services,
SUM(sub_table.r_total_g2_services) r_total_g2_services,
SUM(sub_table.r_total_g3_services) r_total_g3_services,
SUM(sub_table.r_total_g1_products) r_total_g1_products,
SUM(sub_table.r_total_g2_products) r_total_g2_products,
SUM(sub_table.r_total_g3_products) r_total_g3_products,
SUM(sub_table.r_total_g1) r_total_g1,
SUM(sub_table.r_total_g2) r_total_g2,
SUM(sub_table.r_total_g3) r_total_g3,
SUM(sub_table.r_total) r_total,
SUM(sub_table.r_total_services) r_total_services,
SUM(sub_table.r_total_products) r_total_products,
SUM(sub_table.r_total_per_day) r_total_per_day,
SUM(sub_table.r_total_services_per_day) r_total_services_per_day,
SUM(sub_table.r_total_product_per_day) r_total_product_per_day,
SUM(sub_table.r_amount_customer_per_day) r_amount_customer_per_day,
SUM(sub_table.r_total_per_customer) r_total_per_customer,
SUM(sub_table.r_total_services_per_customer) r_total_services_per_customer,
SUM(sub_table.r_total_product_per_customer) r_total_product_per_customer,
COUNT(*) r_cnt_rows
	
FROM (
		SELECT 
r_table.r_tenant_no r_tenant_no,
r_table.r_employee_no r_employee_no,
r_table.r_employee_nm r_employee_nm,
(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_products r_total_g1_products,
r_table.r_total_g2_products r_total_g2_products,
r_table.r_total_g3_products r_total_g3_products,
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_total_services/r_table.r_cnt_dt) r_total_services_per_day,
(r_table.r_total_products/r_table.r_cnt_dt) r_total_product_per_day,
((r_table.type1AmountCustomer - r_table.type2AmountCustomer)/r_table.r_cnt_dt) r_amount_customer_per_day,
(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_total &lt; 0 then -r_table.r_total/cast((r_table.type1AmountCustomer - r_table.type2AmountCustomer) as float) else  r_table.r_total/cast((r_table.type1AmountCustomer - r_table.type2AmountCustomer) as float) end end) r_total_per_customer,
(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_total_services &lt; 0 then -r_table.r_total_services/cast((r_table.type1AmountCustomer - r_table.type2AmountCustomer) as float) else  r_table.r_total_services/cast((r_table.type1AmountCustomer - r_table.type2AmountCustomer) as float) end end) r_total_services_per_customer,
(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_total_products &lt; 0 then -r_table.r_total_products/cast((r_table.type1AmountCustomer - r_table.type2AmountCustomer) as float) else  r_table.r_total_products/cast((r_table.type1AmountCustomer - r_table.type2AmountCustomer) as float) end end) r_total_product_per_customer,

r_table.r_cnt_dt r_cnt_dt

	
FROM(
select
            p.tenant_no r_tenant_no,
			wr.operating_employee_no r_employee_no,
			e.employee_nm r_employee_nm,
			
			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 and 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_g1_products,
			sum(case when coalesce(h.customer_group_no, 1) = 2 and 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_g2_products,
			sum(case when coalesce(h.customer_group_no, 1) = 3 and 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_g3_products,
			
			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
			


			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
			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 
			left join employees e on e.tenant_no = h.tenant_no and h.company_no = e.company_no and wr.operating_employee_no= e.employee_no
			where  h.tenant_no=@TENANT_NO and h.pos_cd=@POS_CD and  h.sales_inv_id in (select sales_inv_id from sales_invs where tenant_no=@TENANT_NO and pos_cd=@POS_CD AND cast (sales_inv_ts as DATE) &gt;= cast ({fn TIMESTAMPADD( SQL_TSI_YEAR, -1,cast (@DATE as Date))} as DATE) AND  cast (sales_inv_ts as DATE) &lt;= cast ({fn TIMESTAMPADD( SQL_TSI_YEAR, -1,cast (@TODATE as Date) )} as DATE) )
			 
			   GROUP BY  p.tenant_no, wr.operating_employee_no,e.employee_nm
			   ) r_table
			   ) sub_table
			   
			   GROUP BY  sub_table.r_tenant_no
			   
			  

		</sql>
	</report-dataset>
	
</report>
