select 
xar.uzivatel AS uzivatel,
round(sum(xar.opravy_p),2) AS s_opravy_p,
round(sum(xar.opravy_v),2) AS s_opravy_v,
round(sum(xar.opravy_p-xar.opravy_v),2) AS r_opravy,
round(sum(xar.cin_sv_p),2) AS s_cin_sv_p,
round(sum(xar.cin_sv_v),2) AS s_cin_sv_v,
round(sum(xar.cin_sv_p-xar.cin_sv_v),2) AS r_cin_sv,
round(sum(xar.poj_maj_p),2) AS s_poj_maj_p,
round(sum(xar.poj_maj_v),2) AS s_poj_maj_v,
round(sum(xar.poj_maj_p-xar.poj_maj_v),2) AS r_poj_maj,
round(sum(xar.dan_z_nem_p),2) AS s_dan_z_nem_p,
round(sum(xar.dan_z_nem_v),2) AS s_dan_z_nem_v,
round(sum(xar.dan_z_nem_p-xar.dan_z_nem_v),2) AS r_dan_z_nem,
round(sum(xar.plat_pp_p),2) AS s_plat_pp_p,
round(sum(xar.plat_pp_v),2) AS s_plat_pp_v,
round(sum(xar.plat_pp_p-xar.plat_pp_v),2) AS r_plat_pp,
round(sum(xar.ved_uce_p),2) AS s_ved_uce_p,
round(sum(xar.ved_uce_v),2) AS s_ved_uce_v,
round(sum(xar.ved_uce_p-xar.ved_uce_v),2) AS r_ved_uce,
round(sum(xar.teplo_p),2) AS s_teplo_p,
round(sum(xar.teplo_v),2) AS s_teplo_v,
round(sum(xar.teplo_p-xar.teplo_v),2) AS r_teplo,
round(sum(xar.ohrev_vody_p),2) AS s_ohrev_vody_p,
round(sum(xar.ohrev_vody_v),2) AS s_ohrev_vody_v,
round(sum(xar.ohrev_vody_p-xar.ohrev_vody_v),2) AS r_ohrev_vody,
round(sum(xar.vodne_p),2) AS s_vodne_p,
round(sum(xar.vodne_v),2) AS s_vodne_v,
round(sum(xar.vodne_p-xar.vodne_v),2) AS r_vodne,
round(sum(xar.el_spol_porst_p),2) AS s_el_spol_porst_p,
round(sum(xar.el_spol_porst_v),2) AS s_el_spol_porst_v,
round(sum(xar.el_spol_porst_p-xar.el_spol_porst_v),2) AS r_el_spol_porst,
round(sum(xar.st_vod_oh_p),2) AS s_st_vod_oh_p,
round(sum(xar.st_vod_oh_v),2) AS s_st_vod_oh_v,
round(sum(xar.st_vod_oh_p-xar.st_vod_oh_v),2) AS r_st_vod_oh,
round(sum(xar.vytah),2) AS s_vytah_p,
round(sum(xar.vytah_v),2) AS s_vytah_v,
round(sum(xar.vytah_p-xar.vytah_v),2) AS r_vytah,
round(sum(xar.anuita_p),2) AS s_anuita_p,
round(sum(xar.anuita_v),2) AS s_anuita_v,
round(sum(xar.anuita_p-xar.anuita_v),2) AS r_anuita,
round(sum(xar.ost_nakl_p),2) AS s_ost_nakl_p,
round(sum(xar.ost_nakl_v),2) AS s_ost_nakl_v,
round(sum(xar.ost_nakl_p-xar.ost_nakl_v),2) AS r_ost_nakl,
round(sum(xar.celk_p),2) AS s_celk_p,
round(sum(xar.celk_v),2) AS s_celk_v,
round(sum(xar.celk_p-xar.celk_v),2) AS s_rozdil,
round(sum(xar.ds_vyt_pl_d),2) AS s_ds_vyt_pl_d,
round(sum(xar.ds_podl_pl_d),2) AS s_ds_podl_pl_d,
round(sum(xar.ds_uzit_pl_d),2) AS s_ds_uzit_pl_d,
round(sum(xar.ds_sp_sv_d),2) AS s_ds_sp_sv_d,
round(sum(xar.ds_sp_sv_tv_d),2) AS s_ds_sp_sv_tv_d,
max(xar.ds_sp_oh_tv_d) AS s_ds_sp_oh_tv_d,
round(sum(xar.ds_sp_teplo_dilky_d),2) AS s_ds_sp_teplo_dilky_d,
max(xar.ds_f_d_teplo_d) AS s_ds_f_d_teplo_d,
max(xar.ds_f_d_oh_tv_d) AS s_ds_f_d_oh_tv_d,
max(xar.ds_f_d_ce_sv_tv_d) AS s_ds_f_d_ce_sv_tv_d,
max(xar.ds_f_d_sv_d) AS s_ds_f_d_sv_d,
round(case when isnull(max(xar.ds_f_d_teplo_d),0) = 0 then 0 else sum(xar.ds_f_d_teplo_kc_d)/max(xar.ds_f_d_teplo_d) end,2) AS s_ds_f_d_teplo_kcmj_d,
round(case when isnull(max(xar.ds_f_d_oh_tv_d),0) = 0 then 0 else sum(xar.ds_f_d_oh_tv_kc_d)/max(xar.ds_f_d_oh_tv_d) end,2) AS s_ds_f_d_oh_tv_kcmj_d,
round(case when isnull(max(xar.ds_f_d_ce_sv_tv_d),0) = 0 then 0 else sum(xar.ds_f_d_ce_sv_tv_kc_d)/max(xar.ds_f_d_ce_sv_tv_d) end,2) AS s_ds_f_d_ce_sv_tv_kcmj_d,
round(case when isnull(max(xar.ds_f_d_sv_d),0) = 0 then 0 else sum(xar.ds_f_d_sv_kc_d)/max(xar.ds_f_d_sv_d) end,2) AS s_ds_f_d_sv_kcmj_d,
round(sum(xar.ds_f_d_teplo_kc_d),2) AS s_ds_f_d_teplo_kc_d,
round(sum(xar.ds_f_d_oh_tv_kc_d),2) AS s_ds_f_d_oh_tv_kc_d,
round(sum(xar.ds_f_d_ce_sv_tv_kc_d),2) AS s_ds_f_d_ce_sv_tv_kc_d,
round(sum(xar.ds_f_d_sv_kc_d),2) AS s_ds_f_d_sv_kc_d,
max(xar.ds_rok) AS s_ds_rok,
max(xar.ds_vlastnik) AS s_ds_vlastnik,
max(xar.ds_vs) AS s_ds_vs,
max(xar.ds_adresa) AS s_ds_adresa,
round(sum(xar.ds_sp_sv_b),3) AS s_ds_sp_sv_b,
round(sum(xar.ds_sp_tv_b),3) AS s_ds_sp_tv_b,
round(sum(xar.ds_vyt_pl_b),2) AS s_ds_vyt_pl_b,
round(sum(xar.ds_podl_pl_b),2) AS s_ds_podl_pl_b,
round(sum(xar.ds_uzit_pl_b),2) AS s_ds_uzit_pl_b,
max(xar.ds_vod_1_cislo_b) AS s_ds_vod_1_cislo_b,
max(xar.ds_vod_1_dat_poc_b) AS s_ds_vod_1_dat_poc_b,
replace(max(xar.ds_vod_1_stav_poc_b),'.',',') AS s_ds_vod_1_stav_poc_b,
max(xar.ds_vod_1_dat_kon_b) AS s_ds_vod_1_dat_kon_b,
replace(max(xar.ds_vod_1_stav_kon_b),'.',',') AS s_ds_vod_1_stav_kon_b,
replace(max(xar.ds_vod_1_spot_b),'.',',') AS s_ds_vod_1_spot_b,
max(xar.ds_vod_2_cislo_b) AS s_ds_vod_2_cislo_b,
max(xar.ds_vod_2_dat_poc_b) AS s_ds_vod_2_dat_poc_b,
replace(max(xar.ds_vod_2_stav_poc_b),'.',',') AS s_ds_vod_2_stav_poc_b,
max(xar.ds_vod_2_dat_kon_b) AS s_ds_vod_2_dat_kon_b,
replace(max(xar.ds_vod_2_stav_kon_b),'.',',') AS s_ds_vod_2_stav_kon_b,
replace(max(xar.ds_vod_2_spot_b),'.',',') AS s_ds_vod_2_spot_b,
max(xar.ds_vod_3_cislo_b) AS s_ds_vod_3_cislo_b,
max(xar.ds_vod_3_dat_poc_b) AS s_ds_vod_3_dat_poc_b,
replace(max(xar.ds_vod_3_stav_poc_b),'.',',') AS s_ds_vod_3_stav_poc_b,
max(xar.ds_vod_3_dat_kon_b) AS s_ds_vod_3_dat_kon_b,
replace(max(xar.ds_vod_3_stav_kon_b),'.',',') AS s_ds_vod_3_stav_kon_b,
replace(max(xar.ds_vod_3_spot_b),'.',',') AS s_ds_vod_3_spot_b,
max(xar.ds_vod_4_cislo_b) AS s_ds_vod_4_cislo_b,
max(xar.ds_vod_4_dat_poc_b) AS s_ds_vod_4_dat_poc_b,
replace(max(xar.ds_vod_4_stav_poc_b),'.',',') AS s_ds_vod_4_stav_poc_b,
max(xar.ds_vod_4_dat_kon_b) AS s_ds_vod_4_dat_kon_b,
replace(max(xar.ds_vod_4_stav_kon_b),'.',',') AS s_ds_vod_4_stav_kon_b,
replace(max(xar.ds_vod_4_spot_b),'.',',') AS s_ds_vod_4_spot_b,
round(sum(xar.ds_teplo_ss_mj_b),0) AS s_ds_teplo_ss_mj_b,
round(sum(xar.ds_teplo_ss_kc_b),2) AS s_ds_teplo_ss_kc_b,
round(sum(xar.ds_teplo_zs_mj_b),3) AS s_ds_teplo_zs_mj_b,
round(sum(xar.ds_teplo_zs_kc_b),2) AS s_ds_teplo_zs_kc_b,
round(sum(xar.ds_teplo_celk_kc_b),2) AS s_ds_teplo_celk_kc_b,
round(sum(xar.ds_oh_tv_ss_mj_b),3) AS s_ds_oh_tv_ss_mj_b,
round(sum(xar.ds_oh_tv_ss_kcmj_b),2) AS s_ds_oh_tv_ss_kcmj_b,
round(sum(xar.ds_oh_tv_ss_kc_b),2) AS s_ds_oh_tv_ss_kc_b,
round(sum(xar.ds_oh_tv_zs_mj_b),3) AS s_ds_oh_tv_zs_mj_b,
round(sum(xar.ds_oh_tv_zs_kcmj_b),2) AS s_ds_oh_tv_zs_kcmj_b,
round(sum(xar.ds_oh_tv_zs_kc_b),2) AS s_ds_oh_tv_zs_kc_b,
round(sum(xar.ds_oh_tv_celk_kc_b),2) AS s_ds_oh_tv_celk_kc_b,
round(sum(xar.ds_sv_tv_ss_mj_b),3) AS s_ds_sv_tv_ss_mj_b,
round(sum(xar.ds_sv_tv_ss_kcmj_b),2) AS s_ds_sv_tv_ss_kcmj_b,
round(sum(xar.ds_sv_tv_ss_kc_b),2) AS s_ds_sv_tv_ss_kc_b,
round(sum(xar.ds_sv_tv_zs_mj_b),3) AS s_ds_sv_tv_zs_mj_b,
round(sum(xar.ds_sv_tv_zs_mj_b),2) AS s_ds_sv_tv_zs_kcmj_b,
round(sum(xar.ds_sv_tv_zs_kc_b),2) AS s_ds_sv_tv_zs_kc_b,
round(sum(xar.ds_sv_tv_celk_kc_b),2) AS s_ds_sv_tv_celk_kc_b,
round(sum(xar.ds_sv_mj_b),3) AS s_ds_sv_mj_b,
round(sum(xar.ds_sv_kcmj_b),2) AS s_ds_sv_kcmj_b,
round(sum(xar.ds_sv_kc_b),2) AS s_ds_sv_kc_b,
round(sum(xar.ds_sv_celk_kc_b),2) AS s_ds_sv_celk_kc_b,
datepart(year,GetDate()) AS s_rok_vyuct
from (select 
max(firma.firma+', '+firma.ulice+', '+firma.psc+' '+firma.obec) AS uzivatel,
sum(case when cinnost.ids='Fond oprav' then predpis.kc else 0 end) AS opravy_p,
0 AS opravy_v,
sum(case when cinnost.ids='Čin. SV' then predpis.kc else 0 end) AS cin_sv_p,
0 AS cin_sv_v,
sum(case when cinnost.ids='Pojištění' then predpis.kc else 0 end) AS poj_maj_p,
0 AS poj_maj_v,
sum(case when cinnost.ids='Daň z nem.' then predpis.kc else 0 end) AS dan_z_nem_p,
0 AS dan_z_nem_v,
sum(case when cinnost.ids='Plat PP' then predpis.kc else 0 end) AS plat_pp_p,
0 AS plat_pp_v,
sum(case when cinnost.ids='Zprac. úč.' then predpis.kc else 0 end) AS ved_uce_p,
0 AS ved_uce_v,
sum(case when cinnost.ids='Teplo' then predpis.kc else 0 end) AS teplo_p,
0 AS teplo_v,
sum(case when cinnost.ids='Ohřev vody' then predpis.kc else 0 end) AS ohrev_vody_p,
0 AS ohrev_vody_v,
sum(case when cinnost.ids='Vodné' then predpis.kc else 0 end) AS vodne_p,
0 AS vodne_v,
sum(case when cinnost.ids='El. spol.' then predpis.kc else 0 end) AS el_spol_porst_p,
0 AS el_spol_porst_v,
sum(case when cinnost.ids='SV pro ohř' then predpis.kc else 0 end) AS st_vod_oh_p,
0 AS st_vod_oh_v,
sum(case when cinnost.ids='Výtah' then predpis.kc else 0 end) AS vytah_p,
0 AS vytah_v,
sum(case when cinnost.ids='Anuita' then predpis.kc else 0 end) AS anuita_p,
0 AS anuita_v,
sum(case when cinnost.ids='Ost. nákl.' then predpis.kc else 0 end) AS ost_nakl_p,
0 AS ost_nakl_v,
sum(predpis.kc) AS celk_p,
0 AS celk_v,
0 AS ds_vyt_pl_d,
0 AS ds_podl_pl_d,
0 AS ds_uzit_pl_d,
0 AS ds_sp_sv_d,
0 AS ds_sp_sv_tv_d,
0 AS ds_sp_oh_tv_d,
0 AS ds_sp_teplo_dilky_d,
0 AS ds_f_d_teplo_d,
0 AS ds_f_d_oh_tv_d,
0 AS ds_f_d_ce_sv_tv_d,
0 AS ds_f_d_sv_d,
0 AS ds_f_d_teplo_kc_d,
0 AS ds_f_d_oh_tv_kc_d,
0 AS ds_f_d_ce_sv_tv_kc_d,
0 AS ds_f_d_sv_kc_d,
'' AS ds_rok,
'' AS ds_vlastnik,
'' AS ds_vs,
'' AS ds_adresa,
0 AS ds_sp_sv_b,
0 AS ds_sp_tv_b,
0 AS ds_vyt_pl_b,
0 AS ds_podl_pl_b,
0 AS ds_uzit_pl_b,
'' AS ds_vod_1_cislo_b,
'' AS ds_vod_1_dat_poc_b,
'' AS ds_vod_1_stav_poc_b,
'' AS ds_vod_1_dat_kon_b,
'' AS ds_vod_1_stav_kon_b,
'' AS ds_vod_1_spot_b,
'' AS ds_vod_2_cislo_b,
'' AS ds_vod_2_dat_poc_b,
'' AS ds_vod_2_stav_poc_b,
'' AS ds_vod_2_dat_kon_b,
'' AS ds_vod_2_stav_kon_b,
'' AS ds_vod_2_spot_b,
'' AS ds_vod_3_cislo_b,
'' AS ds_vod_3_dat_poc_b,
'' AS ds_vod_3_stav_poc_b,
'' AS ds_vod_3_dat_kon_b,
'' AS ds_vod_3_stav_kon_b,
'' AS ds_vod_3_spot_b,
'' AS ds_vod_4_cislo_b,
'' AS ds_vod_4_dat_poc_b,
'' AS ds_vod_4_stav_poc_b,
'' AS ds_vod_4_dat_kon_b,
'' AS ds_vod_4_stav_kon_b,
'' AS ds_vod_4_spot_b,
0 AS ds_teplo_ss_mj_b,
0 AS ds_teplo_ss_kc_b,
0 AS ds_teplo_zs_mj_b,
0 AS ds_teplo_zs_kc_b,
0 AS ds_teplo_celk_kc_b,
0 AS ds_oh_tv_ss_mj_b,
0 AS ds_oh_tv_ss_kcmj_b,
0 AS ds_oh_tv_ss_kc_b,
0 AS ds_oh_tv_zs_mj_b,
0 AS ds_oh_tv_zs_kcmj_b,
0 AS ds_oh_tv_zs_kc_b,
0 AS ds_oh_tv_celk_kc_b,
0 AS ds_sv_tv_ss_mj_b,
0 AS ds_sv_tv_ss_kcmj_b,
0 AS ds_sv_tv_ss_kc_b,
0 AS ds_sv_tv_zs_mj_b,
0 AS ds_sv_tv_zs_kcmj_b,
0 AS ds_sv_tv_zs_kc_b,
0 AS ds_sv_tv_celk_kc_b,
0 AS ds_sv_mj_b,
0 AS ds_sv_kcmj_b,
0 AS ds_sv_kc_b,
0 AS ds_sv_celk_kc_b
from
[05730619]...pud predpis,
[05730619]...sCIN cinnost,
[05730619]...AD firma
where
datepart(year,predpis.datum)=(select datepart(year,dateadd(year,-1,max(ud.datum))) from [05730619]...pud ud where 1=1)
/*and convert(varchar,predpis.datum,112) <> convert(varchar,convert(varchar,datepart(year,predpis.datum))+'-12-31',112)*/
and predpis.ud>='324001' and predpis.ud<='324099'
and predpis.RefCIN=cinnost.id
and cinnost.ids<>''
and predpis.RefSTR=firma.RefSTR
and coalesce(firma.RefSTR,'')<>''
group by
firma.firma

UNION
select 
max(firma.firma+', '+firma.ulice+', '+firma.psc+' '+firma.obec),
0,
sum(case when cinnost.ids='Fond oprav' then vyuct.kc else 0 end),
0,
sum(case when cinnost.ids='Čin. SV' then vyuct.kc else 0 end),
0,
sum(case when cinnost.ids='Pojištění' then vyuct.kc else 0 end),
0,
sum(case when cinnost.ids='Daň z nem.' then vyuct.kc else 0 end),
0,
sum(case when cinnost.ids='Plat PP' then vyuct.kc else 0 end),
0,
sum(case when cinnost.ids='Zprac. úč.' then vyuct.kc else 0 end),
0,
sum(case when cinnost.ids='Teplo' then vyuct.kc else 0 end),
0,
sum(case when cinnost.ids='Ohřev vody' then vyuct.kc else 0 end),
0,
sum(case when cinnost.ids='Vodné' then vyuct.kc else 0 end),
0,
sum(case when cinnost.ids='El. spol.' then vyuct.kc else 0 end),
0,
sum(case when cinnost.ids='SV pro ohř' then vyuct.kc else 0 end),
0,
sum(case when cinnost.ids='Výtah' then vyuct.kc else 0 end),
0,
sum(case when cinnost.ids='Anuita' then vyuct.kc else 0 end),
0,
sum(case when cinnost.ids='Ost. nákl.' then vyuct.kc else 0 end),
0,
sum(vyuct.kc),
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
'',
'',
'',
'',
0,
0,
0,
0,
0,
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0
from
[05730619]...pud vyuct,
[05730619]...sCIN cinnost,
[05730619]...AD firma
where
datepart(year,vyuct.datum)=(select datepart(year,dateadd(year,-1,max(ud.datum))) from [05730619]...pud ud where 1=1)
/*and convert(varchar,vyuct.datum,112) <> convert(varchar,convert(varchar,datepart(year,vyuct.datum))+'-12-31',112)*/
and vyuct.umd>='324001' and vyuct.umd<='324099'
and vyuct.RefCIN=cinnost.id
and cinnost.ids<>''
and vyuct.RefSTR=firma.RefSTR
and coalesce(firma.RefSTR,'')<>''
group by
firma.firma

UNION
select 
max(firma.firma+', '+firma.ulice+', '+firma.psc+' '+firma.obec),
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,/*kon_2*/
(select SUM(dsa.vytap_plocha) from SQL05730619.dbo.user_vyuct_druha_strana dsa where dsa.rok=ds.rok and ds.byt <> ''),
(select SUM(dsa.podlah_plocha) from SQL05730619.dbo.user_vyuct_druha_strana dsa where dsa.rok=ds.rok and ds.byt <> ''),
(select SUM(dsa.uzitna_plocha) from SQL05730619.dbo.user_vyuct_druha_strana dsa where dsa.rok=ds.rok and ds.byt <> ''),
(select SUM(dsa.spotr_sv) from SQL05730619.dbo.user_vyuct_druha_strana dsa where dsa.rok=ds.rok and ds.byt <> ''),
(select SUM(dsa.spotr_sv_tv) from SQL05730619.dbo.user_vyuct_druha_strana dsa where dsa.rok=ds.rok and ds.byt <> ''),
sum(ds.oh_vody_gj),
(select SUM(dsa.teplo_dilky) from SQL05730619.dbo.user_vyuct_druha_strana dsa where dsa.rok=ds.rok and ds.byt <> ''),
sum(ds.teplo_gj),
sum(ds.oh_vody_gj),
SUM(ds.spotr_sv_tv_m3),
SUM(ds.spotr_sv_m3),
(select SUM(dsa.teplo) from SQL05730619.dbo.user_vyuct_druha_strana dsa where dsa.rok=ds.rok and ds.byt <> ''),
(select SUM(dsa.oh_vody_celk) from SQL05730619.dbo.user_vyuct_druha_strana dsa where dsa.rok=ds.rok and ds.byt <> ''),
(select SUM(dsa.cena_sv_oh_vody) from SQL05730619.dbo.user_vyuct_druha_strana dsa where dsa.rok=ds.rok and ds.byt <> ''),
(select SUM(dsa.cena_sv) from SQL05730619.dbo.user_vyuct_druha_strana dsa where dsa.rok=ds.rok and ds.byt <> ''),
max(ds.rok),
firma.firma,
ds.vs,
max(firma.ulice+', '+firma.psc+' '+firma.obec),
SUM(ds.spotr_sv),
SUM(ds.spotr_sv_tv),
SUM(ds.vytap_plocha),
SUM(ds.podlah_plocha),
SUM(ds.uzitna_plocha),
isnull(max(ds.vodomer_1),''),
ISNULL(convert(varchar,max(ds.vodomer_1_dat_poc),104),''),
ISNULL(convert(varchar,max(vodomer_1_stav_ps)),''),
isnull(convert(varchar,max(vodomer_1_dat_kon),104),''),
isnull(convert(varchar,max(vodomer_1_stav_ks)),''),
ISNULL(convert(varchar,max(vodomer_1_spotr)),''),
isnull(max(ds.vodomer_2),''),
ISNULL(convert(varchar,max(ds.vodomer_2_dat_poc),104),''),
ISNULL(convert(varchar,max(vodomer_2_stav_ps)),''),
isnull(convert(varchar,max(vodomer_2_dat_kon),104),''),
isnull(convert(varchar,max(vodomer_2_stav_ks)),''),
ISNULL(convert(varchar,max(vodomer_2_spotr)),''),
isnull(max(ds.vodomer_3),''),
ISNULL(convert(varchar,max(ds.vodomer_3_dat_poc),104),''),
ISNULL(convert(varchar,max(vodomer_3_stav_ps)),''),
isnull(convert(varchar,max(vodomer_3_dat_kon),104),''),
isnull(convert(varchar,max(vodomer_3_stav_ks)),''),
ISNULL(convert(varchar,max(vodomer_3_spotr)),''),
isnull(max(ds.vodomer_4),''),
ISNULL(convert(varchar,max(ds.vodomer_4_dat_poc),104),''),
ISNULL(convert(varchar,max(vodomer_4_stav_ps)),''),
isnull(convert(varchar,max(vodomer_4_dat_kon),104),''),
isnull(convert(varchar,max(vodomer_4_stav_ks)),''),
ISNULL(convert(varchar,max(vodomer_4_spotr)),''),
SUM(ds.teplo_dilky),
SUM(ds.teplo),
0,
0,
SUM(ds.teplo + 0),
SUM((case when ds.vodomer_1 like '%TV%' then ds.vodomer_1_spotr else 0 end)+(case when ds.vodomer_2 like '%TV%' then ds.vodomer_2_spotr else 0 end)+(case when ds.vodomer_3 like '%TV%' then ds.vodomer_3_spotr else 0 end)+(case when ds.vodomer_4 like '%TV%' then ds.vodomer_4_spotr else 0 end)),
case when SUM((case when ds.vodomer_1 like '%TV%' then ds.vodomer_1_spotr else 0 end)+(case when ds.vodomer_2 like '%TV%' then ds.vodomer_2_spotr else 0 end)+(case when ds.vodomer_3 like '%TV%' then ds.vodomer_3_spotr else 0 end)+(case when ds.vodomer_4 like '%TV%' then ds.vodomer_4_spotr else 0 end)) = 0 then 0 else SUM(ds.sp_sl_oh_vody)/SUM((case when ds.vodomer_1 like '%TV%' then ds.vodomer_1_spotr else 0 end)+(case when ds.vodomer_2 like '%TV%' then ds.vodomer_2_spotr else 0 end)+(case when ds.vodomer_3 like '%TV%' then ds.vodomer_3_spotr else 0 end)+(case when ds.vodomer_4 like '%TV%' then ds.vodomer_4_spotr else 0 end)) end,
SUM(ds.sp_sl_oh_vody),
SUM(ds.vytap_plocha),
case when SUM(ds.vytap_plocha) = 0 then 0 else SUM(ds.zakl_sl_en_oh_vody)/SUM(ds.vytap_plocha) end,
SUM(ds.zakl_sl_en_oh_vody),
SUM(ds.sp_sl_oh_vody+ds.zakl_sl_en_oh_vody),
SUM((case when ds.vodomer_1 like '%TV%' then ds.vodomer_1_spotr else 0 end)+(case when ds.vodomer_2 like '%TV%' then ds.vodomer_2_spotr else 0 end)+(case when ds.vodomer_3 like '%TV%' then ds.vodomer_3_spotr else 0 end)+(case when ds.vodomer_4 like '%TV%' then ds.vodomer_4_spotr else 0 end)),
case when SUM((case when ds.vodomer_1 like '%TV%' then ds.vodomer_1_spotr else 0 end)+(case when ds.vodomer_2 like '%TV%' then ds.vodomer_2_spotr else 0 end)+(case when ds.vodomer_3 like '%TV%' then ds.vodomer_3_spotr else 0 end)+(case when ds.vodomer_4 like '%TV%' then ds.vodomer_4_spotr else 0 end)) = 0 then 0 else SUM(ds.cena_sv_oh_vody)/SUM((case when ds.vodomer_1 like '%TV%' then ds.vodomer_1_spotr else 0 end)+(case when ds.vodomer_2 like '%TV%' then ds.vodomer_2_spotr else 0 end)+(case when ds.vodomer_3 like '%TV%' then ds.vodomer_3_spotr else 0 end)+(case when ds.vodomer_4 like '%TV%' then ds.vodomer_4_spotr else 0 end )) end,
SUM(ds.cena_sv_oh_vody),
0,
0,
0,
SUM(ds.cena_sv_oh_vody + 0),
SUM((case when ds.vodomer_1 like '%SV%' then ds.vodomer_1_spotr else 0 end)+(case when ds.vodomer_2 like '%SV%' then ds.vodomer_2_spotr else 0 end)+(case when ds.vodomer_3 like '%SV%' then ds.vodomer_3_spotr else 0 end)+(case when ds.vodomer_4 like '%SV%' then ds.vodomer_4_spotr else 0 end)),
case when SUM((case when ds.vodomer_1 like '%SV%' then ds.vodomer_1_spotr else 0 end)+(case when ds.vodomer_2 like '%SV%' then ds.vodomer_2_spotr else 0 end)+(case when ds.vodomer_3 like '%SV%' then ds.vodomer_3_spotr else 0 end)+(case when ds.vodomer_4 like '%SV%' then ds.vodomer_4_spotr else 0 end)) = 0 then 0 else SUM(ds.cena_sv)/SUM((case when ds.vodomer_1 like '%SV%' then ds.vodomer_1_spotr else 0 end)+(case when ds.vodomer_2 like '%SV%' then ds.vodomer_2_spotr else 0 end)+(case when ds.vodomer_3 like '%SV%' then ds.vodomer_3_spotr else 0 end)+(case when ds.vodomer_4 like '%SV%' then ds.vodomer_4_spotr else 0 end)) end,
SUM(ds.cena_sv),
SUM(ds.cena_sv)
from
SQL05730619.dbo.user_vyuct_druha_strana ds,
[05730619]...sSTR strcis,
[05730619]...AD firma
where
ds.rok = datepart(year,dateadd(year,-1,GetDate()))
and ds.byt = strcis.ids
and strcis.id = firma.RefSTR
and coalesce(firma.RefSTR,'')<>''
group by
ds.rok,
ds.byt,
ds.vs,
firma.firma


) xar
where 1=1

group by
xar.uzivatel

order by 
s_ds_vs