CREATE FUNCTION [dbo].[fnRKCAccrEvidInd](@UOrgID decimal(16,4), @BldID bigint, @D1 smalldatetime, @D2 smalldatetime)
RETURNS @R TABLE 
(
  cdate date NOT NULL,
  acc_id decimal(19,7) NOT NULL,
  ptype_id tinyint NOT NULL,
  cvol2_11 decimal(12,6),
  cvol2_21 decimal(12,6),
  cvol2_22 decimal(12,6),
  cvol2_31 decimal(12,6),
  cvol2_32 decimal(12,6),
  cvol2_33 decimal(12,6),
  cvol2 AS ISNULL(cvol2_11,0.0) + ISNULL(cvol2_21,0.0) + ISNULL(cvol2_22,0.0) + ISNULL(cvol2_31,0.0) + ISNULL(cvol2_32,0.0) + ISNULL(cvol2_33,0.0)
  PRIMARY KEY CLUSTERED (cdate, acc_id, ptype_id)
)
AS 
BEGIN  

  INSERT INTO @R

  SELECT 

  T4.cdate,  
  T4.acc_id,
  T4.ptype_id,
  cvol2_11 = SUM(T4.dvol_11) OVER (PARTITION BY T4.acc_id, T4.ptype_id ORDER BY T4.acc_id, T4.ptype_id),
  cvol2_21 = SUM(T4.dvol_21) OVER (PARTITION BY T4.acc_id, T4.ptype_id ORDER BY T4.acc_id, T4.ptype_id),
  cvol2_22 = SUM(T4.dvol_22) OVER (PARTITION BY T4.acc_id, T4.ptype_id ORDER BY T4.acc_id, T4.ptype_id),
  cvol2_31 = SUM(T4.dvol_32) OVER (PARTITION BY T4.acc_id, T4.ptype_id ORDER BY T4.acc_id, T4.ptype_id),
  cvol2_32 = SUM(T4.dvol_32) OVER (PARTITION BY T4.acc_id, T4.ptype_id ORDER BY T4.acc_id, T4.ptype_id),
  cvol2_33 = SUM(T4.dvol_33) OVER (PARTITION BY T4.acc_id, T4.ptype_id ORDER BY T4.acc_id, T4.ptype_id)

  FROM (
 
  SELECT
  T3.cdate,
  T3.acc_id,
  T3.ptype_id,
  points_count = COUNT(T3.point_id),
  evid_count = SUM(CASE WHEN ctar_id IS NULL THEN 0 ELSE 1 END),
  
  dvol_11 = SUM(CASE WHEN T3.ctar_id = 1 THEN CASE WHEN T3.clevel_id = 2 THEN T3.dvol_1 * (CASE WHEN T3.INHS = 0 THEN T3.ashare_a ELSE CAST(T3.INH as decimal(5,1)) / CAST(T3.INHS as decimal(5,1)) END) ELSE T3.dvol_1 END ELSE NULL END),
  dvol_21 = SUM(CASE WHEN T3.ctar_id = 2 THEN CASE WHEN T3.clevel_id = 2 THEN T3.dvol_1 * (CASE WHEN T3.INHS = 0 THEN T3.ashare_a ELSE CAST(T3.INH as decimal(5,1)) / CAST(T3.INHS as decimal(5,1)) END) ELSE T3.dvol_1 END ELSE NULL END),
  dvol_22 = SUM(CASE WHEN T3.ctar_id = 2 THEN CASE WHEN T3.clevel_id = 2 THEN T3.dvol_2 * (CASE WHEN T3.INHS = 0 THEN T3.ashare_a ELSE CAST(T3.INH as decimal(5,1)) / CAST(T3.INHS as decimal(5,1)) END) ELSE T3.dvol_2 END ELSE NULL END),
  dvol_31 = SUM(CASE WHEN T3.ctar_id = 3 THEN CASE WHEN T3.clevel_id = 2 THEN T3.dvol_1 * (CASE WHEN T3.INHS = 0 THEN T3.ashare_a ELSE CAST(T3.INH as decimal(5,1)) / CAST(T3.INHS as decimal(5,1)) END) ELSE T3.dvol_1 END ELSE NULL END),
  dvol_32 = SUM(CASE WHEN T3.ctar_id = 3 THEN CASE WHEN T3.clevel_id = 2 THEN T3.dvol_2 * (CASE WHEN T3.INHS = 0 THEN T3.ashare_a ELSE CAST(T3.INH as decimal(5,1)) / CAST(T3.INHS as decimal(5,1)) END) ELSE T3.dvol_2 END ELSE NULL END),
  dvol_33 = SUM(CASE WHEN T3.ctar_id = 3 THEN CASE WHEN T3.clevel_id = 2 THEN T3.dvol_3 * (CASE WHEN T3.INHS = 0 THEN T3.ashare_a ELSE CAST(T3.INH as decimal(5,1)) / CAST(T3.INHS as decimal(5,1)) END) ELSE T3.dvol_3 END ELSE NULL END)

  FROM (

  SELECT
  CL.cdate,
  PA.flat_id,
  T2.acc_id,
  T2.ptype_id,
  T2.point_id,
  T2.clevel_id,
  T2.ctar_id,
  T2.dvol_1,
  T2.dvol_2, 
  T2.dvol_3,
  PR.INH,
  PR.INHS,
  ashare_a = ISNULL(PP.pacc_share,1.0),
  pacc_share = ISNULL(PP.pacc_share,1.0)

  FROM Calendar CL

  INNER JOIN (

  SELECT 
  T1.acc_id,
  T1.point_id,
  T1.clevel_id,
  T1.ptype_id,
  T1.pbeg_date,
  T1.pend_date,
  T1.levi_date,
  T1.evid_date,
  T1.ctar_id,   
  dvol_1 = CASE WHEN T1.rep_date = @D1 THEN CAST(T1.vol_1 / T1.days_cnt AS decimal(20,11)) ELSE 0.0 END, 
  dvol_2 = CASE WHEN T1.rep_date = @D1 THEN CAST(T1.vol_2 / T1.days_cnt AS decimal(20,11)) ELSE 0.0 END,  
  dvol_3 = CASE WHEN T1.rep_date = @D1 THEN CAST(T1.vol_3 / T1.days_cnt AS decimal(20,11)) ELSE 0.0 END 
  FROM (
  
  SELECT
  PA.acc_id,
  CP.point_id,
  CP.clevel_id,
  CP.ptype_id,
  pbeg_date = CP.beg_date, 
  pend_date = ISNULL(CP.end_date,'20400101'), 
  CE.ctar_id,   
  CE.levi_date,  
  rep_date = dbo.fnMonthFirstDay(CE.evid_date),
  CE.evid_date,
  CE.vol_1,
  CE.vol_2, 
  CE.vol_3,
  days_cnt = DATEDIFF(day,dbo.MaxDate(CE.levi_date,dbo.fnMonthFirstDay(CE.evid_date)),CE.evid_date) + 1
  FROM Pers_accounts PA 
  INNER JOIN (--так быстрее в 10 раз
  SELECT 
  PA.acc_id,
  CP.point_id
  FROM Counter_points CP
  INNER JOIN Pers_accounts PA ON (CP.clevel_id = 2 AND CP.build_id = PA.build_id AND CP.flat_id = PA.flat_id)
  UNION
  SELECT 
  PA.acc_id,
  CP.point_id
  FROM Counter_points CP
  INNER JOIN Pers_accounts PA ON (CP.clevel_id = 3 AND CP.acc_id = PA.acc_id)
  ) MP ON (PA.acc_id = MP.acc_id)
  INNER JOIN Counter_points CP ON (MP.point_id = CP.point_id)
  LEFT OUTER JOIN (    
	SELECT 
    CN.point_id,
    CN.count_id,
	CS.ctar_id,
    levi_date = LAG(CE.evid_date,1,CN.beg_date) OVER (PARTITION BY CE.count_id ORDER BY CE.count_id),
    CE.evid_date,
	CE.vol_1,
    CE.vol_2, 
    CE.vol_3
    FROM Counter_evid CE
	INNER JOIN Counters CN ON (CE.count_id = CN.count_id)
	INNER JOIN Counter_points CP ON (CP.point_id = CN.point_id)
	INNER JOIN Counter_series CS ON (CN.cseries_id = CS.cseries_id)
	WHERE CE.evid_date >= @D1
	  AND CE.evid_date <= ISNULL(CN.ncheck_date,'20400101') --дата поверки
	  AND CE.evid_date BETWEEN CP.beg_date AND ISNULL(CP.end_date,'20400101')
	  AND CE.evid_date BETWEEN CN.beg_date AND ISNULL(CN.end_date,'20400101')
	  AND CN.beg_date <= @D2 AND ISNULL(CN.end_date,'20400101') >= @D1	  	  
    ) CE ON (CP.point_id = CE.point_id)

  WHERE (CP.beg_date <= @D2) AND (ISNULL(CP.end_date,'20400101') >= @D1)
    AND (PA.build_id = @BldID)	
   
  ) T1
  
  ) T2 ON ((T2.evid_date IS NULL) OR (CL.cdate BETWEEN T2.levi_date AND T2.evid_date))
	AND (CL.cdate BETWEEN T2.pbeg_date AND T2.pend_date)

  INNER JOIN Pers_accounts PA ON (T2.acc_id = PA.acc_id)
    AND (PA.beg_date <= CL.cdate AND ISNULL(PA.end_date,'20400101') >= CL.cdate)
  
  LEFT OUTER JOIN PAcc_params PP ON (
	(T2.acc_id = PP.acc_id) AND	
	(PP.beg_date = (SELECT MAX(beg_date) FROM PAcc_params WHERE (acc_id = T2.acc_id) AND beg_date <= CL.cdate))
	) 
	
  LEFT OUTER JOIN (
    SELECT
	PR.cdate,
	PR.acc_id,
	INH = ISNULL(PR.INH,0),
    INHS = ISNULL((SUM(INH) over(PARTITION BY PR.cdate, PA.flat_id)),0)
	FROM dbo.fnRKCAccrRegM(@UOrgID,@BldID,@D1,@D2) PR
	INNER JOIN Pers_accounts PA ON (PR.acc_id = PA.acc_id)
	) PR ON (CL.CDate = PR.cdate) AND (T2.acc_id = PR.acc_id)  

  WHERE (CL.cdate BETWEEN @D1 AND @D2)
    AND (PA.org_id = @UOrgID) 

  ) T3

  GROUP BY T3.cdate, T3.acc_id, T3.ptype_id

  ) T4

  WHERE T4.points_count = T4.evid_count

  RETURN;
END