select servername, batch_id, count(*) cnt from f_pgm_info
where biz_dt between cast(convert(varchar,getdate(),111)+' 00:00:00:000' as datetime)
and cast(convert(varchar,getdate(),111)+' 23:59:59:998' as datetime)
and batch_id like '%.BATCH'
group by servername, batch_id
order by servername, batch_id
;
ebroker.2035 FO.BATCH 5
ebroker.2035 JU.BATCH 2
ebroker.2035 KP200.BATCH 1
ebroker.2035 LP.BATCH 2
ebroker.2035 MC.BATCH 2
ebroker_save.2076 FO.BATCH 5
ebroker_save.2076 JU.BATCH 2
ebroker_save.2076 KP200.BATCH 1
ebroker_save.2076 LP.BATCH 2
ebroker_save.2076 MC.BATCH 2
etr.2034 FO.BATCH 5
etr.2034 JU.BATCH 2
etr.2034 KP200.BATCH 1
etr.2034 LP.BATCH 2
etr.2034 MC.BATCH 2
etr_ord.2019 FO.BATCH 5
etr_ord.2019 JU.BATCH 2
etr_ord.2019 KP200.BATCH 1
etr_ord.2019 LP.BATCH 2
etr_ord.2019 MC.BATCH 2
etr_sch.2018 FO.BATCH 5
etr_sch.2018 JU.BATCH 2
etr_sch.2018 KP200.BATCH 1
etr_sch.2018 LP.BATCH 2
etr_sch.2018 MC.BATCH 2
select servername
, sum(case when batch_id = 'JU.BATCH' then cnt end) ju_batch
, sum(case when batch_id = 'FO.BATCH' then cnt end) fo_batch
, sum(case when batch_id = 'KP200.BATCH' then cnt end) kp200_batch
, sum(case when batch_id = 'LP.BATCH' then cnt end) lp_batch
, sum(case when batch_id = 'MC.BATCH' then cnt end) mc_batch
from
(
select servername, batch_id, count(*) cnt
from f_pgm_info
where biz_dt between cast(convert(varchar,getdate(),111)+' 00:00:00:000' as datetime)
and cast(convert(varchar,getdate(),111)+' 23:59:59:998' as datetime)
and batch_id like '%.BATCH'
group by servername, batch_id
) a
group by servername
order by servername
;
ebroker.2035 2 5 1 2 2
ebroker_save.2076 2 5 1 2 2
etr.2034 2 5 1 2 2
etr_ord.2019 2 5 1 2 2
etr_sch.2018 2 5 1 2 2