Quality Center - Query on Manual Execution (Daily/Weekly)

PROBLEM:
You wanted to know how many manual test cases has been executed (i.e. daily/monthly) per testers

SOLUTION:
You need 4 tables (CYCL_FOLD, CYCLE , and TESTCYCL, TEST )

Query1: (Daily)

SELECT DATEPART(YY,TESTCYCL.TC_EXEC_DATE) AS 'Year #', DATEPART(WK,TESTCYCL.TC_EXEC_DATE) AS 'Week #',
DATEPART(DD,TESTCYCL.TC_EXEC_DATE) AS 'Day #', COUNT(DATEPART(DD,TESTCYCL.TC_EXEC_DATE)) AS 'Day Count', TEST.TS_EXEC_STATUS AS 'Execution Status'
FROM cycl_fold
LEFT OUTER JOIN CYCLE ON cycl_fold.cf_item_id = CYCLE.cy_folder_id
LEFT OUTER JOIN testcycl ON testcycl.tc_cycle_id = CYCLE.cy_cycle_id
LEFT OUTER JOIN TEST ON TESTCYCL.TC_TEST_ID = TEST.TS_TEST_ID
WHERE TESTCYCL.TC_TEST_ID = TEST.TS_TEST_ID
AND cycl_fold.cf_item_path LIKE
(
    SELECT ''+a.cf_item_path + '%'
    FROM (SELECT cf_item_path
         FROM cycl_fold
         WHERE TESTCYCL.TC_EXEC_DATE >= '2012-01-01 00:00:00'
         AND cf_item_name = 'Manual Regression') a
)
GROUP BY DATEPART(YY,TESTCYCL.TC_EXEC_DATE), DATEPART(WK,TESTCYCL.TC_EXEC_DATE), DATEPART(DD,TESTCYCL.TC_EXEC_DATE), TEST.TS_EXEC_STATUS
ORDER BY 1 DESC, 2 DESC, 3 DESC

Note: cycl_fold.cf_item_path LIKE ... part is to only try to get test cases running for particular testset folder

Query2: (Daily with Tester)

SELECT DATEPART(YY,TESTCYCL.TC_EXEC_DATE) AS 'Year #', DATEPART(WK,TESTCYCL.TC_EXEC_DATE) AS 'Week #',
DATEPART(DD,TESTCYCL.TC_EXEC_DATE) AS 'Day #', COUNT(DATEPART(DD,TESTCYCL.TC_EXEC_DATE)) AS 'Day Count', TEST.TS_EXEC_STATUS AS 'Execution Status', testcycl.TC_ACTUAL_TESTER AS 'Tester'
FROM cycl_fold
LEFT OUTER JOIN CYCLE ON cycl_fold.cf_item_id = CYCLE.cy_folder_id
LEFT OUTER JOIN testcycl ON testcycl.tc_cycle_id = CYCLE.cy_cycle_id
LEFT OUTER JOIN TEST ON TESTCYCL.TC_TEST_ID = TEST.TS_TEST_ID
WHERE TESTCYCL.TC_TEST_ID = TEST.TS_TEST_ID
AND cycl_fold.cf_item_path LIKE
(
    SELECT ''+a.cf_item_path + '%'
    FROM (SELECT cf_item_path
         FROM cycl_fold
         WHERE TESTCYCL.TC_EXEC_DATE >= '2012-01-01 00:00:00'
         AND cf_item_name = 'Manual Regression') a
)
GROUP BY DATEPART(YY,TESTCYCL.TC_EXEC_DATE), DATEPART(WK,TESTCYCL.TC_EXEC_DATE), DATEPART(DD,TESTCYCL.TC_EXEC_DATE), TEST.TS_EXEC_STATUS, testcycl.TC_ACTUAL_TESTER
ORDER BY 1 DESC, 2 DESC, 3 DESC

Query3: (Weekly)

SELECT DATEPART(YY,TESTCYCL.TC_EXEC_DATE) AS 'Year #', DATEPART(WK,TESTCYCL.TC_EXEC_DATE) AS 'Week #', COUNT(DATEPART(WK,TESTCYCL.TC_EXEC_DATE)) AS 'Week Count', TEST.TS_EXEC_STATUS AS 'Execution Status'
FROM cycl_fold
LEFT OUTER JOIN CYCLE ON cycl_fold.cf_item_id = CYCLE.cy_folder_id
LEFT OUTER JOIN testcycl ON testcycl.tc_cycle_id = CYCLE.cy_cycle_id
LEFT OUTER JOIN TEST ON TESTCYCL.TC_TEST_ID = TEST.TS_TEST_ID
WHERE TESTCYCL.TC_TEST_ID = TEST.TS_TEST_ID
AND cycl_fold.cf_item_path LIKE
(
    SELECT ''+a.cf_item_path + '%'
    FROM (SELECT cf_item_path
         FROM cycl_fold
         WHERE TESTCYCL.TC_EXEC_DATE >= '2012-01-01 00:00:00'
         AND cf_item_name = 'Manual Regression') a
)
GROUP BY DATEPART(YY,TESTCYCL.TC_EXEC_DATE), DATEPART(WK,TESTCYCL.TC_EXEC_DATE), TEST.TS_EXEC_STATUS
ORDER BY 1 DESC, 2 DESC