Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Sep 2011
    Posts
    220

    Unanswered: limit the rows based on the year

    Hi,

    DB2 V9.1 Z/OS


    Please find the below query and help me to solve it.


    We have to fetch the result set based on the Quarter value

    For ex. Please find below
    Code:
    BASE_TABLE              
    
    P_LOC    P_CODE    NO_FIRST   VOL_NO      WORK_DATE 
    AAA       A11       111       2           2013-10-26
    AAA       A11       111       2           2013-12-16

    based on the WORK_DATE it has two Dates,we have to fetch latest
    Date with in the same Quarter(Please find below)
    Code:
    BASE_TABLE              
    
    P_LOC    P_CODE    NO_FIRST   VOL_NO      WORK_DATE 
    AAA       A11       111       2           2013-12-16

    Code:
    WITH TEMP(P_LOC,P_CODE,NO_FIRST,VOL_NO,WORK_DATE)
    VALUES    ('AAA','A11','111',2,'2013-10-26')
              ('AAA','A11','111',2,'2013-12-16')
              ('AAA','A11','111',2,'2011-04-15')
              ('AAA','A11','111',2,'2011-05-12')
              ('AAA','B22','111',96,'2010-01-16')
              ('AAA','B22','111',96,'2010-02-06')
              ('AAA','B22','111',96,'2010-03-26')
              ('AAA','B22','111',96,'2009-04-01')
    AS(
    SELECT
    P_LOC
    ,P_CODE
    ,NO_FIRST
    ,VOL_NO
    ,ROW_NUMBER
     OVER(PARTITION BY
    P_LOC
    ,P_CODE
    ,NO_FIRST
    ,YEAR(WORK_DATE)
    ,QUARTER(WORK_DATE)
    ORDER BY WORK_DATE DESC)AS RN
    FROM BASE_TABLE
    WHERE
      P_LOC ='AAA'
    
    AND NO_FIRST='111'
    AND YEAR(WORK_DATE)='2014'
    AND RN=1
    )

    Code:
    BASE_TABLE              
    
    P_LOC    P_CODE    NO_FIRST   VOL_NO      WORK_DATE 
    AAA       A11       111       2           2013-10-26
    AAA       A11       111       2           2013-12-16  
    AAA       A11       111       2           2011-04-15   
    AAA       A11       111       2           2011-05-12        
    AAA       B22       111       96          2010-01-16
    AAA       B22       111       96          2010-02-06
    AAA       B22       111       96          2010-03-26
    AAA       B22       111       96          2009-04-01
    ACTUAL RESULT SET (FOR YEAR(WORK_DATE)=2014)
    Code:
    P_LOC    P_CODE    NO_FIRST   VOL_NO      WORK_DATE    RN
    
    AAA       A11       111       2           2013-12-16   1
    AAA       A11       111       2           2011-05-12   1     
    AAA       B22       111       96          2010-03-26   1
    AAA       B22       111       96          2009-04-01   1

    EXPECTED RESULT (FOR YEAR(WORK_DATE)=2014)
    Code:
    P_LOC    P_CODE    NO_FIRST   VOL_NO      WORK_DATE 
    
    AAA       A11       111       2           2013-12-16  
    AAA       A11       111       2           2011-05-12        
    AAA       B22       111       96          2010-03-26
    EXPECTED RESULT (FOR YEAR(WORK_DATE)=2013)
    Code:
    P_LOC    P_CODE    NO_FIRST   VOL_NO      WORK_DATE 
    
    AAA       A11       111       2           2013-12-16  
    AAA       A11       111       2           2011-05-12        
    AAA       B22       111       96          2010-03-26

    EXPECTED RESULT (FOR YEAR(WORK_DATE)=2012)
    Code:
    P_LOC    P_CODE    NO_FIRST   VOL_NO      WORK_DATE 
    
    AAA       A11       111       2           2011-05-12        
    AAA       B22       111       96          2010-03-26
    EXPECTED RESULT (FOR YEAR(WORK_DATE)=2011)
    Code:
    P_LOC    P_CODE    NO_FIRST   VOL_NO      WORK_DATE 
     
     
    AAA       A11       111       2           2011-05-12        
    AAA       B22       111       96          2010-03-26
    THANKS,

  2. #2
    Join Date
    Sep 2011
    Posts
    220
    hi,

    If the given year does not have the Quarter 1 Work_date,we have to back track of the previous year
    and needs to get the row

    Code:
    BASE_TABLE              
    
    P_LOC    P_CODE    NO_FIRST   VOL_NO      WORK_DATE 
    AAA       A11       111       2           2013-10-26
    AAA       A11       111       2           2013-12-16  
    AAA       A11       111       2           2011-04-15   
    AAA       A11       111       2           2011-05-12        
    AAA       B22       111       96          2010-01-16
    AAA       B22       111       96          2010-02-06
    AAA       B22       111       96          2010-03-26
    AAA       B22       111       96          2009-04-01
    if the year is 2013

    EXPECTED RESULT (FOR YEAR(WORK_DATE)=2013)
    Code:
    P_LOC    P_CODE    NO_FIRST   VOL_NO      WORK_DATE 
    
    AAA       A11       111       2           2013-12-16  
    AAA       A11       111       2           2011-05-12        
    AAA       B22       111       96          2010-03-26
    that 2013 year Work_date row did not have Quarter 1 so we have to get it from
    previous year 2011

    Thanks,

  3. #3
    Join Date
    Nov 2011
    Posts
    334
    Maybe you can try this:
    Code:
    WITH TEMP (P_LOC,P_CODE,NO_FIRST,VOL_NO,WORK_DATE) as ( 
    VALUES
    ('AAA','A11','111',2,'2013-10-26')
    ,('AAA','A11','111',2,'2013-12-16')
    ,('AAA','A11','111',2,'2011-04-15')
    ,('AAA','A11','111',2,'2011-05-12')
    ,('AAA','B22','111',96,'2010-01-16')
    ,('AAA','B22','111',96,'2010-02-06')
    ,('AAA','B22','111',96,'2010-03-26')
    ,('AAA','B22','111',96,'2009-04-01')
     )
    SELECT p_loc, 
           p_code, 
           no_first, 
           vol_no, 
           work_date 
    FROM   (SELECT t.*, 
                   ROW_NUMBER() 
                     OVER( 
                       PARTITION BY p_loc, no_first, quarter 
                       ORDER BY work_date desc ) rn2 
            FROM   (SELECT p_loc, 
                           p_code, 
                           no_first, 
                           vol_no, 
                           Quarter(work_date)          AS quarter, 
                           ROW_NUMBER() 
                             OVER( 
                               PARTITION BY p_loc, no_first, YEAR(work_date), 
                                Quarter(work_date) 
                               ORDER BY work_date desc)AS RN, 
                           work_date 
                    FROM   temp) t 
            WHERE  rn = 1 
           AND YEAR(work_date) <= 2012) 
    WHERE  rn2 = 1 
    ORDER  BY work_date;

  4. #4
    Join Date
    Sep 2011
    Posts
    220
    Thanks,

    The suggested query returns the rows like below for the year 2012

    Code:
    P_LOC    P_CODE    NO_FIRST   VOL_NO      WORK_DATE 
    
    AAA       A11       111       2           2011-05-12
    AAA       B22       111       96          2010-03-26
    AAA       B22       111       96          2009-04-01
    but

    EXPECTED RESULT (FOR YEAR(WORK_DATE)=2012)
    Code:
    P_LOC    P_CODE    NO_FIRST   VOL_NO      WORK_DATE 
    
    AAA       A11       111       2           2011-05-12        
    AAA       B22       111       96          2010-03-26
    Please help me..

  5. #5
    Join Date
    Nov 2011
    Posts
    334
    are you sure ? when i execute the query on my pc, it returns:

    Code:
    AAA,B22,111,96,2010-03-26
    AAA,A11,111,2,2011-05-12

  6. #6
    Join Date
    Sep 2011
    Posts
    220
    Hi,

    Thanks again, Below is the qurey wich i executed in the DB2 V9.1 z/os environmemt


    Code:
    SELECT p_loc, 
           p_code, 
           no_first, 
           vol_no, 
           work_date 
    FROM   (SELECT t.*, 
                   ROW_NUMBER() 
                     OVER( 
                       PARTITION BY p_loc, no_first, quarter 
                       ORDER BY work_date desc ) rn2 
            FROM   (SELECT p_loc, 
                           p_code, 
                           no_first, 
                           vol_no, 
                           Quarter(work_date) AS quarter, 
                           ROW_NUMBER() 
                             OVER( 
                               PARTITION BY p_loc, no_first, YEAR(work_date), 
                                Quarter(work_date) 
                               ORDER BY work_date desc)AS RN, 
                           work_date 
                    FROM   BASE_TABLE 
    where 
        P_LOC='CCC'
    AND NO_FIRST='1111'
    
    
    ) t 
            WHERE  rn = 1 
           AND YEAR(work_date) <= 2013
    )R 
    WHERE  rn2 = 1 
    ORDER  BY work_date;

    I have tested for some other input records



    Code:
    
    BASE_TABLE              
    
    P_LOC    P_CODE    NO_FIRST   VOL_NO        WORK_DATE 
    CCC       D55       1111       2           2001-11-24
    CCC       D55       1111       2           2013-03-16  
    CCC       D55       1111       2           2013-04-15   
    CCC       E66       1111       2           2013-03-16        
    CCC       E66       1111       96          2013-04-15
    but,
    Code:
    ACTUAL RESULT SET GETTING FOR YEAR 2013
    
    P_LOC    P_CODE    NO_FIRST   VOL_NO        WORK_DATE 
    CCC       D55       1111       2           2001-11-24
    CCC       E66       1111       2           2013-03-16        
    CCC       E66       1111       96          2013-04-15
    
    
    
    EXPECTING RESULT SET FOR YEAR 2013
    
    P_LOC    P_CODE    NO_FIRST   VOL_NO        WORK_DATE 
    CCC       E66       1111       2           2013-03-16        
    CCC       D55       1111       96          2013-04-15
    Please help..

  7. #7
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Lightbulb Something Simple

    Maybe something like following query:

    Code:
    SELECT
    P_LOC
    ,P_CODE
    ,NO_FIRST
    ,VOL_NO
    ,ROW_NUMBER
     OVER(PARTITION BY
    P_LOC
    ,P_CODE
    ,NO_FIRST
    ,YEAR(WORK_DATE)
    ,QUARTER(WORK_DATE)
    ORDER BY WORK_DATE DESC)AS RN
    FROM BASE_TABLE
    WHERE
      P_LOC ='AAA'
    AND NO_FIRST='111'
    AND RN=1
    AND 
    (YEAR(WORK_DATE) = 2014 
    and exists
    (select 1 
      FROM  BASE_TABLE
     WHERE   P_LOC ='AAA'
       AND   NO_FIRST='111'
       AND  YEAR(WORK_DATE)= 2014 
       and QUARTER(WORK_DATE) = 1 )   )
    or (
     Not exists
    (select 1 
      FROM  BASE_TABLE
     WHERE   P_LOC ='AAA'
       AND   NO_FIRST='111'
       AND  YEAR(WORK_DATE)= 2014 
       and QUARTER(WORK_DATE) = 1)  
       
    AND YEAR(WORK_DATE) = 
    (select max(YEAR(WORK_DATE)) 
      FROM  BASE_TABLE
     WHERE   P_LOC ='AAA'
       AND   NO_FIRST='111'
       and   QUARTER(WORK_DATE) = 1
       AND  YEAR(WORK_DATE)<= 2014 ) ) 
    Try it

    Lenny
    Last edited by Lenny77; 04-09-13 at 15:45.

  8. #8
    Join Date
    Nov 2011
    Posts
    334
    Maybe i misunderstand your requirement.
    I have some questions:
    1、
    If the given year does not have the Quarter 1 Work_date,we have to back track of the previous year and needs to get the row
    if the previous year does not have quarter 1 too, what can we do, track back recursively?
    2、If the given year does not have the Quarter 2,3,4 Work_date,what shall we do?

    3、
    ACTUAL RESULT SET GETTING FOR YEAR 2013

    P_LOC P_CODE NO_FIRST VOL_NO WORK_DATE
    CCC D55 1111 2 2001-11-24
    CCC E66 1111 2 2013-03-16
    CCC E66 1111 96 2013-04-15



    EXPECTING RESULT SET FOR YEAR 2013

    P_LOC P_CODE NO_FIRST VOL_NO WORK_DATE
    CCC E66 1111 2 2013-03-16
    CCC D55 1111 96 2013-04-15
    Why do you need to remove the record of "2001-11-24"?
    4、
    if the year is 2013

    EXPECTED RESULT (FOR YEAR(WORK_DATE)=2013)
    Code:
    P_LOC P_CODE NO_FIRST VOL_NO WORK_DATE

    AAA A11 111 2 2013-12-16
    AAA A11 111 2 2011-05-12 <-- this is quarter 2
    AAA B22 111 96 2010-03-26
    that 2013 year Work_date row did not have Quarter 1 so we have to get it from
    previous year 2011

  9. #9
    Join Date
    Sep 2011
    Posts
    220
    Thanks, My reply in Blue color

    1.if the previous year does not have quarter 1 too, what can we do, track back recursively?

    If the given year does not have Quarter 1 means we have to go back previous year and will get
    the quarter 4 value( if not have Q4 value get Q3 value, if not get Q2 value,if not get Q1 value)

    eg..given year is 2013(it does not have Q1 value) means back track the closest previous year record

    if the rows have 2012 record Q4 value,we should return 2012 year record

    (simple way is ,If the Given yeardoes not have the Quarter1 (Q1) date,so we have to fetch the closest previous year record)


    2、If the given year does not have the Quarter 2,3,4 Work_date,what shall we do?

    Does it mean it has Quarter1 value,,right....No,issue..that year row should be returned
    3.Why do you need to remove the record of "2001-11-24"?

    Because the given year is 2013 ,the table has 2013 year row with Quarter 1 date(2013-03-16),so we should not fetch previous year rows.4."if the year is 2013

    EXPECTED RESULT (FOR YEAR(WORK_DATE)=2013)
    Code:
    P_LOC P_CODE NO_FIRST VOL_NO WORK_DATE

    AAA A11 111 2 2013-12-16
    AAA A11 111 2 2011-05-12 <-- this is quarter 2
    AAA B22 111 96 2010-03-26
    that 2013 year Work_date row did not have Quarter 1 so we have to get it from
    previous year 2011 "


    The given year 2013 does not have Quarter1,Quarter2 and Quarter3 dates,so we have to get it from closest
    previous year date(that is ..2011-05-12)


    Thanks,

  10. #10
    Join Date
    Sep 2011
    Posts
    220
    Thanks Lenny,

    your query is not giving the result when the given year date not available in the table.

    What i mean is ..look the below example.

    Code:
    
    BASE_TABLE              
    
    P_LOC    P_CODE    NO_FIRST   VOL_NO        WORK_DATE 
    CCC       D55       1111       2           2001-11-24
    CCC       D55       1111       2           2013-03-16  
    CCC       D55       1111       2           2013-04-15   
    CCC       E66       1111       2           2013-03-16        
    CCC       E66       1111       96          2013-04-15
    if the given year is 2012,your suggested query did not returns any rows,
    But the qury should return the closest previous year 2001 row
    Code:
    BASE_TABLE              
    
    P_LOC    P_CODE    NO_FIRST   VOL_NO        WORK_DATE 
    CCC       D55       1111       2           2001-11-24

  11. #11
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by Billa007 View Post
    1.if the previous year does not have quarter 1 too, what can we do, track back recursively?

    If the given year does not have Quarter 1 means we have to go back previous year and will get
    the quarter 4 value( if not have Q4 value get Q3 value, if not get Q2 value,if not get Q1 value)

    eg..given year is 2013(it does not have Q1 value) means back track the closest previous year record

    if the rows have 2012 record Q4 value,we should return 2012 year record

    (simple way is ,If the Given yeardoes not have the Quarter1 (Q1) date,so we have to fetch the closest previous year record)


    ...
    ...
    Doesn't it contradict to your first example?
    EXPECTED RESULT (FOR YEAR(WORK_DATE)=2014)

    Code:
    P_LOC    P_CODE    NO_FIRST   VOL_NO      WORK_DATE 
    
    AAA       A11       111       2           2013-12-16
    AAA       A11       111       2           2011-05-12
    AAA       B22       111       96          2010-03-26
    "AAA, A11, 111" does not have Quarter 1 for 2014.
    So, does "back track the closest previous year record" mean to take "2013-12-16" only and to exclude "2011-05-12"?

  12. #12
    Join Date
    Sep 2011
    Posts
    220
    Thanks tonkuma,You are right...sorry for the typo mistake


    EXPECTED RESULT (FOR YEAR(WORK_DATE)=2014)
    Code:
    P_LOC    P_CODE    NO_FIRST   VOL_NO      WORK_DATE 
    
    AAA       A11       111       2           2013-12-16

  13. #13
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Arrow

    Quote Originally Posted by Billa007 View Post
    Thanks Lenny,

    your query is not giving the result when the given year date not available in the table.

    What i mean is ..look the below example.

    Code:
    
    BASE_TABLE              
    
    P_LOC    P_CODE    NO_FIRST   VOL_NO        WORK_DATE 
    CCC       D55       1111       2           2001-11-24
    CCC       D55       1111       2           2013-03-16  
    CCC       D55       1111       2           2013-04-15   
    CCC       E66       1111       2           2013-03-16        
    CCC       E66       1111       96          2013-04-15
    if the given year is 2012,your suggested query did not returns any rows,
    But the qury should return the closest previous year 2001 row
    Code:
    BASE_TABLE              
    
    P_LOC    P_CODE    NO_FIRST   VOL_NO        WORK_DATE 
    CCC       D55       1111       2           2001-11-24
    take a look on your initial query: P_LOC ='AAA' in given now table we have
    p_loc = 'CCC' thats why you have nothing. but we have to change my query in a flex way:

    Code:
    select m.* 
    from
    (SELECT
     P_LOC
    ,P_CODE
    ,NO_FIRST
    ,VOL_NO
    ,ROW_NUMBER () 
    OVER(PARTITION BY 
     P_LOC,P_CODE,NO_FIRST,YEAR(WORK_DATE),QUARTER(WORK_DATE)
     ORDER BY WORK_DATE DESC) RN
    FROM BASE_TABLE            )  M  
    WHERE
    RN=1
    AND 
    (YEAR(M.WORK_DATE) = 2012 
    and exists
    (select 1 
      FROM  BASE_TABLE e
     WHERE   e.P_LOC                 = m.P_LOC    
       AND    e.NO_FIRST             = m.NO_FIRST   
       AND    YEAR(e.WORK_DATE) = YEAR(m.WORK_DATE) 
       and    QUARTER(e.WORK_DATE) = 1    ) )
    or (
     Not exists
    (select 1 
      FROM  BASE_TABLE n
     WHERE   n.P_LOC                        = m.P_LOC    
       AND    n.NO_FIRST                    = m.NO_FIRST   
       AND    YEAR(n.WORK_DATE)        = 2012   
       and    QUARTER(n.WORK_DATE)   = 1    )
       
    AND m.WORK_DATE = 
    (select max(y.WORK_DATE) 
      FROM  BASE_TABLE y
     WHERE   y.P_LOC               = m.P_LOC    
       AND   y.NO_FIRST            = m.NO_FIRST   
       AND   YEAR(y.WORK_DATE)    <= 2012         
       and   QUARTER(y.WORK_DATE)  = 
       (select min(QUARTER(q.WORK_DATE))  
          FROM  BASE_TABLE q
         WHERE  q.P_LOC            = y.P_LOC    
          AND   q.NO_FIRST         = y.NO_FIRST   
          AND   YEAR(q.WORK_DATE)  = YEAR(y.WORK_DATE))
    ) 
    )  
    
    With UR;
    Lenny
    Last edited by Lenny77; 04-10-13 at 11:49.

  14. #14
    Join Date
    Nov 2011
    Posts
    334
    Quote Originally Posted by Billa007 View Post
    Hi,

    Thanks again, Below is the qurey wich i executed in the DB2 V9.1 z/os environmemt


    Code:
    SELECT p_loc, 
           p_code, 
           no_first, 
           vol_no, 
           work_date 
    FROM   (SELECT t.*, 
                   ROW_NUMBER() 
                     OVER( 
                       PARTITION BY p_loc, no_first, quarter 
                       ORDER BY work_date desc ) rn2 
            FROM   (SELECT p_loc, 
                           p_code, 
                           no_first, 
                           vol_no, 
                           Quarter(work_date) AS quarter, 
                           ROW_NUMBER() 
                             OVER( 
                               PARTITION BY p_loc, no_first, YEAR(work_date), 
                                Quarter(work_date) 
                               ORDER BY work_date desc)AS RN, 
                           work_date 
                    FROM   BASE_TABLE 
    where 
        P_LOC='CCC'
    AND NO_FIRST='1111'
    
    
    ) t 
            WHERE  rn = 1 
           AND YEAR(work_date) <= 2013
    )R 
    WHERE  rn2 = 1 
    ORDER  BY work_date;

    I have tested for some other input records



    Code:
    
    BASE_TABLE              
    
    P_LOC    P_CODE    NO_FIRST   VOL_NO        WORK_DATE 
    CCC       D55       1111       2           2001-11-24
    CCC       D55       1111       2           2013-03-16  
    CCC       D55       1111       2           2013-04-15   
    CCC       E66       1111       2           2013-03-16        
    CCC       E66       1111       96          2013-04-15
    but,
    Code:
    ACTUAL RESULT SET GETTING FOR YEAR 2013
    
    P_LOC    P_CODE    NO_FIRST   VOL_NO        WORK_DATE 
    CCC       D55       1111       2           2001-11-24
    CCC       E66       1111       2           2013-03-16        
    CCC       E66       1111       96          2013-04-15
    
    
    
    EXPECTING RESULT SET FOR YEAR 2013
    
    P_LOC    P_CODE    NO_FIRST   VOL_NO        WORK_DATE 
    CCC       E66       1111       2           2013-03-16        
    CCC       D55       1111       96          2013-04-15
    Please help..
    Although I dont understand why you need to remove the record :
    CCC E66 1111 96 2013-04-15
    CCC D55 1111 2 2013-03-16

    but here is a query you can try:
    Code:
    WITH TEMP (P_LOC,P_CODE,NO_FIRST,VOL_NO,WORK_DATE) as ( 
    VALUES
    ('AAA','A11','111',2,'2013-10-26')
    ,('AAA','A11','111',2,'2013-12-16')
    ,('AAA','A11','111',2,'2011-04-15')
    ,('AAA','A11','111',2,'2011-05-12')
    ,('AAA','B22','111',96,'2010-01-16')
    ,('AAA','B22','111',96,'2010-02-06')
    ,('AAA','B22','111',96,'2010-03-26')
    ,('AAA','B22','111',96,'2009-04-01')
     )
    select 
       p_loc
      ,p_code
      ,no_first
      ,vol_no
      ,work_date 
    from ( SELECT p_loc, 
             p_code, 
             no_first, 
             vol_no, 
             work_date,
             year,
             quarter,
             count(case when year = 2013  then p_loc end)
               over(
                 PARTITION BY p_loc, p_code, no_first
                 ) as cnt,
             count( case when year = 2013 and quarter = 1 then p_loc end)
               over( 
                 PARTITION BY p_loc, p_code, no_first
                 ) as cnt2,
             row_number()
               over(
                 PARTITION BY p_loc, p_code, no_first
                   order by work_date desc
                ) as rn2  
       FROM (  SELECT p_loc, 
                      p_code, 
                      no_first, 
                      vol_no, 
                      year(work_date) as year,
                      Quarter(work_date)  AS quarter, 
                      ROW_NUMBER() 
                          OVER( 
                             PARTITION BY p_loc, p_code, no_first, 
                                 YEAR(work_date), Quarter(work_date) 
                             ORDER BY work_date desc
                          )AS RN, 
                      work_date 
                    FROM   temp ) t 
            WHERE  rn = 1 
            AND YEAR(work_date) <= 2013 )
    where rn2 <= cnt + ( case when cnt2 =0 then 1 else 0 end)
    ORDER  BY work_date;

  15. #15
    Join Date
    Sep 2011
    Posts
    220
    Thanks all,

    I was trying with Lenny query,it gives correct result set in some scenario
    but it returns the wrong result set for the follwing example
    Code:
    P_LOC    P_CODE    NO_FIRST   VOL_NO        WORK_DATE 
     
    CCC       D55       1111       5           2012-06-16  
    CCC       D55       1111       0           2012-08-15   
    CCC       E66       1111       5           2012-06-16        
    CCC       E66       1111       10          2012-08-15
    ACTUAL RESULT SET GETTING FOR YEAR 2013
    Code:
    P_LOC    P_CODE    NO_FIRST   VOL_NO        WORK_DATE 
     
    CCC       D55       1111       5           2012-06-16    
    CCC       E66       1111       5           2012-06-16
    EXPECTED RESULT (FOR YEAR(WORK_DATE)=2013)
    Code:
    P_LOC    P_CODE    NO_FIRST   VOL_NO        WORK_DATE 
     
    CCC       D55       1111       5           2012-06-16  
    CCC       D55       1111       0           2012-08-15 -->3rd quqrter  
    CCC       E66       1111       5           2012-06-16        
    CCC       E66       1111       10          2012-08-15-->3rd quqrter

    According to fengsun2 query,i am facing the below error


    SQL0104N An unexpected token "(" was found following "". Expected tokens may
    include: ", FROM INTO". SQLSTATE=42601


    i am realizing that below code will be the problem that i guess

    "
    ...
    ...
    quarter,
    count(case when year = 2013 then p_loc end)
    over(
    PARTITION BY p_loc, p_code, no_first
    ) as cnt,
    count( case when year = 2013 and quarter = 1 then p_loc end)


    ...
    ...

    Thanks,

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •