Week based count

Question

I have a requirement to retrieve the data in the below fashion

Weeks  delay_count
0           6         
1           0
2           3
3           4
4           0
5           1           
6           0
7           0    
8           0       
9           0
10          2
11          0        
12          0      
13          0    
14          0
15          3

Here weeks is the hard coded column from 0 to 15 and delay_count is the derived column. I have a column delay_weeks. Based on the values in this column I need to populate the values in the delay_count column (derived column)

delay_weeks column values are below.

blank
blank 
blank 
2
10
5
blank 
3
2
10
2
3
3
3
0
0
15
22
29

Conditions:

• When delay_weeks is blank or 0 then count in the delay_count column should be 1
• When delay_weeks is 3 then in the delay_count column the count should be 1 under week 3
• When delay_weeks is 10 then in the delay_count column the count should be 1 under week 10
• When delay_weeks is greater than or equal to 15 then in the delay_count column the count should be 1 under week 15.

I wrote code like below

SELECT   "Weeks", a."delay_count"
    FROM (SELECT     LEVEL AS "Weeks"
                FROM DUAL
          CONNECT BY LEVEL <= 15) m,
         (SELECT   VALUE, COUNT (VALUE) AS "delay_numbers"
              FROM (SELECT CASE
                                          WHEN attr11.VALUE >= 15
                                             THEN '15'
                                          ELSE attr11.VALUE
                                       END
                                     VALUE
                      FROM docs,
                           (SELECT object_id, VALUE, attribute_type_id
                              FROM ATTRIBUTES
                             WHERE attribute_type_id =
                                      (SELECT attribute_type_id
                                         FROM attribute_types
                                        WHERE name_display_code =
                                                 'ATTRIBUTE_TYPE.DELAY IN WEEKS')) attr11
                     WHERE docs.obj_id = attr11.object_id(+)

          GROUP BY VALUE) a
   WHERE m."Weeks" = a.VALUE(+)

Answer 1

Reverse-engineering the relevant parts of the table definitions, I think this gives you what you want:

select t.weeks, count(delay) as delay_count
from (select level - 1 as weeks from dual connect by level <= 16) t
left join (
    select case when a.value is null then 0
        when to_number(a.value) > 15 then 15
        else to_number(a.value) end as delay
    from docs d
    left join (
        select a.object_id, a.value
        from attributes a 
        join attribute_types at on at.attribute_type_id = a.attribute_type_id
        where at.name_display_code = 'ATTRIBUTE_TYPE.DELAY IN WEEKS'
    ) a on a.object_id = d.obj_id
) delays on delays.delay = t.weeks
group by t.weeks
order by t.weeks;

With what I think is matching data I get:

     WEEKS DELAY_COUNT
---------- -----------
         0           6
         1           0
         2           3
         3           4
         4           0
         5           1
         6           0
         7           0
         8           0
         9           0
        10           2
        11           0
        12           0
        13           0
        14           0
        15           3

But obviously since you haven't given the real table structures I'm guessing a bit on the relationships. Obligatory SQL Fiddle.

Answer 2

select
  weeks,
  nvl(cnt, 0) as delay_count
from
  (select level-1 as weeks from dual connect by level < 17)
  left join (
    select 
      nvl(least(attr11.value, 15), 0) as weeks,
      count(0) as cnt
    from 
      DOCS 
      left join (
        ATTRIBUTES attr11 
        join ATTRIBUTE_TYPES atr_tp using(attribute_type_id)
      ) 
        on atr_tp.name_display_code = 'ATTRIBUTE_TYPE.DELAY IN WEEKS'
        and docs.obj_id = attr11.object_id
    group by nvl(least(attr11.value, 15), 0)
  ) using(weeks)
order by 1