Days Between Date

[pavan522]

   Hi ,

     I have 2 columns what is product and another one is date

   ex         product            date       difference in days
                  1                     1st jan            0
                   2                     2nd jan           1
                   3                     5th jan            3

i need to get 3 rd columns as above kindly help me out.

[Francis aka khayman]

how do you compute the third column?

3 and 5th Jan = 3?

   Hi ,

     I have 2 columns what is product and another one is date

   ex         product            date       difference in days
                  1                     1st jan            0
                   2                     2nd jan           1
                   3                     5th jan            3

i need to get 3 rd columns as above kindly help me out.

[anjidwh.cognos]

Hi Friend,

USE THE "RUNNING-DIFFERNCE FUNCTION ON DATE COLUMN" AND DATATYPE IS NUMBER, MISSING VALUE AS ZERO.

Thanks
Ram

For Ref:

<report xmlns="http://developer.cognos.com/schemas/report/7.0/" useStyleVersion="10" expressionLocale="en-us">
            <modelPath>/content/folder[@name='SAM']/folder[@name='Packages']/package[@name='StandardClientReporting']/model[@name='2013-12-04T07:03:08.865Z']</modelPath>
            <drillBehavior modelBasedDrillThru="true"/>
            <layouts>
               <layout>
                  <reportPages>
                     <page name="Page1">
                        <style>
                           <defaultStyles>
                              <defaultStyle refStyle="pg"/>
                           </defaultStyles>
                        </style>
                        <pageBody>
                           <style>
                              <defaultStyles>
                                 <defaultStyle refStyle="pb"/>
                              </defaultStyles>
                           </style>
                           <contents><list horizontalPagination="true" refQuery="Query1" name="List1">
         
         
         
         <style>
            <CSS value="border-collapse:collapse"/>
            <defaultStyles>
               <defaultStyle refStyle="ls"/>
            </defaultStyles>
         </style>
      <listColumns><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="product"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lm"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="product"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="date"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lm"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="date"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="Data Item1"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lm"/></defaultStyles><dataFormat><numberFormat whenMissing="0"/></dataFormat></style><contents><textItem><dataSource><dataItemValue refDataItem="Data Item1"/></dataSource></textItem></contents></listColumnBody></listColumn></listColumns></list></contents>
                        </pageBody>
                     </page>
                  </reportPages>
               </layout>
            </layouts>
         <XMLAttributes><XMLAttribute name="RS_CreateExtendedDataItems" value="false" output="no"/><XMLAttribute name="listSeparator" value="," output="no"/></XMLAttributes><queries><query name="Query1">
         <source>
            
         <sqlQuery name="SQL1" dataSource="SAMOLTP">
         <sqlText>select 1 as "product" , 1 as "date" from dual

union

select 2 as "product" , 2 as "date" from dual

union

select 3 as "product" , 5 as "date" from dual
</sqlText>
      <mdProjectedItems><mdProjectedItem name="product"/><mdProjectedItem name="date"/></mdProjectedItems></sqlQuery></source>
         <selection><dataItem name="product"><expression>[SQL1].[product]</expression></dataItem><dataItem name="date"><expression>[SQL1].[date]</expression></dataItem><dataItem name="Data Item1"><expression>running-difference ([date])</expression></dataItem></selection>
      </query></queries></report>