ค้นหา server ที่ว่าง.sql 3.09 KB
Newer Older
TongZuu committed
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60
/*  server ö assing queue ŧ§ */
/* process_normal */
with csc_server as (
	select 'yum1' as 'server_name','4' as 'thread_process','10' as 'thread_workflow','10' as 'thread_report','active' as server_status,'2013-11-24 15:08:00' as 'last assign'
	union all
	select 'yum2' as 'server_name','4' as 'thread_process','10' as 'thread_workflow','10' as 'thread_report','active' as server_status,'2013-11-24 15:09:00' as 'last assign'
	union all
	select 'yum3' as 'server_name','6' as 'thread_process','10' as 'thread_workflow','10' as 'thread_report','active' as server_status,'2013-11-24 15:08:00' as 'last assign'
)
select top 1 s.server_name,s.server_status,s.thread_process,COUNT(q.server_name) as 'process_runing',s.[last assign]
from csc_server s LEFT OUTER JOIN CSC_QUQUE q 
on s.server_name = q.SERVER_NAME and (q.PROCESS_STATUS = 'R' or q.PROCESS_STATUS = 'T') and q.PROCESS_GROUP = 'P' and PROCESS_TYPE != 'W'
where s.server_status = 'active'
group by s.server_name,s.thread_process,s.server_status,s.[last assign],s.thread_workflow,s.thread_report
having COUNT(q.server_name) < s.thread_process
order by process_runing,[last assign]

--checking sql upper
select * from csc_quque where SERVER_NAME = 'yum1'


/*  server Шӹǹ еǶ */
select s.server_name,
s.thread_process,SUM(case when PROCESS_TYPE <> 'W' and PROCESS_GROUP = 'P' then 1 else 0 end) as process_normal,
s.thread_workflow,SUM(case when PROCESS_TYPE = 'W' and PROCESS_GROUP = 'P' then 1 else 0 end) as process_workflow,
s.thread_report,SUM(case when charindex(PROCESS_GROUP, 'RIEM') > 0 then 1 else 0 end) as process_report,
s.LAST_ACTION
from csc_server s left outer join CSC_QUQUE q
on s.server_status = 'active' and s.server_name = q.SERVER_NAME and (q.PROCESS_STATUS = 'R' or q.PROCESS_STATUS = 'T')
group by s.server_name,s.thread_process,s.thread_workflow,s.thread_report,s.LAST_ACTION








select * from csc_quque where QUQUEID between '1000' and '1003'
--update csc_quque set server_name = 'yum1',process_status = 'T',process_type='N',process_group = 'E' where QUQUEID between '1020' and '1021'



/*  server ö assing queue ŧ§ */
/*  condition */
select s.server_name,
s.thread_process,SUM(case when PROCESS_TYPE <> 'W' and PROCESS_GROUP = 'P' then 1 else 0 end) as process_normal,
s.thread_workflow,SUM(case when PROCESS_TYPE = 'W' and PROCESS_GROUP = 'P' then 1 else 0 end) as process_workflow,
s.thread_report,SUM(case when charindex(PROCESS_GROUP, 'RIEM') > 0 then 1 else 0 end) as process_report,
s.LAST_ACTION,s.LAST_ASSIGN
from csc_server s left outer join CSC_QUQUE q
on s.server_status = 'active' and s.server_name = q.SERVER_NAME and (q.PROCESS_STATUS = 'R' or q.PROCESS_STATUS = 'T')
group by s.server_name,s.thread_process,s.thread_workflow,s.thread_report,s.LAST_ACTION,s.LAST_ASSIGN

/* for process_normal */
--and process_normal < thread_process order by process_normal,LAST_ASSIGN
/* for process_workflow */
and process_workflow < thread_workflow order by process_workflow,LAST_ASSIGN
/* for process_report */
--and process_report < thread_workflow order by process_report,LAST_ASSIGN