OpsCenter SQL query problem

Hi

 

I have a SQL query im using to generate a report showing last succesful back up after a failure on all clients, looking at data from the last 7 days.

The report is perfect except I want it to not include entries where the last succesfull back was less than 2 days ago.

This is what im using at the moment

SELECT DISTINCT
B.friendlyName as 'Master Server',
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.scheduleName = A.scheduleName AND
                              CKP.statusCode = 0  ),
            (       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.scheduleName = A.scheduleName AND
                              BKP.statusCode = 0 )
FROM  "domain_JobArchive" A , "domain_masterserver" B
WHERE
A.masterServerId = B.id AND A.id = A.parentjobid AND
          DATEDIFF(day,UtcBigIntToNomTime(A.endTime), GETDATE()) <= 7
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 ) >1 AND
A.scheduleName != 'user_backup' AND
A.statusCode not in (0,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 (-1,2)
Group by A.clientName,A.masterServerId,A.policyName,A.scheduleName,B.friendlyName,policyType,scheduleType,clientid,policyid
ORDER BY B.friendlyName , A.clientName

I have tried changing >1 to >2, I have also tried != (0,1), i have also tried not in (0,1).  The problem is as far as i can tell the section i have changed to bold below is doing nothing. I can even remove this section from the query and it makes no difference to the report. This is the section i believe i can use to filter out 0 and 1 from the last succefull back up field but as it does nothing im not getting very far!

WHERE
A.masterServerId = B.id AND A.id = A.parentjobid AND
          DATEDIFF(day,UtcBigIntToNomTime(A.endTime), GETDATE()) <= 7
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 ) >1 AND
A.scheduleName != 'user_backup' AND
A.statusCode not in (0,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 (-1,2)
Group by A.clientName,A.masterServerId,A.policyName,A.scheduleName,B.friendlyName,policyType,scheduleType,clientid,policyid
ORDER BY B.friendlyName , A.clientName

 

Every thing I have tried has failed to remove entries where the last succesful back up was less than 2 days.

 

Has anyone get any suggestions or fixes for this. I have next to no sql knowledge and have only delved into this to create a very specific report that can run every day and be sent out to our ops team. Any help will be greatly appreciated.

 

Thanks in advance for any help

 

Matt