drop table #usl
create table #usl (period varchar(35), adres varchar(526)
, uslname varchar(250), resurs varchar(250), tarifresurs varchar(16), vidpu varchar(1), tarif1 float, edizm varchar(4))

insert into #usl
SELECT
--CASE WHEN a.code_house IN (SELECT code_house FROM gis_209fz..dogovor d1) AND d.data_po > GETDATE() THEN 'ДУ' ELSE 'ДРСО' END AS tipdog,
--dg.code_dog_gis,
CAST(otn.mes AS VARCHAR) + '.2021' AS period, r.name + ' '+ rs.name + ', ' + cs.name + ' ' + c.name + ', ' + ss.sname + '. ' + s.name + ' ' +a.number + ', ' + a.flat AS adres,
--htf.code_fias, CASE WHEN hg.tip_house = 'ЖДБ' THEN a.flat ELSE '' END AS block, CASE WHEN hg.tip_house = 'МКД' THEN a.flat ELSE '' END AS pom,
--CASE WHEN hg.tip_house = 'ЖД' THEN a.flat ELSE '' END AS komn,
CASE WHEN usl.kod = 3 THEN 'Отведение сточных вод' ELSE usl.name END uslname,
CASE WHEN d.code_house IS NULL AND usl.kod IN (1,19) AND ISNULL(aa.xvoda,h.xvoda)=6 THEN 'Техническая вода'
WHEN d.code_house IS NULL and usl.kod IN (1,19) AND ISNULL(aa.xvoda,h.xvoda) IN (1,2,3,4,5) THEN 'Питьевая вода'
WHEN d.code_house IS NULL AND usl.kod IN (2,18) AND ISNULL(aa.gvoda,h.gvoda) in (3,4) THEN 'Горячая вода'
WHEN d.code_house IS NULL AND usl.kod IN (2,18) AND ISNULL(aa.gvoda,h.gvoda) in (1,2) THEN 'Теплоноситель'
WHEN d.code_house IS NULL AND usl.kod = 6 THEN 'Тепловая энергия'
WHEN d.code_house IS NULL AND usl.kod = 3 THEN 'Сточные воды' ELSE NULL END AS resurs,
CASE WHEN d.code_house IS NOT NULL AND usl.kod IN (1,19) THEN 'Холодная вода'
WHEN d.code_house IS NOT NULL AND usl.kod IN (2,18) THEN 'Горячая вода'
WHEN d.code_house IS NOT NULL AND usl.kod = 6 THEN 'Тепловая энергия'
WHEN d.code_house IS NOT NULL AND usl.kod = 3 THEN 'Сточные воды' ELSE '' END AS tarifresurs,
'' AS vidpu, ROUND(SUM(otn.volume),6) as tarif1,
-- '' AS tarif2, '' AS tarif3,
 CASE WHEN usl.code_ed_izm = 113 THEN 'м3' ELSE
CASE WHEN usl.code_ed_izm = 233 THEN 'Гкал' ELSE NULL END END AS edizm

FROM (SELECT [on].kod_Adres, [on].mes, [on].kod_uslugi, [on].kod_postav, [on].volume FROM kv2021..otchet_nach [on]
UNION
SELECT os.kod_adres, os.mesy, os.kod_uslugi, os.kod_postav, os.volume FROM kv2021..otchet_sn os) otn
LEFT JOIN spr..vAdres a ON a.code = otn.kod_Adres
LEFT JOIN spr..Houses_to_fias htf ON htf.code_house = a.code_house
INNER JOIN gis_209fz..houses_gis hg ON htf.code_fias = hg.code_gis OR htf.code_fias = hg.code_fias
LEFT JOIN spr..uslugi u ON u.kod= otn.kod_uslugi
LEFT JOIN spr..Streets s ON s.code = a.code_street
LEFT JOIN spr..StreetStatus ss ON ss.code = s.code_status
LEFT JOIN spr..Cities c ON c.code = s.code_city
LEFT JOIN spr..CityStatus cs ON cs.code = c.code_status
LEFT JOIN spr..Okrug o ON o.code = c.code_okrug
LEFT JOIN spr..OkrugStatus ost ON ost.code = o.code_status
LEFT JOIN spr.dbo.Regions r ON o.code_region = r.code
LEFT JOIN spr.dbo.RegionStatus rs ON r.code_status = rs.code
LEFT JOIN gis_209fz..dogovor d ON d.code_house = a.code_house AND d.data_po > GETDATE()
LEFT JOIN gis_209fz..dogovor_gis dg ON dg.code_dog = CASE WHEN d.num_dog IS NULL THEN CAST(a.code_house AS VARCHAR) ELSE d.num_dog END
LEFT JOIN gis_209fz..uslugi usl ON usl.kod_kv = otn.kod_uslugi
LEFT JOIN spr..postav p ON p.code = otn.kod_postav
LEFT JOIN kv2021..Adres_a aa ON aa.code = a.code AND aa.mes = otn.mes
LEFT JOIN kv2021..doma_a h ON h.code = a.code_house AND h.mes = otn.mes
where/* u.type = 1 AND u.kod <> 68 AND*/ (p.name LIKE '%Верхнеколымская%' or p.name like '%Кулешова%') AND otn.mes=12 -- AND otn.kod_Adres = 7006015001
/*AND dg.code_dog_gis =  '65a790d0-d43a-4ed7-be5e-ce5ab906f463'*/
GROUP BY otn.kod_Adres,otn.kod_postav, d.data_po, dg.code_dog_gis, otn.mes, r.name, rs.name, cs.name, c.name, ss.sname, s.name,
a.number, a.flat, htf.code_fias, hg.tip_house, usl.code_ed_izm, usl.resurs, a.code_house, usl.name, h.xvoda, usl.kod, h.gvoda, aa.xvoda, aa.gvoda, d.code_house
ORDER BY period, usl.name


select * from #usl

--select * from spr..uslugi