Validate reporting result using sampling method

Feature points sampling method validating reports

--p7, cross year

--2017-12
select lob,PROCESS_NAME,count(loaded) sendto,count(completed) worked,1.0*count(completed)/count(loaded) [% automated]
from tbl_AutomationScorecard
where month(loaded)=12 and year(loaded)=2017
group by lob,PROCESS_NAME

--2018-02
select lob,PROCESS_NAME,count(loaded) sendto,count(completed) worked,1.0*count(completed)/count(loaded) [% automated]
from tbl_AutomationScorecard
where month(loaded)=2 and year(loaded)=2018
group by lob,PROCESS_NAME

--process name subtotal
select lob,PROCESS_NAME,count(loaded) sendto,count(completed) worked,1.0*count(completed)/count(loaded) [% automated]
from tbl_AutomationScorecard
where cast(year(loaded) as varchar)+case when month(loaded)<10 then '0'+cast(month(loaded) as varchar) else cast(month(loaded) as varchar) end between 201712 and 201802
group by lob,PROCESS_NAME

--lob subtotal
select lob,count(loaded) sendto,count(completed) worked,1.0*count(completed)/count(loaded) [% automated]
from tbl_AutomationScorecard
where 
cast(year(loaded) as varchar)+case when month(loaded)<10 then '0'+cast(month(loaded) as varchar) else cast(month(loaded) as varchar) end between 201712 and 201802
group by lob

--total
select count(loaded) sendto,count(completed) worked,1.0*count(completed)/count(loaded) [% automated]
from tbl_AutomationScorecard
where cast(year(loaded) as varchar)+case when month(loaded)<10 then '0'+cast(month(loaded) as varchar) else cast(month(loaded) as varchar) end between 201712 and 201802

point07-query-result
point07-report

Conclusion

Feature points sampling method is easy to implement, with the shortest time frame you could verify the correctness of a report.

125 total views, 1 views today

Author: Albert

Leave a Reply