@jameslcoleman, Hi James,
I had similar request - glad you posted your query right on time.
I tried your original in sql developer against one of my ALM (V11) project using the project db (Test_Project_db) and it gave me an error which was added in previous comment.

Today, I again tried your original query in Project's Analysis as directed above and unfortunalty, I am getting error. Error (Fig: Analysis) attached. ![alt text][1]

I again tried to run your modified (2nd query) against one of my ALM project (v 11). I again got the same error. Error (sql_dev) attached) below.
![alt text][2]

[1]: /storage/temp/852-analysis.png
[2]: /storage/temp/853-sql-dev.png

fyi---my DB is Oracle. Detali listed as below:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
''CORE 11.2.0.4.0 Production''
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

Hope to hear from you.
As always Thank you!

@jameslcoleman , Hi James, I know you have not forgotten this. But, I would like to keep this alive and on top of the community page so that it keeps refresh for all of us.
Any update on this would be greatly appreciated.

@miraclefren2 , Hello. I have converted the query, and entered it below. Give it a try to see if it works in your oracle db.

Select AP_NEW_VALUE As ''Status'', count(TOTAL_BUGS) As ''Defect Count'', Sum(D1) As ''Day 1'', Sum(D2) As ''Day 2'', Sum(D3) As ''Day 3'', Sum(D4) As ''Day 4'', Sum(D5) As ''Day 5'', Sum(D6) As ''Day 6'', Sum(D7) As ''Day 7'' FROM (
SELECT AP_NEW_VALUE, count(BG_BUG_ID) TOTAL_BUGS,
SUM(CASE WHEN systimestamp - AU_TIME <= 1 then 1 else 0 end) D1, sum(case when systimestamp - AU_TIME <= 2 and systimestamp - AU_TIME > 1 then 1 else 0 end) D2,
sum(case when systimestamp - AU_TIME <= 3 and systimestamp - AU_TIME > 2 then 1 else 0 end) D3,
sum(case when systimestamp - AU_TIME <= 4 and systimestamp - AU_TIME > 3 then 1 else 0 end) D4,
sum(case when systimestamp - AU_TIME <= 5 and systimestamp - AU_TIME > 4 then 1 else 0 end) D5,
sum(case when systimestamp - AU_TIME <= 6 and systimestamp - AU_TIME > 5 then 1 else 0 end) D6,
sum(case when systimestamp - AU_TIME <= 7 and systimestamp - AU_TIME > 6 then 1 else 0 end) D7
FROM td.BUG
INNER JOIN td.AUDIT_LOG al on AU_ENTITY_ID = BG_BUG_ID and AU_ENTITY_TYPE = 'BUG' and AU_ACTION ='UPDATE'
INNER JOIN td.AUDIT_PROPERTIES ap on AP_ACTION_ID = AU_ACTION_ID and AP_FIELD_NAME = 'BG_STATUS'
INNER JOIN (
SELECT MAX(AU_TIME) MY_TIME, AU_ENTITY_ID MY_BUG_ID FROM td.AUDIT_LOG
INNER JOIN td.AUDIT_PROPERTIES on AP_ACTION_ID = AU_ACTION_ID and AP_FIELD_NAME = 'BG_STATUS' and AU_ENTITY_TYPE='BUG' and AU_ACTION='UPDATE'
GROUP BY TO_CHAR(AU_TIME,101), AU_ENTITY_ID
) IJ on MY_TIME = AU_TIME and MY_BUG_ID = BG_BUG_ID
WHERE systimestamp - AU_TIME <= 7 GROUP BY AP_NEW_VALUE, AU_TIME ) MAP GROUP BY AP_NEW_VALUE

@jameslcoleman, Hi James,
Your original query must be fantastic for people who are using mysql. But, its little bit of pain for people like me who are using oracle platform. The main pain is with the time and group by condition in this query while using in mysql vs oracle.
The query which you had modified as expected to work in Oracle did not work because of wrong format of time and group condition.
So, I head down and finally got the result as expected by time and group by parameters.

Thank you so much for your hard work.

Hi James,
Here is a tested query in Oracle platform.

`Select AP_NEW_VALUE As ''Status'', count(TOTAL_BUGS) As ''Defect Count'', Sum(D1) As ''Day 1'', Sum(D2) As ''Day 2'', Sum(D3) As ''Day 3'', Sum(D4) As ''Day 4'', Sum(D5) As ''Day 5'', Sum(D6) As ''Day 6'', Sum(D7) As ''Day 7'' FROM (
SELECT AP_NEW_VALUE, count(BG_BUG_ID) TOTAL_BUGS,
SUM(CASE WHEN SYSDATE- AU_TIME <= 1 then 1 else 0 end) D1, sum(case when SYSDATE- AU_TIME <= 2 and SYSDATE- AU_TIME > 1 then 1 else 0 end) D2,
sum(case when SYSDATE- AU_TIME <= 3 and SYSDATE- AU_TIME > 2 then 1 else 0 end) D3,
sum(case when SYSDATE- AU_TIME <= 4 and SYSDATE- AU_TIME > 3 then 1 else 0 end) D4,
sum(case when SYSDATE- AU_TIME <= 5 and SYSDATE- AU_TIME > 4 then 1 else 0 end) D5,
sum(case when SYSDATE- AU_TIME <= 6 and SYSDATE- AU_TIME > 5 then 1 else 0 end) D6,
sum(case when SYSDATE- AU_TIME <= 7 and SYSDATE- AU_TIME > 6 then 1 else 0 end) D7
FROM test_project_alm_db.BUG
INNER JOIN test_project_alm_db.AUDIT_LOG al on AU_ENTITY_ID = BG_BUG_ID and AU_ENTITY_TYPE = 'BUG' and AU_ACTION ='UPDATE'
INNER JOIN test_project_alm_db.AUDIT_PROPERTIES ap on AP_ACTION_ID = AU_ACTION_ID and AP_FIELD_NAME = 'BG_STATUS'
INNER JOIN (
SELECT MAX(AU_TIME) MY_TIME, AU_ENTITY_ID MY_BUG_ID FROM test_project_alm_db.AUDIT_LOG
INNER JOIN test_project_alm_db.AUDIT_PROPERTIES on AP_ACTION_ID = AU_ACTION_ID and AP_FIELD_NAME = 'BG_STATUS' and AU_ENTITY_TYPE='BUG' and AU_ACTION='UPDATE'
GROUP BY TO_CHAR(AU_TIME,'HH24'), AU_ENTITY_ID
) IJ on MY_TIME = AU_TIME and MY_BUG_ID = BG_BUG_ID
WHERE SYSDATE - AU_TIME <= 7 GROUP BY AP_NEW_VALUE, AU_TIME ) MAP GROUP BY AP_NEW_VALUE` In above query, systimestamp is changed to SYSDATE and 101 in group by condition is changed to 'HH24'.

I have entered the query below that may be what you are looking for. It displays the number of defects for each day, per defect status selected. The report displays the number of defects created over the period of a single week. I have also included a screenshot of the results displayed in the Excel spreadsheet. In the screenshot provided, I had created 15 defects with various statuses. You will need to run this query in the 'Analysis Module' within ALM. To create this report, you will need to create a 'New Excel Report' within the Analysis module, then enter the script below into the 'Configuration' tab. If the 'New Excel Report' option isn't available in the Analysis module, then you will need to add the 'ENABLE_CREATE_LEGACY_EXCEL_REPORT' parameter into the 'Site Configuration' tab within Site Admin, and set the value field to 'Y'. I hope this helps.

Select AP_NEW_VALUE As ''Status'', count(TOTAL_BUGS) As ''Weekly Defects'', Sum(D1) As ''Day 1'', Sum(D2) As ''Day 2'', Sum(D3) As ''Day 3'', Sum(D4) As ''Day 4'', Sum(D5) As ''Day 5'', Sum(D6) As ''Day 6'', Sum(D7) As ''Day 7'' FROM (
SELECT AP_NEW_VALUE, count(BG_BUG_ID) TOTAL_BUGS,
sum( case when datediff(DAY, AU_TIME, getdate()) <= 1 then 1 else 0 end) D1, sum( case when datediff(DAY, AU_TIME, getdate()) <= 2 and datediff(DAY, AU_TIME, getdate()) > 1 then 1 else 0 end) D2,
sum( case when datediff(DAY, AU_TIME, getdate()) <= 3 and datediff(DAY, AU_TIME, getdate()) > 2 then 1 else 0 end) D3,
sum( case when datediff(DAY, AU_TIME, getdate()) <= 4 and datediff(DAY, AU_TIME, getdate()) > 3 then 1 else 0 end) D4,
sum( case when datediff(DAY, AU_TIME, getdate()) <= 5 and datediff(DAY, AU_TIME, getdate()) > 4 then 1 else 0 end) D5,
sum( case when datediff(DAY, AU_TIME, getdate()) <= 6 and datediff(DAY, AU_TIME, getdate()) > 5 then 1 else 0 end) D6,
sum( case when datediff(DAY, AU_TIME, getdate()) <= 7 and datediff(DAY, AU_TIME, getdate()) > 6 then 1 else 0 end) D7
FROM BUG
INNER JOIN AUDIT_LOG on AU_ENTITY_ID = BG_BUG_ID and AU_ENTITY_TYPE='BUG' and AU_ACTION='UPDATE'
INNER JOIN AUDIT_PROPERTIES on AP_ACTION_ID = AU_ACTION_ID and AP_FIELD_NAME = 'BG_STATUS'
INNER JOIN (
SELECT MAX(AU_TIME) MY_TIME, AU_ENTITY_ID MY_BUG_ID FROM AUDIT_LOG
INNER JOIN AUDIT_PROPERTIES on AP_ACTION_ID = AU_ACTION_ID and AP_FIELD_NAME = 'BG_STATUS' and AU_ENTITY_TYPE='BUG' and AU_ACTION='UPDATE'
GROUP BY CONVERT(VARCHAR,AU_TIME,101), AU_ENTITY_ID
) IJ on MY_TIME = AU_TIME and MY_BUG_ID = BG_BUG_ID
WHERE datediff(DAY, AU_TIME, getdate()) <= 7 GROUP BY AP_NEW_VALUE, AU_TIME ) MAP GROUP BY AP_NEW_VALUE ![alt text][1] [1]: /storage/temp/851-capture.png

@miraclefren2 ... I have edited the SQL query to work in SQL Developer when using SQL Server Authentication. I have entered the query below.

Select AP_NEW_VALUE As ''Status'', count(TOTAL_BUGS) As ''Defect Count'', Sum(D1) As ''Day 1'', Sum(D2) As ''Day 2'', Sum(D3) As ''Day 3'', Sum(D4) As ''Day 4'', Sum(D5) As ''Day 5'', Sum(D6) As ''Day 6'', Sum(D7) As ''Day 7'' FROM (
SELECT AP_NEW_VALUE, count(BG_BUG_ID) TOTAL_BUGS,
SUM(CASE WHEN datediff(DAY, AU_TIME, getdate()) <= 1 then 1 else 0 end) D1, sum(case when datediff(DAY, AU_TIME, getdate()) <= 2 and datediff(DAY, AU_TIME, getdate()) > 1 then 1 else 0 end) D2,
sum(case when datediff(DAY, AU_TIME, getdate()) <= 3 and datediff(DAY, AU_TIME, getdate()) > 2 then 1 else 0 end) D3,
sum(case when datediff(DAY, AU_TIME, getdate()) <= 4 and datediff(DAY, AU_TIME, getdate()) > 3 then 1 else 0 end) D4,
sum(case when datediff(DAY, AU_TIME, getdate()) <= 5 and datediff(DAY, AU_TIME, getdate()) > 4 then 1 else 0 end) D5,
sum(case when datediff(DAY, AU_TIME, getdate()) <= 6 and datediff(DAY, AU_TIME, getdate()) > 5 then 1 else 0 end) D6,
sum(case when datediff(DAY, AU_TIME, getdate()) <= 7 and datediff(DAY, AU_TIME, getdate()) > 6 then 1 else 0 end) D7
FROM td.BUG
INNER JOIN td.AUDIT_LOG al on AU_ENTITY_ID = BG_BUG_ID and AU_ENTITY_TYPE = 'BUG' and AU_ACTION ='UPDATE'
INNER JOIN td.AUDIT_PROPERTIES ap on AP_ACTION_ID = AU_ACTION_ID and AP_FIELD_NAME = 'BG_STATUS'
INNER JOIN (
SELECT MAX(AU_TIME) MY_TIME, AU_ENTITY_ID MY_BUG_ID FROM td.AUDIT_LOG
INNER JOIN td.AUDIT_PROPERTIES on AP_ACTION_ID = AU_ACTION_ID and AP_FIELD_NAME = 'BG_STATUS' and AU_ENTITY_TYPE='BUG' and AU_ACTION='UPDATE'
GROUP BY CONVERT(VARCHAR,AU_TIME,101), AU_ENTITY_ID
) IJ on MY_TIME = AU_TIME and MY_BUG_ID = BG_BUG_ID
WHERE datediff(DAY, AU_TIME, getdate()) <= 7 GROUP BY AP_NEW_VALUE, AU_TIME ) MAP GROUP BY AP_NEW_VALUE