Flagged Scenes Count

Description

Retrieves flagged scene counts by image quality status on a monthly or daily basis.


Query :

--MONTHLY COUNTS
select CAST(YEAR(CreatedOn) AS VARCHAR(4)) + '-' + CAST(MONTH(CreatedOn) AS VARCHAR(2)) 'Session_Date',ClientID,Client,Country,
SubStatus,ManualSubStatusId,
ImageRecognitionStatusRejectedText ManualSubStatus,--ImageQualityStatus,
/*SubClientName,LocationCluster,OutletCode,OutletName,*//*SessionUid,*/Count(SceneUid)Scene_count/*,sceneid,SceneTypeCategory,SceneTypeName,*/
/*convert(char,convert(datetime,ImageQualityLastModifiedOn at time zone 'UTC' at time zone 'Central Standard Time (Mexico)'),120)ImageQualityLastModifiedOn,ImageQualityLastModifiedBy,ValidationCheck,ValidationCheckedUser,IrUserName*/--,SelfUsername,SelfCheck
from vwAssetPurityList with (nolock)
where ClientId IN (450) --and SubClientName = 'Grupo Embotellador Nayar' 
and convert(char,convert(datetime,CreatedOn at time zone 'UTC' at time zone 'Central Standard Time (Mexico)'),120)>= '2025-10-01' /*and 
convert(char,convert(datetime,CreatedOn at time zone 'UTC' at time zone 'Central Standard Time (Mexico)'),120)< '2024-07-30'*/
--and (ltrim(rtrim(SubStatus))<>'' or ltrim(rtrim(ManualSubStatus))<>'')--= 'Picture of Picture'
--and (ltrim(rtrim(ManualSubStatus))<>'' or ltrim(rtrim(SubStatus))<>'')
and (ltrim(rtrim(SubStatus))= 'Picture of Picture' or ImageQualityStatus = '13') --or ltrim(rtrim(ImageQualityStatus))<>'')--= 'Picture of Picture'
--and sceneuid = 'ed07d2a2-4aaa-490c-9cec-c094a8918205'
group by CAST(YEAR(CreatedOn) AS VARCHAR(4)) + '-' + CAST(MONTH(CreatedOn) AS VARCHAR(2)),ClientID,Client,Country,
SubStatus,ManualSubStatusId,ImageRecognitionStatusRejectedText--,ImageQualityStatus
order by Session_Date asc
 
--DAILY COUNTS
select CAST(MONTH(CreatedOn) AS VARCHAR(2)) + '-' + CAST(DAY(CreatedOn) AS VARCHAR(2)) 'Session_Date',ClientID,Client,Country,
SubStatus,ManualSubStatusId,
ImageRecognitionStatusRejectedText ManualSubStatus,ImageQualityStatus,
/*SubClientName,LocationCluster,OutletCode,OutletName,*//*SessionUid,*/Count(SceneUid)Scene_count/*,sceneid,SceneTypeCategory,SceneTypeName,*/
/*convert(char,convert(datetime,ImageQualityLastModifiedOn at time zone 'UTC' at time zone 'Central Standard Time (Mexico)'),120)ImageQualityLastModifiedOn,ImageQualityLastModifiedBy,ValidationCheck,ValidationCheckedUser,IrUserName*/--,SelfUsername,SelfCheck
from vwAssetPurityList with (nolock)
where ClientId IN (450) --and SubClientName = 'Grupo Embotellador Nayar' 
and convert(char,convert(datetime,CreatedOn at time zone 'UTC' at time zone 'Central Standard Time (Mexico)'),120)>= '2025-10-01' /*and 
convert(char,convert(datetime,CreatedOn at time zone 'UTC' at time zone 'Central Standard Time (Mexico)'),120)< '2024-07-30'*/
--and (ltrim(rtrim(SubStatus))<>'' or ltrim(rtrim(ManualSubStatus))<>'')--= 'Picture of Picture'
--and (ltrim(rtrim(ManualSubStatus))<>'' or ltrim(rtrim(SubStatus))<>'')
and (ltrim(rtrim(SubStatus))= 'Picture of Picture' or ImageQualityStatus ='13') --or ltrim(rtrim(ImageQualityStatus))<>'')--= 'Picture of Picture'
--and sceneuid = 'ed07d2a2-4aaa-490c-9cec-c094a8918205'
group by CAST(MONTH(CreatedOn) AS VARCHAR(2)) + '-' + CAST(DAY(CreatedOn) AS VARCHAR(2)),ClientID,Client,Country,
SubStatus,ManualSubStatusId,ImageRecognitionStatusRejectedText,ImageQualityStatus
order by Session_Date asc