CA Service Desk uygulaması için örnek sql server sorguları

Ca Service Desk uygulaması için örnek sql sorguları:

-- cagri master tablosu (son halini gosterir)
select * from call_req where ref_num = 'R1958636'

-- rootcause bilgileri
select * from rootcause
 
-- cagri statu lookup
select * from cr_stat
 
-- user tablosu
SELECT * FROM CA_CONTACT (NOLOCK) where DEPARTMENT = 400002
and contact_uuid = 0x4B9F048CB4CB8A4AAE88F1A5AC2BCA5B -- call_req tablosundaki customer alani
 
-- cagrinin steplerini gosterir
select * from soft_actlog where call_req_id = 'cr:2346488'


SELECT
    COUNT(*)
FROM
    CALL_REQ
-- 1,556,229

-- calisanlarin actigi cagrilarin bilgileri
SELECT
    CA.REF_NUM,
    CA.SUMMARY,
    CA.DESCRIPTION,
    CA.ROOTCAUSE,
    RC.SYM AS ROOTCAUSESYM,
    CA.STATUS,
    ST.SYM AS STATUSSYM,
    ST.DESCRIPTION AS STATUSDESCRIPTION,
    CA.OPEN_DATE,
    DATEADD(ss,CA.OPEN_DATE+3600,'1970-01-01 02:00:00') AS CALLOPENDATE,
    CO.FIRST_NAME,
    CO.LAST_NAME,
    CO.EMAIL_ADDRESS,
    CO.ALTERNATE_IDENTIFIER AS EMPLOYEENUMBER,
    CO.USERID AS NTUSER
    --CA.*,
    --CO.*    
FROM
    CALL_REQ CA,
    CA_CONTACT CO,
    CR_STAT ST,
    ROOTCAUSE RC
WHERE
    CA.CUSTOMER = CO.CONTACT_UUID
    AND CA.STATUS = ST.CODE
    AND (CA.ROOTCAUSE = RC.ID)
    AND CO.DEPARTMENT = 400002
    AND CO.ALTERNATE_IDENTIFIER = '90016439'
    AND CA.STATUS <> 'IPTAL'
ORDER BY
    CA.OPEN_DATE DESC


SELECT
    CA.REF_NUM,
    CA.SUMMARY,
    CA.DESCRIPTION,
    CA.ROOTCAUSE,
    RC.SYM AS ROOTCAUSESYM,
    CA.STATUS,
    ST.SYM AS STATUSSYM,
    ST.DESCRIPTION AS STATUSDESCRIPTION,
    CA.OPEN_DATE,
    DATEADD(ss,CA.OPEN_DATE+3600,'1970-01-01 02:00:00') AS CALLOPENDATE,
    CA.CLOSE_DATE,
    DATEADD(ss,CA.CLOSE_DATE+3600,'1970-01-01 02:00:00') AS CALLCLOSEDATE,
    CO.FIRST_NAME,
    CO.LAST_NAME,
    CO.EMAIL_ADDRESS,
    CO.ALTERNATE_IDENTIFIER AS EMPLOYEENUMBER,
    CO.USERID AS NTUSER
    --CA.*,
    --CO.*    
FROM
    CALL_REQ CA LEFT JOIN CA_CONTACT CO ON CA.CUSTOMER = CO.CONTACT_UUID
                LEFT JOIN CR_STAT ST ON CA.STATUS = ST.CODE
                LEFT JOIN ROOTCAUSE RC ON CA.ROOTCAUSE = RC.ID
WHERE
    CO.DEPARTMENT = 400002
    AND CO.ALTERNATE_IDENTIFIER = '90013829'
    AND CA.STATUS <> 'IPTAL'
ORDER BY
    CA.OPEN_DATE DESC



SELECT
    CA.REF_NUM,
    CA.SUMMARY,
    CA.DESCRIPTION,
    CA.ROOTCAUSE,
    RC.SYM AS ROOTCAUSESYM,
    CA.STATUS,
    ST.SYM AS STATUSSYM,
    ST.DESCRIPTION AS STATUSDESCRIPTION,
    CA.OPEN_DATE,
    DATEADD(ss,CA.OPEN_DATE+3600,'1970-01-01 02:00:00') AS CALLOPENDATE,
    CA.CLOSE_DATE,
    DATEADD(ss,CA.CLOSE_DATE+3600,'1970-01-01 02:00:00') AS CALLCLOSEDATE,
    CO.FIRST_NAME,
    CO.LAST_NAME,
    CO.EMAIL_ADDRESS,
    CO.ALTERNATE_IDENTIFIER AS EMPLOYEENUMBER,
    CO.USERID AS NTUSER
    --CA.*,
    --CO.*    
FROM
    CALL_REQ CA LEFT JOIN CA_CONTACT CO ON CA.CUSTOMER = CO.CONTACT_UUID
                LEFT JOIN CR_STAT ST ON CA.STATUS = ST.CODE
                LEFT JOIN ROOTCAUSE RC ON CA.ROOTCAUSE = RC.ID
WHERE
    CO.DEPARTMENT = 400002
    AND CO.ALTERNATE_IDENTIFIER = ~USERCODE
    AND CA.STATUS <> 'IPTAL'
    AND DATEADD(ss,CA.OPEN_DATE+3600,'1970-01-01 02:00:00') BETWEEN  ~STARTDATE AND ~ENDDATE
ORDER BY
    CA.OPEN_DATE DESC