-- *******************************************************
-- Step 3 Summarize by Date
Select distinct date_friday, sum(new_that_week) as "Total New", sum(new_production) as "New Production", sum(was_open) as "Open Defects", sum(new_testing) as "New Testing",  sum(was_closed) as defects_closed, sum(defect_existing) as total_defects
from (
-- Step 2 Check if Defect was Open / New / Closed that week.
select date_friday, day_week, date_last_sat , bg_bug_id, bg_detection_date, bg_closing_date, test_phase, defect_existing, bg_status, open_date,
(case when bg_detection_date >= date_last_sat and bg_detection_date <= date_friday then 1 else 0 end) as new_that_week,
(case when bg_detection_date >= date_last_sat and bg_detection_date <= date_friday and test_phase = 'Pilot Support' then 1 else 0 end)as new_production,
(case when bg_detection_date >= date_last_sat and bg_detection_date <= date_friday and test_phase = 'System Test' then 1 else 0 end)as new_testing,
(case when bg_closing_date < DATEADD(day,1,date_friday) then 1 else 0 end)as was_closed,
(case when bg_status = 'Open' or(open_date <  DATEADD(day,1,date_friday) and  bg_closing_date >= DATEADD(day,1,date_friday))  then 1 else 0 end) as was_open
from (
--Step 1 get all defects existing during week range
select date_friday, day_week, date_last_sat , bg_bug_id, bg_detection_date, bg_closing_date , bg_user_05 as test_phase, 1 as defect_existing, bg_status,open_date
from (
-- Get all Fridays an calculate week range
select distinct d_friday as date_friday, DATENAME(weekday,d_friday) AS day_week,
DATEADD(day,-6,d_friday) as date_last_sat
from (
-- get all dates
SELECT au_time, DATEADD(dd,4,DATEADD(wk, DATEDIFF(wk,0,au_time), 0)) AS d_friday
FROM   audit_log
where DATEPART(YEAR, au_time) = '2010'
-- end get all dates
) results_1
-- end get all Fridays
) results_2
,  (select bg_bug_id, open_date, bg_detection_date, bg_closing_date , bg_user_05, bg_status
From bug left join (
select distinct au_entity_id, max(au_time) as open_date
from (
SELECT au_entity_id,  au_action_id ,
              ap_old_value, ap_new_value  , au_time
              FROM   Audit_properties,  audit_log
               where  au_action_id = ap_action_id
                 and    ap_field_name = 'BG_STATUS'  and
                  ap_new_value = 'Open'
                  ) aa
group by au_entity_id
) date
on  bg_bug_id  =  au_entity_id ) composed_bug
Where bg_detection_date <= results_2.date_friday
-- end step 1
) results_3
-- end step 2
) results_4 group by date_friday
-- end step 3
-- **********************************************

quote:

---

Originally posted by slimt_slimt

your code is hard to read and therefore it's hard to understand what your problem is.

for the purpose of this post, please try to extract the problem with some sample data.

---