많은 가비지 값과 몇 가지 중요한 값이있는 String_Value라는 열이 있습니다.
경고, 위험과 같은 중요한 값만 필터링하면. 필터와 일치하는 string_value가있는 job_ticket_ids 만 가져오고 고유 한 작업 ID도 제공합니다.
하지만 내가 필요한 것은 string_value 열이 필터없이 중요한 값만 포함해야한다는 것입니다. 중요한 값이없는 작업 ID는 null로 반환 될 수 있기 때문입니다. 또한 작업 ID는 구별되어야합니다.
참조를 위해 예상 출력을 첨부했습니다. 당신이 어떤 아이디어가 있으면 이것을 통해 나를 안내하십시오.
select distinct
Job_ticket_id,
CASE WHEN (case when [Escalated to Resolved time] is null then (ISNULL([Escalated to Resolved time],0) + ISNULL([Escalated to Closed time],0)- ISNULL([hold time],0)) else (ISNULL([Escalated to Closed time],0) - ISNULL([hold time],0)) end) <0THEN 0 ELSE (case when [Escalated to Resolved time] is null then (ISNULL([Escalated to Resolved time],0) + ISNULL([Escalated to Closed time],0)- ISNULL([hold time],0)) else (ISNULL([Escalated to Closed time],0) - ISNULL([hold time],0)) end) END AS JobTime,
CASE WHEN ISNULL(cat.[Time to Accept SLA],0) <0 THEN 0 ELSE ISNULL(cat.[Time to Accept SLA],0) END AS [Time to Accept SLA],
cat.Report_Date,
cat.[Problem_Type_Name(Parent)],
cat.[Problem_Type_Name(Child)],
cat.[Assigned Tech],
cat.PRIORITY_TYPE_NAME, cat.Close_Date,
cat.NAME, cat.[Ticket Status],
isnull(max(cat.[Escalated to Closed time]), 0) as 'Escalated to Closed time',
isnull(max(cat.[Escalated to Resolved time]), 0) as 'Escalated to Resolved time',
isnull(max(cat.[hold time]), 0) as 'Hold Time',
cat.String_value as 'String_value',
cat.PROBLEM_TYPE_ID
from
TEMP_TICKET_STATE_Category cat
group by
JOB_TICKET_ID, String_value, [Problem_Type_Name(Parent)],
JOB_TICKET_ID, [Problem_Type_Name(Child)], PROBLEM_TYPE_ID,
REPORT_DATE, CLOSE_DATE, PRIORITY_TYPE_NAME, LAST_UPDATED,
TECH_GROUP_ID, NAME, ENTRY_DATE,
[Escalated to Closed time], [Escalated to Resolved time],
[hold time], [Time to Accept SLA], [Assigned Tech], [Ticket Status]
이 코드를 시도해 볼 수 있습니다.
select distinct Job_ticket_id,
CASE WHEN (case when [Escalated to Resolved time] is null then (ISNULL([Escalated to Resolved time],0) + ISNULL([Escalated to Closed time],0)- ISNULL([hold time],0)) else (ISNULL([Escalated to Closed time],0) - ISNULL([hold time],0)) end) <0THEN 0 ELSE (case when [Escalated to Resolved time] is null then (ISNULL([Escalated to Resolved time],0) + ISNULL([Escalated to Closed time],0)- ISNULL([hold time],0)) else (ISNULL([Escalated to Closed time],0) - ISNULL([hold time],0)) end) END AS JobTime
,CASE WHEN ISNULL(cat.[Time to Accept SLA],0) <0 THEN 0 ELSE ISNULL(cat.[Time to Accept SLA],0) END AS [Time to Accept SLA]
, cat.Report_Date
, cat.[Problem_Type_Name(Parent)]
, cat.[Problem_Type_Name(Child)]
, cat.[Assigned Tech]
, cat.PRIORITY_TYPE_NAME
,cat.Close_Date,cat.NAME
,cat.[Ticket Status]
,isnull(max(cat.[Escalated to Closed time]),0) as 'Escalated to Closed time'
,isnull(max(cat.[Escalated to Resolved time]),0) as 'Escalated to Resolved time'
,isnull(max(cat.[hold time]),0) as 'Hold Time'
,case when cat.String_value in ('warning' ,'critical') then cat.String_value else null end as 'String_value'
,cat.PROBLEM_TYPE_ID
from TEMP_TICKET_STATE_Category cat
Group By JOB_TICKET_ID,String_value,[Problem_Type_Name(Parent)],JOB_TICKET_ID,[Problem_Type_Name(Child)],PROBLEM_TYPE_ID, REPORT_DATE,CLOSE_DATE,PRIORITY_TYPE_NAME,LAST_UPDATED,TECH_GROUP_ID,NAME,ENTRY_DATE,[Escalated to Closed time],[Escalated to Resolved time],[hold time],[Time to Accept SLA],[Assigned Tech],[Ticket Status]
이 기사는 인터넷에서 수집됩니다. 재 인쇄 할 때 출처를 알려주십시오.
침해가 발생한 경우 연락 주시기 바랍니다[email protected] 삭제
몇 마디 만하겠습니다