06-21-2016 03:45 AM
Hello,
I am using this code:
-----------------------------------------------------------------------------
SELECT DISTINCT A.clientName as 'Client Name', A.policyName as 'Policy 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.policyName LIKE 'XXX%'
OR A.policyName LIKE 'YYY%'
AND A.type IN (0,22,28)Group by A.clientName,A.masterServerId,A.policyName,B.friendlyName,policyType,scheduleType,clientid,policyid ORDER BY A.policyName , A.clientName
-------------------------------------------------------------------------------------
But I need to include these lines :
AND A.policyName NOT LIKE 'SLP_%'
AND A.clientName LIKE 'xxxxxx'
OR A.clientName LIKE 'yyyyyy'
instead of the following:
AND A.policyName NOT LIKE 'SLP_%'
AND A.policyName LIKE 'XXX'
OR A.policyName LIKE 'YYYY'
because I need to include clients by clientName and not by policyname , what lines had I to change in my code?
Thanks a lot.
06-21-2016 11:33 PM
Is this what you need
example of the line
AND A.clientName LIKE 'moon%' OR A.clientName LIKE ' sun% '
SELECT DISTINCT A.clientName as 'Client Name', A.policyName as 'Policy 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.clientName LIKE 'XXX%' OR A.clientName LIKE 'YYY%'
AND A.type IN (0,22,28)Group by A.clientName,A.masterServerId,A.policyName,B.friendlyName,policyType,scheduleType,clientid,policyid ORDER BY A.policyName , A.clientName
06-23-2016 12:46 AM
Thank you ,M_henriksen
It works but it repeats values and in column " days since last successul" sometimes appears "16975".
Regards.