Hi,
We have the following report to generate a list with the number of images a client has per policy:
SELECT
domain_jobimage.clientName AS 'CLIENT_NAME',
COUNT(domain_imagecopy.imageid) AS 'NUM_IMAGES',
domain_image.policyName AS 'POLICY_NAME',
domain_image.policyType AS 'POLICY_TYPE',
SUM(domain_image.sizeOfImageInKBytes) AS 'SIZE_KB'
FROM domain_image
LEFT JOIN domain_imagecopy ON domain_image.Id = domain_imagecopy.imageId
JOIN domain_jobimage ON domain_image.id = domain_jobimage.imageid
WHERE
domain_imagecopy.expirationTime >= 122192928000000000
AND
NOM_DATEDIFF(domain_image.expirationTime, NOMTimeToUTCBigint(GETDATE()) ) < 0
AND
domain_image.isValid = 1
AND
NOM_DATEDIFF(domain_imagecopy.expirationTime, NOMTimeToUTCBigint(GETDATE()) ) < 0
AND
domain_imagecopy.isValid = 1
GROUP BY
domain_image.policyName,
domain_image.policyType,
domain_jobimage.clientName
Now normally this works just fine but for some clients we get false informations. We get the info that one client has images in like 6 different policies, when there are actually only images from one.
Has anybody had the same problem or has an idea how to solve it?
Regards
Simon
