How to retain skipped weeks

vpoojari

Hi

In crossTab report, when column group is week-of-year and data is not present for some of the weeks, it skips the weeks of unavailable data.

If there are following data for column group date:
1/Aug/2011
15/Aug/2011

Report is skipping the week in between of these two date.

example: 1st week and then 3rd Week

mcremer

<blockquote class='ipsBlockquote' data-author="'vpoojari'" data-cid="82142" data-time="1314783013" data-date="31 August 2011 - 02:30 AM"><p>
Hi<br />
<br />
In crossTab report, when column group is week-of-year and data is not present for some of the weeks, it skips the weeks of unavailable data.<br />
<br />
If there are following data for column group date:<br />
1/Aug/2011<br />
15/Aug/2011<br />
<br />
Report is skipping the week in between of these two date.<br />
<br />
example: 1st week and then 3rd Week<br /></p></blockquote>
<br />
Hi vpoojari,<br />
<br />
This is a logical result the crosstab will only show actual data. So if a week is not in there it will skip it. If you want to make every week of the year available you have to provide it trough the query. You could do something like this:<br />
<br />
(This is a oracle specific query but it shows the logic what I mean) (My region settings are DUTCH so MA stands for Monday (if your using a english client change it to MON<br />
<br />
<pre class='_prettyXprint _lang-auto _linenums:0'>
select extract(year from next_day( to_date( '04-jan-' || years, 'dd-mon-yyyy' ) + (weeknr-2)*7, 'MA' )) years
,TO_CHAR(next_day( to_date( '04-jan-' || years, 'dd-mon-yyyy' ) + (weeknr-2)*7, 'MA' ), 'IW') weeknr
,next_day( to_date( '04-jan-' || years, 'dd-mon-yyyy' ) + (weeknr-2)*7, 'MA' ) mondaydate
from (select '2011' years
,rownum weeknr
from all_objects
where rownum <= 52 )
</pre>
<br />
Now if you use the with clause and a bit complex querying you can get somting like this:<br />
<br />
<pre class='_prettyXprint _lang-auto _linenums:0'>
with weekyear AS (select extract(year from next_day( to_date( '04-jan-' || years, 'dd-mon-yyyy' ) + (weeknr-2)*7, 'MA' )) years
,TO_CHAR(next_day( to_date( '04-jan-' || years, 'dd-mon-yyyy' ) + (weeknr-2)*7, 'MA' ), 'IW') weeknr
,next_day( to_date( '04-jan-' || years, 'dd-mon-yyyy' ) + (weeknr-2)*7, 'MA' ) mondaydate
from (select '2011' years
,rownum weeknr
from all_objects
where rownum <= 52 ) )
select year
,nvl(pl.week,wy.week)
,nvl(pl.mondaydate,wy.mondaydate)
from <yourtable> pl
,weekyear wy
where pl.mondaydate = wy.mondaydate
<rest of your select>
</pre>
<br />
This will coused to the gaps being filled with the acutal data you want.<br />
<br />
You of course need to make it a outerjoin so that it will show up the missing data. But its a quick example hope it helps.