select cycle_year , cycle_index , tenant_name, t.batch_id, t.batchType, sum(total_cost) total_cost
		from (
			select b.id as batch_id, brt.mdm_name tenant_name, b.cycle_year, b.cycle_index, concat(cycle_year, '-0', cycle_index, '-', '01') date, ii.resource_name, ii.total_cost,
			concat(cycle_year, '-', cycle_index, ': ', visibility_status) as batchType,
			rank() over (partition by cycle_year, cycle_index order by b.created_at  desc) as rnk
			from batch b
			inner join batch_invoice bi on bi.batch_id  = b.id
			inner join batch_invoice_item ii on ii.batch_invoice_id = bi.id 
			left join batch_raw_tenant brt on brt.batch_id = ii.batch_id and brt.mdm_name = ii.tenant_name 
			left join batch_raw_organization bro on bro.batch_id = ii.batch_id and bro.mdm_id = brt.mdm_org_id 
			--inner join batch_raw_carol_app brca on brca.batch_id = ii.batch_id and brca.mdm_tenant_id = brt.mdm_id 
			where 1=1 
			--and brca.mdm_name in ('','clockinapp')
			and b.id in (select id from (select id, cycle_year , cycle_index , created_at , rank() over (partition by cycle_year, cycle_index order by created_at  desc) as rnk from batch b ) t where rnk = 1)
			--and bro.mdm_name in ('totvstechfin','totvstechfinstaging','totvstechfindev')
			and bi.billing_account_id  in (select id from billing_account ba where name = 'TOTVSSupply')
		) t
		group by cycle_year , cycle_index , tenant_name, t.batch_id, t.batchType
		order by 6 desc

select cycle_year, cycle_index, concat(cycle_year, '-0', cycle_index, '-', '01') date, resource_name, sum(total_cost) total_cost, batchType
	from (
		--select cycle_year , cycle_index , tenant_name, t.batch_id, t.batchType, sum(total_cost) total_cost
		--from (
			select b.id as batch_id, brt.mdm_name tenant_name, b.cycle_year, b.cycle_index, concat(cycle_year, '-0', cycle_index, '-', '01') date, ii.resource_name, ii.total_cost,
			concat(cycle_year, '-', cycle_index, ': ', visibility_status) as batchType,
			rank() over (partition by cycle_year, cycle_index order by b.created_at  desc) as rnk
			from batch b
			inner join batch_invoice bi on bi.batch_id  = b.id
			inner join batch_invoice_item ii on ii.batch_invoice_id = bi.id 
			left join batch_raw_tenant brt on brt.batch_id = ii.batch_id and brt.mdm_name = ii.tenant_name 
			left join batch_raw_organization bro on bro.batch_id = ii.batch_id and bro.mdm_id = brt.mdm_org_id 
			--inner join batch_raw_carol_app brca on brca.batch_id = ii.batch_id and brca.mdm_tenant_id = brt.mdm_id 
			where 1=1 
			--and brca.mdm_name in ('','clockinapp')
			and b.id in (select id from (select id, cycle_year , cycle_index , created_at , rank() over (partition by cycle_year, cycle_index order by created_at  desc) as rnk from batch b ) t where rnk = 1)
			--and bro.mdm_name in ('totvstechfin','totvstechfinstaging','totvstechfindev')
			and bi.billing_account_id  in (select id from billing_account ba where name = 'TOTVSSupply')
		--) t
		--group by cycle_year , cycle_index , tenant_name, t.batch_id, t.batchType
		--order by 6 desc
	) t
	where t.rnk = 1
	group by cycle_year , cycle_index , resource_name, t.batch_id, t.batchType
	order by 1,2,3,4