<?xml version="1.0"?>

<report name="cash-report" pdf-renderer="HitlisteSalon.xsl" csv-renderer="HitlisteSalonCSV.xsl">
	<title>Umsatz 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-transaction report-nm="posRevenue" report-transaction="de.timeglobe.reportsnew.reports.HitlistSalonNew2">
	</report-transaction>
	
<!-- 	<report-result report-dataset="sumBuRevenue">
	</report-result> -->
	<!-- <report-result report-dataset="sumBuRevenueSalon">
	</report-result>	-->
	<report-result report-dataset="departments">
	</report-result>
		<!--<report-result report-dataset="sumPrevYearBuRevenueSalon">
	</report-result>
	-->



	<report-result report-dataset="companies">
	</report-result>
	
	<report-dataset name="businessunits">
		<sql>
			select * from businessunits where tenant_no=@TENANT_NO 
		</sql>
	</report-dataset>

<report-dataset name="companies">
		<sql>
			select * from companies where tenant_no=@TENANT_NO  
		</sql>
	</report-dataset>
<report-dataset name="departments">
		<sql>
			select * from departments where tenant_no=@TENANT_NO  
		</sql>
	</report-dataset>
	

	

	
		<report-dataset name="sumBuRevenueOld">
		<sql>
	SELECT 
sub_table.r_pos_cd r_pos_cd,
sub_table.r_company_no r_company_no,
sub_table.r_department_no r_department_no,
sub_table.r_businessunit_no r_businessunit_no,


sub_table.r_total_g1_services r_total_g1_services,
sub_table.r_total_g2_services r_total_g2_services,
sub_table.r_total_g3_services r_total_g3_services,

sub_table.r_total r_total,
sub_table.r_total_products r_total_products,

(sub_table.r_total_per_day_per_employee) r_total_per_day_per_employee,
(sub_table.r_total_services_per_day_per_employee) r_total_services_per_day_per_employee,
(sub_table.r_total_product_per_day_per_employee) r_total_product_per_day_per_employee,
(sub_table.r_amount_customer_per_day_per_employee) r_amount_customer_per_day_per_employee


	
FROM(
		SELECT 
r_table.r_pos_cd r_pos_cd,
r_table.r_company_no r_company_no,
r_table.r_department_no r_department_no,
r_table.r_businessunit_no r_businessunit_no,
 
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 r_total,
r_table.r_total_products r_total_products,

case when coalesce(cntETable.cntEmpl,0) = 0 THEN 0 else (r_table.r_total/cast(r_table.r_cnt_dt as float)/cast (cntETable.cntEmpl as float)) END r_total_per_day_per_employee,
case when coalesce(cntETable.cntEmpl,0) = 0 THEN 0 else (r_table.r_total_services/cast(r_table.r_cnt_dt as float)/cast (cntETable.cntEmpl as float)) END r_total_services_per_day_per_employee,
case when coalesce(cntETable.cntEmpl,0) = 0 THEN 0 else (r_table.r_total_products/cast(r_table.r_cnt_dt as float)/cast (cntETable.cntEmpl as float)) END r_total_product_per_day_per_employee,
case when coalesce(cntETable.cntEmpl,0) = 0 THEN 0 else ((r_table.type1AmountCustomer - r_table.type2AmountCustomer)/cast(r_table.r_cnt_dt as float) /cast (cntETable.cntEmpl as float)) END r_amount_customer_per_day_per_employee
,coalesce(cntETable.cntEmpl,0) cntEmpl

FROM (
select
            h.pos_cd r_pos_cd,
            h.company_no r_company_no,
            h.department_no r_department_no,
            h.businessunit_no r_businessunit_no,
		
			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,
		
	
			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
			
			from sales_inv_positions p 
			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 
		
			join (SELECT b.tenant_no
	            ,b.company_no
	            ,b.department_no
	            ,b.businessunit_no 
	            ,b.pos_cd_hint 
		        FROM user_group_businessunit ugb 
		        join businessunits b 
		          on ugb.tenant_no = b.tenant_no 
		         AND ugb.company_no = b.company_no
		         AND ugb.department_no = b.department_no
		         AND ugb.businessunit_no = b.businessunit_no
		         
		        WHERE b.tenant_no=@TENANT_NO and b.company_no=coalesce(@COMPANY_NO ,b.company_no) 
		             AND ugb.user_group_cd IN ( SELECT user_group_cd 
		                                          FROM user_group_members
		                                           WHERE tenant_no =@TENANT_NO
		                                           AND login_nm = @USER_LOGIN_NM )
		          
		          	                                           
		       	GROUP BY b.tenant_no
            		    ,b.company_no
            			,b.department_no
               			,b.businessunit_no
                		,b.pos_cd_hint                    
	        ) buFilter  
	        on buFilter.tenant_no=h.tenant_no and buFilter.company_no=h.company_no and buFilter.department_no=h.department_no and buFilter.pos_cd_hint=h.pos_cd
			
			where h.tenant_no=@TENANT_NO  AND cast (h.sales_inv_ts as DATE) &gt;= cast (@DATE as DATE) AND  cast (h.sales_inv_ts as DATE) &lt;= cast (@TODATE as DATE) 
			 
			 	 AND @__BUSINESSUNIT_FILTER__h
			 
			   GROUP BY  h.pos_cd, h.company_no, h.department_no , h.businessunit_no 
			   ) r_table
			   
			   left JOIN (
	
								SELECT avl_employees_wf.tenant_no tenant_no
									,avl_employees_wf.pos_cd pos_cd

									,count(distinct sd.operating_employee_no) cntEmpl
							

								from sales_inv_positions p 
								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 sd on h.tenant_no = sd.tenant_no and h.pos_cd = sd.pos_cd and h.sales_dln_id = sd.sales_dln_id and p.sales_inv_position_id = sd.sales_dln_position_id
								JOIN
								(
								SELECT	e.tenant_no , b.pos_cd_hint as pos_cd, e.employee_no, max(coalesce(ec.work_factor,1.0)) workf
								FROM 
										employees e
								JOIN	employee_contracts ec
									on	e.tenant_no  = ec.tenant_no
									AND e.company_no = ec.company_no
									AND e.employee_no = ec.employee_no
								JOIN	businessunits b
								on		b.tenant_no = ec.tenant_no
									AND b.company_no = ec.company_no
									AND b.department_no = ec.department_no
									AND b.businessunit_no = ec.businessunit_no
								WHERE  cast(ec.valid_from as DATE ) &lt;= cast (@TODATE as DATE)    
								AND   cast(coalesce(ec.valid_to,CURRENT_TIMESTAMP) as DATE ) &gt;= cast (@DATE as DATE)    
								GROUP BY e.tenant_no ,b.pos_cd_hint, e.employee_no
								) avl_employees_wf

								on	sd.tenant_no = avl_employees_wf.tenant_no
								AND sd.pos_cd = avl_employees_wf.pos_cd
								AND sd.operating_employee_no = avl_employees_wf.employee_no

								where h.tenant_no=@TENANT_NO and cast (h.sales_inv_ts as DATE) &gt;= cast (@DATE as DATE) AND  cast (h.sales_inv_ts as DATE) &lt;= cast (@TODATE as DATE) 
								  AND avl_employees_wf.workf > 0.0
								GROUP BY avl_employees_wf.tenant_no 
									,avl_employees_wf.pos_cd
						


				) cntETable
				on r_table.r_pos_cd = cntETable.pos_cd


			   ) sub_table
			   
			   
			   ORDER BY (sub_table.r_total_per_day_per_employee) DESC
		</sql>
	</report-dataset>
	
			<report-dataset name="sumBuRevenue">
		<sql>
	SELECT 
sub_table.r_pos_cd r_pos_cd,
sub_table.r_company_no r_company_no,
sub_table.r_department_no r_department_no,
sub_table.r_businessunit_no r_businessunit_no,


sub_table.r_total_g1_services r_total_g1_services,
sub_table.r_total_g2_services r_total_g2_services,
sub_table.r_total_g3_services r_total_g3_services,

sub_table.r_total r_total,
sub_table.r_total_products r_total_products,

(sub_table.r_total_per_day_per_employee) r_total_per_day_per_employee,
(sub_table.r_total_services_per_day_per_employee) r_total_services_per_day_per_employee,
(sub_table.r_total_product_per_day_per_employee) r_total_product_per_day_per_employee,
(sub_table.r_amount_customer_per_day_per_employee) r_amount_customer_per_day_per_employee


	
FROM(
		SELECT 
r_table.r_pos_cd r_pos_cd,
r_table.r_company_no r_company_no,
r_table.r_department_no r_department_no,
r_table.r_businessunit_no r_businessunit_no,
 
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 r_total,
r_table.r_total_products r_total_products,

case when coalesce(cntETable.cntEmpl,0) = 0 THEN 0 else (r_table.r_total_per_day/cast (cntETable.cntEmpl as float)) END r_total_per_day_per_employee,
case when coalesce(cntETable.cntEmpl,0) = 0 THEN 0 else (r_table.r_total_services_per_day/cast (cntETable.cntEmpl as float)) END r_total_services_per_day_per_employee,
case when coalesce(cntETable.cntEmpl,0) = 0 THEN 0 else (r_table.r_total_product_per_day/cast (cntETable.cntEmpl as float)) END r_total_product_per_day_per_employee,
case when coalesce(cntETable.cntEmpl,0) = 0 THEN 0 else (r_table.r_amount_customer_per_day /cast (cntETable.cntEmpl as float)) END r_amount_customer_per_day_per_employee
,coalesce(cntETable.cntEmpl,0) cntEmpl

FROM (

SELECT 
	z_table.r_pos_cd r_pos_cd,
	z_table.r_company_no r_company_no,
	z_table.r_department_no r_department_no,
	z_table.r_businessunit_no r_businessunit_no,
	
	sum(z_table.r_total_g1_services)  r_total_g1_services,
	sum(z_table.r_total_g2_services)  r_total_g2_services,
	sum(z_table.r_total_g3_services)  r_total_g3_services,

	sum(z_table.r_total) r_total,
	sum(z_table.r_total_products) r_total_products,

	sum(z_table.r_total/cast(z_table.r_cnt_dt as float) ) r_total_per_day,
	sum(z_table.r_total_services/cast(z_table.r_cnt_dt as float) ) r_total_services_per_day,
	sum(z_table.r_total_products/cast(z_table.r_cnt_dt as float) ) r_total_product_per_day,
	sum((z_table.type1AmountCustomer - z_table.type2AmountCustomer)/cast(z_table.r_cnt_dt as float) ) r_amount_customer_per_day
 
FROM (
	select
            h.pos_cd r_pos_cd,
            h.company_no r_company_no,
            h.department_no r_department_no,
            h.businessunit_no r_businessunit_no,
            wr.operating_employee_no,
		
			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,
		
	
			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
			
			from sales_inv_positions p 
			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 
		
			join (SELECT b.tenant_no
	            ,b.company_no
	            ,b.department_no
	            ,b.businessunit_no 
	            ,b.pos_cd_hint 
		        FROM user_group_businessunit ugb 
		        join businessunits b 
		          on ugb.tenant_no = b.tenant_no 
		         AND ugb.company_no = b.company_no
		         AND ugb.department_no = b.department_no
		         AND ugb.businessunit_no = b.businessunit_no
		         
		        WHERE b.tenant_no=@TENANT_NO and b.company_no=coalesce(@COMPANY_NO ,b.company_no) 
		             AND ugb.user_group_cd IN ( SELECT user_group_cd 
		                                          FROM user_group_members
		                                           WHERE tenant_no =@TENANT_NO
		                                           AND login_nm = @USER_LOGIN_NM )
		          
		          	                                           
		       	GROUP BY b.tenant_no
            		    ,b.company_no
            			,b.department_no
               			,b.businessunit_no
                		,b.pos_cd_hint                    
	        ) buFilter  
	        on buFilter.tenant_no=h.tenant_no and buFilter.company_no=h.company_no and buFilter.department_no=h.department_no and buFilter.pos_cd_hint=h.pos_cd
			
			where h.tenant_no=@TENANT_NO  AND cast (h.sales_inv_ts as DATE) &gt;= cast (@DATE as DATE) AND  cast (h.sales_inv_ts as DATE) &lt;= cast (@TODATE as DATE) 
			 
			 	 AND @__BUSINESSUNIT_FILTER__h
			 
			   GROUP BY  h.pos_cd, h.company_no, h.department_no , h.businessunit_no,    wr.operating_employee_no 
		  ) z_table
			   
		 GROUP BY   z_table.r_pos_cd,  z_table.r_company_no,  z_table.r_department_no , z_table.r_businessunit_no   
	) r_table 
   left JOIN (

					SELECT avl_employees_wf.tenant_no tenant_no
						,avl_employees_wf.pos_cd pos_cd

						,count(distinct sd.operating_employee_no) cntEmpl
				

					from sales_inv_positions p 
					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 sd on h.tenant_no = sd.tenant_no and h.pos_cd = sd.pos_cd and h.sales_dln_id = sd.sales_dln_id and p.sales_inv_position_id = sd.sales_dln_position_id
					JOIN
					(
					SELECT	e.tenant_no , b.pos_cd_hint as pos_cd, e.employee_no, max(coalesce(ec.work_factor,1.0)) workf
					FROM 
							employees e
					JOIN	employee_contracts ec
						on	e.tenant_no  = ec.tenant_no
						AND e.company_no = ec.company_no
						AND e.employee_no = ec.employee_no
					JOIN	businessunits b
					on		b.tenant_no = ec.tenant_no
						AND b.company_no = ec.company_no
						AND b.department_no = ec.department_no
						AND b.businessunit_no = ec.businessunit_no
					WHERE  cast(ec.valid_from as DATE ) &lt;= cast (@TODATE as DATE)    
					AND   cast(coalesce(ec.valid_to,CURRENT_TIMESTAMP) as DATE ) &gt;= cast (@DATE as DATE)    
					GROUP BY e.tenant_no ,b.pos_cd_hint, e.employee_no
					) avl_employees_wf

					on	sd.tenant_no = avl_employees_wf.tenant_no
					AND sd.pos_cd = avl_employees_wf.pos_cd
					AND sd.operating_employee_no = avl_employees_wf.employee_no

					where h.tenant_no=@TENANT_NO and cast (h.sales_inv_ts as DATE) &gt;= cast (@DATE as DATE) AND  cast (h.sales_inv_ts as DATE) &lt;= cast (@TODATE as DATE) 
					  AND avl_employees_wf.workf > 0.0
					GROUP BY avl_employees_wf.tenant_no 
						,avl_employees_wf.pos_cd
			


	) cntETable
	on r_table.r_pos_cd = cntETable.pos_cd


			   ) sub_table
			   
			   
			   ORDER BY (sub_table.r_total_per_day_per_employee) DESC
		</sql>
	</report-dataset>
	
	
			<report-dataset name="sumBuRevenueSalon">
		<sql>

		SELECT 
         r_table.r_pos_cd r_pos_cd,
         r_table.r_company_no r_company_no,
         r_table.r_department_no r_department_no,
         r_table.r_businessunit_no r_businessunit_no,
         r_table.r_total r_total,
         r_table.r_total_products r_total_products,
         (r_table.type1AmountCustomer - r_table.type2AmountCustomer) r_amount_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 &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,
  		 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
FROM (
select
            h.pos_cd r_pos_cd,
            h.company_no r_company_no,
            h.department_no r_department_no,
            h.businessunit_no r_businessunit_no,
            
			
			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,
		
	
			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)=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
			from sales_inv_positions p 
			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 
			join (SELECT b.tenant_no
	            ,b.company_no
	            ,b.department_no
	            ,b.businessunit_no 
	            ,b.pos_cd_hint 
		        FROM user_group_businessunit ugb 
		        join businessunits b 
		          on ugb.tenant_no = b.tenant_no 
		         AND ugb.company_no = b.company_no
		         AND ugb.department_no = b.department_no
		         AND ugb.businessunit_no = b.businessunit_no
		         
		        WHERE b.tenant_no=@TENANT_NO and b.company_no=coalesce(@COMPANY_NO ,b.company_no) 
		             AND ugb.user_group_cd IN ( SELECT user_group_cd 
		                                          FROM user_group_members
		                                           WHERE tenant_no =@TENANT_NO
		                                           AND login_nm = @USER_LOGIN_NM )
		                                           
		         	                                           
		       	GROUP BY b.tenant_no
            		    ,b.company_no
            			,b.department_no
               			,b.businessunit_no
                		,b.pos_cd_hint                    
	        ) buFilter  
	        on buFilter.tenant_no=h.tenant_no and buFilter.company_no=h.company_no and buFilter.department_no=h.department_no and buFilter.pos_cd_hint=h.pos_cd
			
			where h.tenant_no=@TENANT_NO  AND cast (h.sales_inv_ts as DATE) &gt;= cast (@DATE as DATE) AND  cast (h.sales_inv_ts as DATE) &lt;= cast (@TODATE as DATE) 
			 	 AND @__BUSINESSUNIT_FILTER__h
			   GROUP BY  h.pos_cd, h.company_no, h.department_no , h.businessunit_no 
			   ) r_table
			   
			    

		</sql>
	</report-dataset>
	
		<report-dataset name="sumPrevYearBuRevenueSalon">
		<sql>

		SELECT 
         r_table.r_pos_cd r_pos_cd,
         r_table.r_company_no r_company_no,
         r_table.r_department_no r_department_no,
         r_table.r_businessunit_no r_businessunit_no,
         r_table.r_total r_total,
         r_table.r_total_products r_total_products,
       	 (r_table.type1AmountCustomer - r_table.type2AmountCustomer) r_amount_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 &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

FROM (
select
            h.pos_cd r_pos_cd,
            h.company_no r_company_no,
            h.department_no r_department_no,
            h.businessunit_no r_businessunit_no,
     		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,
		
			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)=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
			from sales_inv_positions p 
			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 
			join (SELECT b.tenant_no
	            ,b.company_no
	            ,b.department_no
	            ,b.businessunit_no 
	            ,b.pos_cd_hint 
		        FROM user_group_businessunit ugb 
		        join businessunits b 
		          on ugb.tenant_no = b.tenant_no 
		         AND ugb.company_no = b.company_no
		         AND ugb.department_no = b.department_no
		         AND ugb.businessunit_no = b.businessunit_no
		         
		        WHERE b.tenant_no=@TENANT_NO and b.company_no=coalesce(@COMPANY_NO ,b.company_no) 
		             AND ugb.user_group_cd IN ( SELECT user_group_cd 
		                                          FROM user_group_members
		                                           WHERE tenant_no =@TENANT_NO
		                                           AND login_nm = @USER_LOGIN_NM )
		                                           
		        GROUP BY b.tenant_no
            		    ,b.company_no
            			,b.department_no
               			,b.businessunit_no
                		,b.pos_cd_hint        
	        ) buFilter  
	        on buFilter.tenant_no=h.tenant_no and buFilter.company_no=h.company_no and buFilter.department_no=h.department_no and buFilter.pos_cd_hint=h.pos_cd
			
			where h.tenant_no=@TENANT_NO  AND cast (h.sales_inv_ts as DATE) &gt;= DATEADD(year ,-1,cast (@DATE as DATE)) AND  cast (h.sales_inv_ts as DATE) &lt;= DATEADD(year ,-1,cast (@TODATE as DATE))
			 	 AND @__BUSINESSUNIT_FILTER__h
			   GROUP BY  h.pos_cd, h.company_no, h.department_no , h.businessunit_no 
			   ) r_table
			   
			    

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