05-04-2016 02:47 AM
Hello,
I,m trying to use this query :
SELECT DISTINCT
A.clientName as 'Client Name',
A.policyName as 'Policy Name',
A.scheduleName as 'Schedule name',
LIST( DISTINCT A.statuscode) as 'Status Code',
( select DATEDIFF(DAY,UtcBigIntToNomTime(MAX(CKP.endTime)),GETDATE()) as 'Days since Last successful'
from domain_JobArchive CKP
where CKP.masterServerId = A.masterServerId AND
CKP.id = CKP.parentJobId AND
CKP.clientId = A.clientId AND
CKP.policyId = A.policyId AND
CKP.policyName = A.policyName AND
CKP.policyType = A.policyType AND
( CKP.statusCode = 0 OR CKP.statusCode = 1 ) ),
( select UtcBigIntToNomTime(MAX(BKP.endTime)) as 'Last successful'
from domain_JobArchive BKP
where BKP.masterServerId = A.masterServerId AND
BKP.Id = BKP.parentJobId AND
BKP.clientId = A.clientId AND
BKP.policyId = A.policyId AND
BKP.policyName = A.policyName AND
BKP.policyType = A.policyType AND
( BKP.statusCode = 0 OR BKP.statusCode = 1 ) )
FROM "domain_JobArchive" A , "domain_masterserver" B
WHERE
A.masterServerId = B.id AND A.id = A.parentjobid AND
DATEDIFF(hour,UtcBigIntToNomTime(A.endTime), GETDATE()) <= 24
AND
( select DATEDIFF(DAY,UtcBigIntToNomTime(MAX(CKP.endTime)),GETDATE()) as 'Days since Last successful'
from domain_JobArchive CKP
where CKP.masterServerId = A.masterServerId AND
CKP.id = A.parentJobId AND
CKP.clientId = A.clientId AND
CKP.policyId = A.policyId AND
CKP.policyName = A.policyName AND
CKP.policyType = A.policyType AND
CKP.scheduleType = A.scheduleType AND
( CKP.statusCode = 0 OR CKP.statusCode = 1 ) ) > 0 AND
A.scheduleName != 'user_backup' AND
A.statusCode not in (0,1,190,199,230,239,240,800,1000) AND
A.policyName NOT LIKE 'SLP_%' AND A.type IN (0,22,28) AND
A.scheduletype not in (0,-1,2)
Group by A.clientName,A.masterServerId,A.policyName,B.friendlyName,policyType,scheduleType,clientid,policyid,mediaServerName,A.scheduleName
ORDER BY A.clientName,A.scheduleName
but I need to find the code for viewing only Schedule name monthly and Anual, what can I write at this sentences
A.scheduletype not in (0,-1,2)
instead of (0,-1,2) ?
Thanks a lot
Regards
05-05-2016 02:32 AM
Hi.
Replace this line
A.scheduletype not in (0,-1,2)
with this line
A.schedulename = 'monthly' or A.schedulename = 'anual'
06-01-2016 04:09 AM
Hello,
Thank you for your help. It does not work.
regards.