SELECT K.FKTO, K.BKTO_ID, SUM (CASE WHEN E.CH_GEBGRUPPE_ID = 3 then E.GPREIS else 0 end) AS FIX, 
	SUM (CASE WHEN CH_GEBGRUPPE_ID = 5 then GPREIS else 0 end) AS VAR
FROM EPOS E
LEFT JOIN RECH_JJJJ_KENNUNG K ON E.RECH_ID = K.ID_RECH
GROUP BY K.FKTO, K.BKTO_ID

SELECT E.ID_EPOS, E.RECH_ID, R2.ID_RECH AS REF_RECH_ID,
'UPDATE EPOS SET REF_RECH_ID = ' + cast(R2.ID_RECH as varchar) 
+ ' where ID_EPOS = ' + cast(E.ID_EPOS AS varchar) 
FROM EPOS E
LEFT JOIN RECH_JJJJ_KENNUNG R1 ON E.RECH_ID = R1.ID_RECH
LEFT JOIN RECH_JJJJ_KENNUNG R2 ON R1.RENU = R2.RENU AND R2.CH_TYP_RB = 'R'

SELECT E.ID_EPOS, E.RECH_ID, R1.FKTO AS FKTO,R1.BKTO_ID AS BKTO_ID,
'UPDATE EPOS SET FKTO = ''' + cast(R1.FKTO as varchar) 
+ ''', BKTO_ID = ' + cast(R1.BKTO_ID as varchar) + ' where ID_EPOS = ' + cast(E.ID_EPOS AS varchar) 
FROM EPOS E
LEFT JOIN RECH_JJJJ_KENNUNG R1 ON E.RECH_ID = R1.ID_RECH
LEFT JOIN RECH_JJJJ_KENNUNG R2 ON R1.RENU = R2.RENU AND R2.CH_TYP_RB = 'R'

ALTER view [dbo].[V_RECH_JJJJ_KENNUNG] AS 
SELECT [ID_RECH]
      ,[IN_VIEW]
      ,[CH_TYP_RB]
      ,[RFN_ID]
      ,[FKTO]
      ,[BKTO_ID]
      ,[RENU]
      ,[DA_RECHDATUM]
      ,[CH_RECHJAHR]
      ,[CH_RECHMONAT]
  FROM [TELIS].[dbo].[V_RECH_JJJJ_KENNUNG_0]

UNION ALL   
SELECT [ID_RECH]
      ,[IN_VIEW]
      ,[CH_TYP_RB]
      ,[RFN_ID]
      ,[FKTO]
      ,[BKTO_ID]
      ,[RENU]
      ,[DA_RECHDATUM]
      ,[CH_RECHJAHR]
      ,[CH_RECHMONAT]
	  FROM RECH_JJJJ_KENNUNG
	  where CH_TYP_RB = 'R'
	  
	  
SELECT K.FKTO, K.BKTO_ID, 
'UPDATE EPOS SET FKTO = ''' + cast(K.FKTO as varchar) + ''', BKTO_ID = ' + cast(K.BKTO_ID as bigint) + ' WHERE E.REF_RECH_ID = ' cast(K.ID_RECH as bigint) 
FROM RECH_JJJJ_KENNUNG K 
LEFT JOIN EPOS E ON E.REF_RECH_ID = K.ID_RECH 	  