3 Replies Latest reply: Dec 21, 2012 6:14 AM by HakanBiroglu RSS

    Business Intelligence

    908588
      Hi ,

      I have date format in my table as 'YYYYMMDD' in string .I want to etract data for finacial years.
      I am using the below logic but i find year is displayed twice.eg:2009-2010 and again 2009-1010


      Logic:
      CASE WHEN ((CAST(SUBSTRING(CAST(mobilemsglog.log_dt AS varchar(8)) FROM 5 FOR 2) AS INTEGER))>3) THEN (CONCAT(SUBSTRING(CAST(mobilemsglog.log_dt AS varchar(8))FROM 1 FOR 4),( CONCAT(' - ',CAST((CAST(SUBSTRING(CAST(mobilemsglog.log_dt AS varchar(8))FROM 1 FOR 4) AS integer) +1) AS char) )) )) WHEN( (CAST(SUBSTRING(CAST(mobilemsglog.log_dt AS varchar(8)) FROM 5 FOR 2) AS INTEGER))<=3) THEN( CONCAT(CAST((CAST(SUBSTRING(CAST(mobilemsglog.log_dt AS varchar(8))FROM 1 FOR 4) AS integer)-1) AS char),(CONCAT(' - ',SUBSTRING(CAST(mobilemsglog.log_dt AS varchar(8))FROM 1 FOR 4) )))) END
        • 1. Re: Business Intelligence
          HakanBiroglu
          You only state what is currently happening, but your do not state what you want to happen.
          What is it that you want to achieve?
          • 2. Re: Business Intelligence
            908588
            the year should come only once but it is displayed twice.
            • 3. Re: Business Intelligence
              HakanBiroglu
              That is because your concatenate the year based on the month being past March or not ...

              I rewrote your sql in Oracle so I could test it on my env.

              SELECT
              CASE
              WHEN ( (CAST (SUBSTR('20120101',5, 2) AS NUMBER) )>3)
              THEN (CONCAT (SUBSTR( CAST('20120101' AS VARCHAR(8)), 1 ,4), ( CONCAT (' - ' ,CAST( (CAST( SUBSTR( CAST('20120101' AS VARCHAR(8)), 1, 4) AS NUMBER) +1) AS VARCHAR(10)) )) ))
              WHEN( (CAST (SUBSTR(CAST('20120101' AS VARCHAR(8)), 5, 2) AS NUMBER))<=3)
              THEN ( CONCAT (CAST((CAST(SUBSTR(CAST('20120101' AS VARCHAR(8)), 1 , 4) AS NUMBER)-1) AS VARCHAR(10)) ,(CONCAT(' - ',SUBSTR(CAST('20120101' AS VARCHAR(8)), 1 , 4) ))) )
              END "Fical Year"
              FROM dual

              What you do here is
              if month past March
              then display
              year - (year +1) eg 2012 -2013
              if month before March or in March
              then display
              (year -1) - year eg 2011 -2012

              I understand you want to show only only one year, then do not concatenate the year calculation, something like this

              SELECT
              CASE
              WHEN ( (CAST (SUBSTR('20120101',5, 2) AS NUMBER) )>3)
              THEN (SUBSTR( CAST('20120101' AS VARCHAR(8)), 1 ,4) )
              WHEN( (CAST (SUBSTR(CAST('20120101' AS VARCHAR(8)), 5, 2) AS NUMBER))<=3)
              THEN ( (CAST((CAST(SUBSTR(CAST('20120101' AS VARCHAR(8)), 1 , 4) AS NUMBER)-1) AS VARCHAR(10)) ))
              END "Fical Year"
              FROM dual

              if month past March
              then display
              year eg 2012
              if month before March or in March
              then display
              (year -1) (eg 2011)

              Did I understand you correctly?

              Hakan