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

    Unanswered: past and future year record

    Hi,

    Please help to write a query based on the conditions

    SELECT query has to pick the records based on such scenarios
    starting year of PUR_DATE will be current year-1 (if pur_date
    year is not exist means go for very closest year record like..
    2013 year not exist means go for 2012 year record) to current year + 3 year

    Code:
    Example 1
    
    ID_NO    ID_NAME  PUR_DATE   ID_ADD  ID_IND     
    
    1234	AA	2011-12-13    AA        I   
    1234	B1	2011-12-14    B1        I 
    1234	AA	2012-03-27    AA        I
    1234	B1	2012-03-28    B1        I
    1234	AA	2013-03-27    AA        I
    1234	B1	2013-03-28    B1        D
    1234	B1	2014-03-12    B1        I
    Example 1:

    This example output has current year(2014) record and past year(2013) record


    Example 1 Expected OUTPUT
    Code:
    ID_NO    ID_NAME  PUR_DATE   ID_ADD  ID_IND     
    
    1234	B1	2013-03-28    B1        D
    1234	B1	2014-03-12    B1        I
    Code:
    Example 2
    
    ID_NO    ID_NAME  PUR_DATE   ID_ADD  ID_IND     
    
    1234	1B	2012-03-28    1B        I
    1234	BB	2012-03-27    BB        D
    1234	1B	2012-11-28    1B        D
    1234	AA	2017-03-11    AA        D
    1234	BB	2023-03-12    BB        D
    Example 2:

    This example output has current year + 3 year record and past year(2012) record(2013 year not exist so 2012)


    Example 2 Expected OUTPUT
    Code:
    ID_NO    ID_NAME  PUR_DATE   ID_ADD  ID_IND
    1234	1B	2012-11-28    1B        D
    1234	AA	2017-03-11    AA        D

    Code:
    Example 3
    
    ID_NO    ID_NAME  PUR_DATE   ID_ADD  ID_IND     
    
    1234	AA	2012-12-13    AA        I   
    1234	B1	2012-12-14    B1        I 
    1234	B1	2013-11-28    AA        I
    1234	B1	2013-11-28    B1        I
    1234	AA	2014-03-27    AA        I
    1234	AA	2014-03-27    AA        D
    1234	CC	2017-03-11    AA        I
    1234	AA	2017-03-11    B1        I
    Example 3:

    This example output has current year + 3 year record and current year record and past year(2013) record
    2017 year has 2 record bcoz ID_NAME has distinct values

    Example 3 Expected OUTPUT
    Code:
    ID_NO    ID_NAME  PUR_DATE   ID_ADD  ID_IND     
     
    1234	B1	2013-11-28    B1        I
    1234	AA	2014-03-27    AA        D
    1234	CC	2017-03-11    AA        I
    1234	AA	2017-03-11    B1        I
    DB2 V 9.1 Z/OS

    Thanks,

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Code:
    Example 3
    
    ID_NO    ID_NAME  PUR_DATE   ID_ADD  ID_IND     
    
    ...
    ...
    1234	B1	2013-11-28    AA        I
    1234	B1	2013-11-28    B1        I
    1234	AA	2014-03-27    AA        I
    1234	AA	2014-03-27    AA        D
    ...
    ...
    Why took those for year 2014 and 2013?

    Example 3 Expected OUTPUT

    Code:
    ID_NO    ID_NAME  PUR_DATE   ID_ADD  ID_IND     
     
    1234	B1	2013-11-28    B1        I
    1234	AA	2014-03-27    AA        D
    ...
    ...
    and NOT those.
    Code:
    1234	B1	2013-11-28    AA        I
    1234	AA	2014-03-27    AA        I
    ...
    ...

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Are there any data like this?

    (I marked Bold/Red for added rows.)
    Code:
    Example 3
    
    ID_NO    ID_NAME  PUR_DATE   ID_ADD  ID_IND     
    
    1234	AA	2012-12-13    AA        I   
    1234	B1	2012-12-14    B1        I 
    1234	B1	2013-11-28    AA        I
    1234	B2	2013-11-28    AA        I
    1234	B1	2013-11-28    B1        I
    1234	AA	2014-03-27    AA        I
    1234	AA	2014-03-27    AA        D
    1234	BB	2014-03-27    AA        I
    1234	CC	2017-03-11    AA        I
    1234	AA	2017-03-11    B1        I
    1234	DD	2017-03-11    AA        I
    1234	EE	2017-03-11    AA        I
    More than 3 rows having distinct ID_NAME (and all same PUR_DATE) for year 2017.
    Two identical ID_NAME and a different ID_NAME (and all same PUR_DATE) for year 2014.
    Two identical ID_NAME and a different ID_NAME (and all same PUR_DATE) for year 2013.


    If there were such data,
    what results do you want?

  4. #4
    Join Date
    Sep 2011
    Posts
    220
    Thanks for the reply!


    Code:
    Example 3

    ID_NO ID_NAME PUR_DATE ID_ADD ID_IND

    ...
    ...
    1234 B1 2013-11-28 AA I
    1234 B1 2013-11-28 B1 I
    1234 AA 2014-03-27 AA I
    1234 AA 2014-03-27 AA D
    ...
    ...Why took those for year 2014 and 2013?

    Example 3 Expected OUTPUT


    Code:
    ID_NO ID_NAME PUR_DATE ID_ADD ID_IND

    1234 B1 2013-11-28 B1 I
    1234 AA 2014-03-27 AA D
    ...
    ...and NOT those.

    Code:
    1234 B1 2013-11-28 AA I
    1234 AA 2014-03-27 AA I
    ...
    ...
    excuse me for the typo mistake..we have to consider the indicator values(ID_IND).preference would be given to 'D' record if the ID_NAME,PUR_DATE,ID_ADD has same value..

    so

    1234 AA 2014-03-27 AA D
    is the correct record

    here typo mistake in PUR_DATE

    ID_NO ID_NAME PUR_DATE ID_ADD ID_IND

    ...
    ...
    1234 B1 2013-11-28 AA I
    1234 B1 2013-11-29 B1 I

    Are there any data like this?

    (I marked Bold/Red for added rows.)

    Code:
    Example 3

    ID_NO ID_NAME PUR_DATE ID_ADD ID_IND

    1234 AA 2012-12-13 AA I
    1234 B1 2012-12-14 B1 I
    1234 B1 2013-11-28 AA I
    1234 B2 2013-11-28 AA I
    1234 B1 2013-11-28 B1 I
    1234 AA 2014-03-27 AA I
    1234 AA 2014-03-27 AA D
    1234 BB 2014-03-27 AA I
    1234 CC 2017-03-11 AA I
    1234 AA 2017-03-11 B1 I
    1234 DD 2017-03-11 AA I
    1234 EE 2017-03-11 AA I
    More than 3 rows having distinct ID_NAME (and all same PUR_DATE) for year 2017.
    Two identical ID_NAME and a different ID_NAME (and all same PUR_DATE) for year 2014.
    Two identical ID_NAME and a different ID_NAME (and all same PUR_DATE) for year 2013.


    If there were such data,
    what results do you want?
    For those scenario that ID_IND will have different values
    input record might be below like this
    Code:
    Example 3
    
    ID_NO    ID_NAME  PUR_DATE   ID_ADD  ID_IND     
    
    1234	AA	2012-12-13    AA        I   
    1234	B1	2012-12-14    B1        I 
    1234	B1	2013-11-28    AA        I
    1234	B2	2013-11-28    AA        D
    1234	B1	2013-11-28    B1        I
    1234	AA	2014-03-27    AA        I
    1234	AA	2014-03-27    AA        D
    1234	BB	2014-03-27    AA        I
    1234	CC	2017-03-11    AA        I
    1234	AA	2017-03-11    B1        I
    1234	DD	2017-03-11    AA        I
    1234	EE	2017-03-11    AA        D
    the expected output would be
    Code:
    ID_NO    ID_NAME  PUR_DATE   ID_ADD  ID_IND     
         
    1234	B1	2012-12-14    B1        I  
    1234	B1	2013-11-28    B1        D
    1234	AA	2014-03-27    AA        D
    1234	EE	2017-03-11    AA        D
    Why below reord comes to output means
    1234 B1 2012-12-14 B1 I

    for the year of 2013 the PUR_DATE has 4th quarter date so , for the remainig three
    quarters (1,2,3) we have to get it from previuos year record like PUR_DATE
    (2012-12-14)

    Thanks,

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    ...
    ...

    Why below reord comes to output means
    1234 B1 2012-12-14 B1 I

    for the year of 2013 the PUR_DATE has 4th quarter date so , for the remainig three
    quarters (1,2,3) we have to get it from previuos year record like PUR_DATE
    (2012-12-14)
    You added new considerations to quarters(which was not in the first post) to your requirement.

    I don't like this way(sarting from simple case, then (sometimes, after some discussions) add some more requirements, little by little.
    I like to know all requirements in the early stages, then consider the final solution(s) at once.
    (except, when the problem was very complex and the solutions might require some hundreds or thousands lines of code).


    I would appreciate it if you expressed(re-stated) your requirements like this way ...
    (1) itemise/enumerate whole your requirements including few or exceptional(but possible/considerable) cases.
    Note 1: It might be better to include the examples/cases which were already provided by you(and me?).
    Note 2: It might be desirable if you mentioned to (at least) ID_NAME, PUR_DATE and ID_IND in each of your requirement items.
    (If some column were ignorable, state that fact explicitly.)

    (2) then show sample data and expected results from the data for every cases listed(/itemised) in (1).
    Last edited by tonkuma; 04-01-14 at 14:57.

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

    Apology for the delaying response..to get finalize the requirement leads to delay


    Please ignore previous post..

    DB2 v9.1 z/os

    Requirements:

    1.Fetch the latest date record based on Quarters between current year - 1 year to current year + 3 years
    2.Incase ID_NO doesnt have current year-1year(as of now 2013) go for previous closest year(In example
    see ID_NO is 1666)for that example 2013 year is not available ,so need to pick closest year 2010 year record(2010-03-01)
    3.Fetch all 4 quarter latest date values if available,suppose 1st quarter date is not exist means we go for closest previous
    year 4th quarter value(if 4th QTR is not,go for 3rd QTR,if not go for 2nd QTR like that..)
    4.If the PUR_DATE are same for one ID_NO cloumn,calculate the SUM of ID_QNTY(it always 10)
    5.Fetch the rows based on the ID_IND column
    for example:ID_NO =1336,it has same PUR_DATE and 3 different ID_NAME and the preference
    should be given to D row (SUM of ID_QNTY also 10)


    Qty_pri_table
    Code:
    ID_NO    ID_NAME  PUR_DATE   ID_ADD  ID_IND  ID_QNTY
    
    1222	BB	2012-09-14    BB        I      10   
    1222	BB	2012-09-21    BB        I      10-->Need to fetch,latest date in 3rd Qtr in 2012   
    1222	BB	2013-11-28    BB        I      10-->Need to fetch,latest date in 4th Qtr in 2013 
    1222	BB	2014-02-28    BB        I      10-->Need to fetch,latest date in 1st Qtr in 2014 
    
    1223	A1	2012-09-14    BB        D       4   
    1223	A2	2012-09-14    BB        D      10-->Need to fetch,latest date in 3rd Qtr in 2012 and D row   
    1223	B1	2014-02-28    B1        I      10 
    1223	B1	2014-02-28    B2        D      10-->Need to fetch,latest date in 1st Qtr in 2014 and D row
       
    1234	AA	2009-09-12    AA        I      10  
    1234	AA	2009-09-30    AA        I      10 
    1234	AA	2013-03-01    AA        I      10 
    1234	AA	2013-03-28    AA        I      10-->Need to fetch,latest date in 1st Qtr in 2013 
    1234	AA	2013-12-25    AA        I      10 
    1234	AA	2013-12-29    AA        I      10-->Need to fetch,latest date in last Qtrin 2013  
    1234	AA	2014-03-30    AA        I      10-->Need to fetch,latest date in 1st Qtr  in 2014
    1234	AA	2015-06-01    AA        I      10-->Need to fetch,latest date in 2nd Qtr in 2014 
    1234	AA	2016-03-28    AA        I      10-->Need to fetch,latest date in 1st Qtr in 2016 
    1234	AA	2020-12-25    AA        I      10   
    
    1333    BB	2013-03-21    BB        I       6
    1333	AA	2013-03-27    AA        I       5-->Need to fetch,latest date in 1st Qtr in 2013
    1333	BB	2013-03-27    BB        I       5-->Need to fetch,latest date in 1st Qtr in 2013
                                                        sum of ID_QTY should be 10 with same date
    1333	BB	2013-06-27    BB        I      10-->Need to fetch,latest date in 2nd Qtr in 2013
    1333	CC	2014-03-11    CC        I       2-->Need to fetch,latest date in 1st Qtr in 2014
    1333	CD	2014-03-11    CD        I       2-->Need to fetch,latest date in 1st Qtr in 2014
    1333	CE	2014-03-11    CE        I       2-->Need to fetch,latest date in 1st Qtr in 2014
    1333	CF	2014-03-11    CF        I       2-->Need to fetch,latest date in 1st Qtr in 2014
    1333	CG	2014-03-11    CG        I       2-->Need to fetch,latest date in 1st Qtr in 2014
                                                      sum of ID_QTY should be 10 with same date
    
    1336	C1	2014-03-21    C1        I       10
    1336	CF	2014-03-21    FF        D        5-->Need to fetch,latest date in 1st Qtr in 2014 and D row
    1336	CG	2014-03-21    CG        D        5-->Need to fetch,latest date in 1st Qtr in 2014 and D row
                                                      sum of ID_QTY should be 10 with same date
      
    1444	BB	2012-09-14    BB        I      10   
    1444    BB	2012-09-21    BB        I      10-->Need to fetch,latest date in 3rd Qtr in 2012 
    1444	BB	2013-11-28    BB        I      10
    1444	BB	2013-12-01    CC        I      10-->Need to fetch,latest date in 4th Qtr in 2013
    1444	BB	2014-03-26    BB        I      10
    1444	BB	2014-03-27    DD        I      10-->Need to fetch,latest date in 1ST Qtr in 2014
    
    1555	XX	2013-03-27    XX        I      10-->Need to fetch,latest date in 1ST Qtr in 2013
    1555	XX	2013-06-27    XX        I      10-->Need to fetch,latest date in 2ND Qtr in 2013
    1555	XX	2013-12-12    CC        I      10
    1555	YY	2013-12-25    YY        D      10-->Need to fetch,latest date in 4th Qtr in 2013
    1555	XX	2014-03-11    XX        I      10-->Need to fetch,latest date in 1st Qtr in 2014
    1555	XX	2014-09-11    XX        I      10
    1555	XX	2014-09-30    XX        D      10-->Need to fetch,latest date in 3rd Qtr in 2014
    
    1666    zz      2010-02-21    zz        I      10
    1666    Z1      2010-03-01    Z1        D      10-->Need to fetch,latest date in 3rd Qtr in 2010
    1666    Z2      2015-05-31    Z2        D      10-->Need to fetch,latest date in 3rd Qtr in 2015
    Code:
    WITH TEMP(ID_NO,ID_NAME,PUR_DATE,ID_ADD,ID_IND,ID_QNTY)
    VALUES  
    ('1222','BB','2012-09-14','BB','I','10')   
    ('1222','BB','2012-09-21','BB','I','10')
    ('1222','BB','2013-11-28','BB','I','10')
    ('1222','BB','2014-02-28','BB','I','10')
    ('1223','A1','2012-09-14','BB','D','4')   
    ('1223','A2','2012-09-14','BB','D','10')
    ('1223','B1','2014-02-28','B1','I','10')
    ('1223','B2','2014-02-28','B2','D','10')
    ('1234','AA','2009-09-12','AA','D','10')  
    ('1234','AA','2009-09-30','AA','I','10') 
    ('1234','AA','2013-03-01','AA','I','10') 
    ('1234','AA','2013-03-28','AA','I','10')
    ('1234','AA','2013-12-25','AA','I','10') 
    ('1234','AA','2013-12-29','AA','I','10')  
    ('1234','AA','2014-03-30','AA','I','10')
    ('1234','AA','2015-06-01','AA','I','10')
    ('1234','AA','2016-03-28','AA','I','10')
    ('1234','AA','2020-12-25','AA','I','10') 
    ('1333','BB','2013-03-21','BB','I','6')
    ('1333','AA','2013-03-27','AA','I','5')
    ('1333','BB','2013-03-27','BB','I','5')                                              
    ('1333','BB','2013-06-27','BB','I','10')
    ('1333','CC','2014-03-11','CC','I','2')
    ('1333','CD','2014-03-11','CD','I','2')
    ('1333','CE','2014-03-11','CE','I','2')
    ('1333','CF','2014-03-11','CF','I','2')
    ('1333','CG','2014-03-11','CG','I','2')  
    ('1336','C1','2014-03-21','C1','I','10')
    ('1336','CF','2014-03-21','FF','D','5')
    ('1336','CG','2014-03-21','CG','D','5')                                            
    ('1444','BB','2012-09-14','BB','D','10')  
    ('1444','BB','2012-09-21','BB','I','10')
    ('1444','BB','2013-11-28','BB','I','10')
    ('1444','BB','2013-12-01','CC','I','10')
    ('1444','BB','2014-03-26','BB','I','10')
    ('1444','BB','2014-03-27','DD','I','10')
    ('1555','XX','2013-03-27','XX','I','10')
    ('1555','XX','2013-06-27','XX','I','10')
    ('1555','XX','2013-12-12','CC','I','10')
    ('1555','YY','2013-12-25','YY','D','10')
    ('1555','XX','2014-03-11','XX','I','10')
    ('1555','XX','2014-09-11','XX','I','10')
    ('1555','XX','2014-09-30','XX','D','10')
    ('1666','zz','2010-02-21','zz','I','10')
    ('1666','Z1','2010-03-01','Z1','D','10')
    ('1666','Z2','2015-05-31','Z2','D','10') 
    as (
    SELECT 
    ID_NO,ID_NAME,PUR_DATE,ID_ADD,ID_IND,ID_QNTY
    
    FROM Qty_pri_table
    EXPECTED RESULTSET

    Code:
    ID_NO    ID_NAME  PUR_DATE   ID_ADD  ID_IND  ID_QNTY  
     
    1222	BB	2012-09-21    BB        I      10   
    1222	BB	2013-11-28    BB        I      10
    1222	BB	2014-02-28    BB        I      10
    1223	A2	2012-09-14    BB        D      10
    1223	B1	2014-02-28    B2        D      10
    1234	AA	2013-03-28    AA        I      10
    1234	AA	2013-12-29    AA        I      10 
    1234	AA	2014-03-30    AA        I      10
    1234	AA	2015-06-01    AA        I      10 
    1234	AA	2016-03-28    AA        I      10
    1333	AA	2013-03-27    AA        I       5
    1333	BB	2013-03-27    BB        I       5
    1333	BB	2013-06-27    BB        I      10
    1333	CC	2014-03-11    CC        I       2
    1333	CD	2014-03-11    CD        I       2
    1333	CE	2014-03-11    CE        I       2
    1333	CF	2014-03-11    CF        I       2
    1333	CG	2014-03-11    CG        I       2
    1336	CF	2014-03-21    FF        D       5
    1336	CG	2014-03-21    CG        D       5  
    1444    BB	2012-09-21    BB        I      10 
    1444	BB	2013-12-01    CC        I      10
    1444	BB	2014-03-27    DD        I      10
    1555	XX	2013-03-27    XX        I      10
    1555	XX	2013-06-27    XX        I      10
    1555	YY	2013-12-25    YY        D      10
    1555	XX	2014-03-11    XX        I      10
    1555	XX	2014-09-30    XX        D      10
    1666    Z1      2010-03-01    Z1        D      10
    1666    Z2      2015-05-31    Z2        D      10
    Please let me know,if anything is not sufficient

    Thanks,

  7. #7
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Billa,
    Sorry I am not as verbose or as dilligent in writing out the entire SQL like some of our friends here. It seems to me that what you are really after is the MAX quarters where the date is <= current date + 3 years. The bottom range you supplied above really isn't a bottom range, since you state if the prior year doesn't exist go back to the most recent and give a 2010 in your example above, so that bottom range should jst be removed from your reqs.
    Dave

  8. #8
    Join Date
    Sep 2011
    Posts
    220
    Thanks for the inputs,But wants the prior year record if last year record does not exist..

  9. #9
    Join Date
    Sep 2011
    Posts
    220
    Hi,
    Any updates!..

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

    Please help me,

    Db2 v9.1 z/os

    I want the select qry to fetch the records from prior year

    1.If the record has prior year and quarter 1 record then no need of old records(like 2012...)

    Input records

    ID_NO ID_NAME PUR_DATE
    1222 BB 2012-06-14
    1222 BB 2012-09-21
    1222 BB 2013-03-28
    1222 BB 2014-02-28

    Expected output

    ID_NO ID_NAME PUR_DATE

    1222 BB 2013-03-28
    1222 BB 2014-02-28



    2.if the prior year(2013) quarter 1 record is not exist and the qry has to return the
    latest prior year latest quarter record


    Input records

    ID_NO ID_NAME PUR_DATE
    1223 BB 2012-06-19
    1223 BB 2012-09-15
    1223 BB 2013-05-28
    1223 BB 2014-02-28

    Expected output


    ID_NO ID_NAME PUR_DATE

    1223 BB 2012-09-15
    1223 BB 2013-05-28
    1223 BB 2014-02-28

    Thanks,

  11. #11
    Join Date
    Sep 2011
    Posts
    220
    Hi tonkuma,
    I posted the final requirement earlier,Please let me know if anything needs,

    Thanks,

  12. #12
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I felt that your requirements include some inconsistencies and/or I couldn't understand your requirements fully.

    (1) You might suggested that you want to get the resuls by ID_IND column.
    For example: "5.Fetch the rows based on the ID_IND column"

    But, from the EXPECTED RESULTSET like the following,
    I thought that you might want the result for every ID_NO and ID_NAME, not only for ID_NO.

    Code:
    ID_NO    ID_NAME  PUR_DATE   ID_ADD  ID_IND  ID_QNTY  
    
    ... 
    1333	AA	2013-03-27    AA        I       5
    1333	BB	2013-03-27    BB        I       5
    1333	BB	2013-06-27    BB        I      10
    1333	CC	2014-03-11    CC        I       2
    1333	CD	2014-03-11    CD        I       2
    1333	CE	2014-03-11    CE        I       2
    1333	CF	2014-03-11    CF        I       2
    1333	CG	2014-03-11    CG        I       2
    ...

    (2) You wrote
    Qty_pri_table

    Code:
    ID_NO    ID_NAME  PUR_DATE   ID_ADD  ID_IND  ID_QNTY
    
    ...
    1336	C1	2014-03-21    C1        I       10
    1336	CF	2014-03-21    FF        D        5-->Need to fetch,latest date in 1st Qtr in 2014 and D row
    1336	CG	2014-03-21    CG        D        5-->Need to fetch,latest date in 1st Qtr in 2014 and D row
                                                      sum of ID_QTY should be 10 with same date
    
    ...
    But, I couldn't find summed ID_QNTY in your EXPECTED RESULTSET.


    (3) Why the rows marked in the following data not included in the EXPECTED RESULTSET?

    Code:
    WITH TEMP(ID_NO,ID_NAME,PUR_DATE,ID_ADD,ID_IND,ID_QNTY)
    VALUES
    ('1222','BB','2012-09-14','BB','I','10')
    ('1222','BB','2012-09-21','BB','I','10')
    ('1222','BB','2013-11-28','BB','I','10')
    ('1222','BB','2014-02-28','BB','I','10')
    ('1223','A1','2012-09-14','BB','D', '4') -- Why this row desn't be included in the result?
    ('1223','A2','2012-09-14','BB','D','10')
    ('1223','B1','2014-02-28','B1','I','10') -- The result took ID_NO  = 'B1'
    ('1223','B2','2014-02-28','B2','D','10') -- The result took ID_IND = 'D', ID_ADD = 'B2'
                                             -- And combined two rows into one result row.
    ('1234','AA','2009-09-12','AA','D','10')
    ('1234','AA','2009-09-30','AA','I','10')
    ('1234','AA','2013-03-01','AA','I','10')
    ('1234','AA','2013-03-28','AA','I','10')
    ('1234','AA','2013-12-25','AA','I','10')
    ('1234','AA','2013-12-29','AA','I','10')
    ('1234','AA','2014-03-30','AA','I','10')
    ('1234','AA','2015-06-01','AA','I','10')
    ('1234','AA','2016-03-28','AA','I','10')
    ('1234','AA','2020-12-25','AA','I','10')
    ('1333','BB','2013-03-21','BB','I', '6')
    ('1333','AA','2013-03-27','AA','I', '5')
    ('1333','BB','2013-03-27','BB','I', '5')
    ('1333','BB','2013-06-27','BB','I','10')
    ('1333','CC','2014-03-11','CC','I', '2')
    ('1333','CD','2014-03-11','CD','I', '2')
    ('1333','CE','2014-03-11','CE','I', '2')
    ('1333','CF','2014-03-11','CF','I', '2')
    ('1333','CG','2014-03-11','CG','I', '2')
    ('1336','C1','2014-03-21','C1','I','10') -- Why this row desn't be included in the result?
    ('1336','CF','2014-03-21','FF','D', '5')
    ('1336','CG','2014-03-21','CG','D', '5')
    ('1444','BB','2012-09-14','BB','D','10') -- Why this row doesn't be included in the result?
    ('1444','BB','2012-09-21','BB','I','10') -- And, why this row was took?
    ('1444','BB','2013-11-28','BB','I','10')
    ('1444','BB','2013-12-01','CC','I','10')
    ('1444','BB','2014-03-26','BB','I','10')
    ('1444','BB','2014-03-27','DD','I','10')
    ('1555','XX','2013-03-27','XX','I','10')
    ('1555','XX','2013-06-27','XX','I','10')
    ('1555','XX','2013-12-12','CC','I','10') -- Why this row doesn't be included in the result?
    ('1555','YY','2013-12-25','YY','D','10')
    ('1555','XX','2014-03-11','XX','I','10')
    ('1555','XX','2014-09-11','XX','I','10')
    ('1555','XX','2014-09-30','XX','D','10')
    ('1666','zz','2010-02-21','zz','I','10') -- Why this row doesn't be included in the result?
    ('1666','Z1','2010-03-01','Z1','D','10')
    ('1666','Z2','2015-05-31','Z2','D','10')

  13. #13
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    By the way,

    assuming the following (1), (2) and (3), I got an "Example 1".
    (1) the results are for every ID_NO and ID_NAME.
    (2) No summed ID_QNTY.
    (3) Include the rows marked in
    Code:
    WITH TEMP(ID_NO,ID_NAME,PUR_DATE,ID_ADD,ID_IND,ID_QNTY)
    VALUES
    ('1222','BB','2012-09-14','BB','I','10')
    ('1222','BB','2012-09-21','BB','I','10')
    ('1222','BB','2013-11-28','BB','I','10')
    ('1222','BB','2014-02-28','BB','I','10')
    ('1223','A1','2012-09-14','BB','D', '4') -- Why this row desn't be included in the result?
    ('1223','A2','2012-09-14','BB','D','10')
    ('1223','B1','2014-02-28','B1','I','10') -- The result took ID_NO  = 'B1'
    ('1223','B2','2014-02-28','B2','D','10') -- The result took ID_IND = 'D', ID_ADD = 'B2'
                                             -- And combined two rows into one result row.
    ('1234','AA','2009-09-12','AA','D','10')
    ('1234','AA','2009-09-30','AA','I','10')
    ('1234','AA','2013-03-01','AA','I','10')
    ('1234','AA','2013-03-28','AA','I','10')
    ('1234','AA','2013-12-25','AA','I','10')
    ('1234','AA','2013-12-29','AA','I','10')
    ('1234','AA','2014-03-30','AA','I','10')
    ('1234','AA','2015-06-01','AA','I','10')
    ('1234','AA','2016-03-28','AA','I','10')
    ('1234','AA','2020-12-25','AA','I','10')
    ('1333','BB','2013-03-21','BB','I', '6')
    ('1333','AA','2013-03-27','AA','I', '5')
    ('1333','BB','2013-03-27','BB','I', '5')
    ('1333','BB','2013-06-27','BB','I','10')
    ('1333','CC','2014-03-11','CC','I', '2')
    ('1333','CD','2014-03-11','CD','I', '2')
    ('1333','CE','2014-03-11','CE','I', '2')
    ('1333','CF','2014-03-11','CF','I', '2')
    ('1333','CG','2014-03-11','CG','I', '2')
    ('1336','C1','2014-03-21','C1','I','10') -- Why this row desn't be included in the result?
    ('1336','CF','2014-03-21','FF','D', '5')
    ('1336','CG','2014-03-21','CG','D', '5')
    ('1444','BB','2012-09-14','BB','D','10') -- Why this row doesn't be included in the result?
    ('1444','BB','2012-09-21','BB','I','10') -- And, why this row was took?
    ('1444','BB','2013-11-28','BB','I','10')
    ('1444','BB','2013-12-01','CC','I','10')
    ('1444','BB','2014-03-26','BB','I','10')
    ('1444','BB','2014-03-27','DD','I','10')
    ('1555','XX','2013-03-27','XX','I','10')
    ('1555','XX','2013-06-27','XX','I','10')
    ('1555','XX','2013-12-12','CC','I','10') -- Why this row doesn't be included in the result?
    ('1555','YY','2013-12-25','YY','D','10')
    ('1555','XX','2014-03-11','XX','I','10')
    ('1555','XX','2014-09-11','XX','I','10')
    ('1555','XX','2014-09-30','XX','D','10')
    ('1666','zz','2010-02-21','zz','I','10') -- Why this row doesn't be included in the result?
    ('1666','Z1','2010-03-01','Z1','D','10')
    ('1666','Z2','2015-05-31','Z2','D','10')


    Example 1: Tested on DB2 Express-C 9.7.5 for Windows.
    Note: I used lower cases for object names(e.g. column name, table name, aliase, so on...) for readability.
    (though, I know that using capial letters for all may be a custom in z/OS.)

    Test data: Same as you provided.
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     qty_pri_table
    ( id_no , id_name , pur_date , id_add , id_ind , id_qnty ) AS (
    VALUES
      ('1222','BB','2012-09-14','BB','I','10')
    , ('1222','BB','2012-09-21','BB','I','10')
    , ('1222','BB','2013-11-28','BB','I','10')
    , ('1222','BB','2014-02-28','BB','I','10')
    , ('1223','A1','2012-09-14','BB','D',' 4')
    , ('1223','A2','2012-09-14','BB','D','10')
    , ('1223','B1','2014-02-28','B1','I','10')
    , ('1223','B2','2014-02-28','B2','D','10')
    , ('1234','AA','2009-09-12','AA','D','10')
    , ('1234','AA','2009-09-30','AA','I','10')
    , ('1234','AA','2013-03-01','AA','I','10')
    , ('1234','AA','2013-03-28','AA','I','10')
    , ('1234','AA','2013-12-25','AA','I','10')
    , ('1234','AA','2013-12-29','AA','I','10')
    , ('1234','AA','2014-03-30','AA','I','10')
    , ('1234','AA','2015-06-01','AA','I','10')
    , ('1234','AA','2016-03-28','AA','I','10')
    , ('1234','AA','2020-12-25','AA','I','10')
    , ('1333','BB','2013-03-21','BB','I',' 6')
    , ('1333','AA','2013-03-27','AA','I',' 5')
    , ('1333','BB','2013-03-27','BB','I',' 5')
    , ('1333','BB','2013-06-27','BB','I','10')
    , ('1333','CC','2014-03-11','CC','I',' 2')
    , ('1333','CD','2014-03-11','CD','I',' 2')
    , ('1333','CE','2014-03-11','CE','I',' 2')
    , ('1333','CF','2014-03-11','CF','I',' 2')
    , ('1333','CG','2014-03-11','CG','I',' 2')
    , ('1336','C1','2014-03-21','C1','I','10')
    , ('1336','CF','2014-03-21','FF','D',' 5')
    , ('1336','CG','2014-03-21','CG','D',' 5')
    , ('1444','BB','2012-09-14','BB','D','10')
    , ('1444','BB','2012-09-21','BB','I','10')
    , ('1444','BB','2013-11-28','BB','I','10')
    , ('1444','BB','2013-12-01','CC','I','10')
    , ('1444','BB','2014-03-26','BB','I','10')
    , ('1444','BB','2014-03-27','DD','I','10')
    , ('1555','XX','2013-03-27','XX','I','10')
    , ('1555','XX','2013-06-27','XX','I','10')
    , ('1555','XX','2013-12-12','CC','I','10')
    , ('1555','YY','2013-12-25','YY','D','10')
    , ('1555','XX','2014-03-11','XX','I','10')
    , ('1555','XX','2014-09-11','XX','I','10')
    , ('1555','XX','2014-09-30','XX','D','10')
    , ('1666','zz','2010-02-21','zz','I','10')
    , ('1666','Z1','2010-03-01','Z1','D','10')
    , ('1666','Z2','2015-05-31','Z2','D','10')
    )
    query:
    Code:
    SELECT id_no , id_name , pur_date , id_add , id_ind , id_qnty
     FROM  (SELECT r.*
                 , ROW_NUMBER()
                      OVER( PARTITION BY id_no , id_name
                                ORDER BY past_pur_date DESC NULLS LAST
                          ) AS rn_past_pur_date
             FROM  (SELECT q.*
                         , RANK()
                              OVER( PARTITION BY id_no , id_name
                                               , YEAR   (pur_date)
                                               , QUARTER(pur_date)
                                        ORDER BY id_ind   ASC
                                               , pur_date DESC
                                   ) AS rank_in_year_quarer
                         , CASE
                           WHEN YEAR(pur_date)    < YEAR(current date) - 1
                            OR  YEAR(pur_date)    = YEAR(current date) - 1
                            AND QUARTER(pur_date) = 1                      THEN
                                pur_date
                           END  AS past_pur_date
                     FROM  qty_pri_table AS q
                     WHERE YEAR(pur_date)    <= YEAR(current date) + 3
                   ) AS r
             WHERE rank_in_year_quarer = 1
           )
     WHERE YEAR(pur_date)   >= YEAR(current date) - 1
      OR   YEAR(pur_date)   <  YEAR(current date) - 1
      AND  rn_past_pur_date =  1
      AND  past_pur_date    IS NOT NULL
     ORDER BY
           id_no , id_name , pur_date
    ;
    Result:
    Code:
    ------------------------------------------------------------------------------
    
    ID_NO ID_NAME PUR_DATE   ID_ADD ID_IND ID_QNTY
    ----- ------- ---------- ------ ------ -------
    1222  BB      2012-09-21 BB     I      10     
    1222  BB      2013-11-28 BB     I      10     
    1222  BB      2014-02-28 BB     I      10     
    1223  A1      2012-09-14 BB     D       4     
    1223  A2      2012-09-14 BB     D      10     
    1223  B1      2014-02-28 B1     I      10     
    1223  B2      2014-02-28 B2     D      10     
    1234  AA      2013-03-28 AA     I      10     
    1234  AA      2013-12-29 AA     I      10     
    1234  AA      2014-03-30 AA     I      10     
    1234  AA      2015-06-01 AA     I      10     
    1234  AA      2016-03-28 AA     I      10     
    1333  AA      2013-03-27 AA     I       5     
    1333  BB      2013-03-27 BB     I       5     
    1333  BB      2013-06-27 BB     I      10     
    1333  CC      2014-03-11 CC     I       2     
    1333  CD      2014-03-11 CD     I       2     
    1333  CE      2014-03-11 CE     I       2     
    1333  CF      2014-03-11 CF     I       2     
    1333  CG      2014-03-11 CG     I       2     
    1336  C1      2014-03-21 C1     I      10     
    1336  CF      2014-03-21 FF     D       5     
    1336  CG      2014-03-21 CG     D       5     
    1444  BB      2012-09-14 BB     D      10     
    1444  BB      2013-12-01 CC     I      10     
    1444  BB      2014-03-27 DD     I      10     
    1555  XX      2013-03-27 XX     I      10     
    1555  XX      2013-06-27 XX     I      10     
    1555  XX      2013-12-12 CC     I      10     
    1555  XX      2014-03-11 XX     I      10     
    1555  XX      2014-09-30 XX     D      10     
    1555  YY      2013-12-25 YY     D      10     
    1666  Z1      2010-03-01 Z1     D      10     
    1666  Z2      2015-05-31 Z2     D      10     
    1666  zz      2010-02-21 zz     I      10     
    
      35 record(s) selected.
    Last edited by tonkuma; 04-26-14 at 14:44. Reason: Add "Tested on DB2 Express-C 9.7.5 for Windows."

  14. #14
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    More to say,

    I couldn't understand why your descriptions of requirements include apparent/simple inconsistencies like ...

    Quote Originally Posted by Billa007 View Post
    Qty_pri_table
    Code:
    ID_NO    ID_NAME  PUR_DATE   ID_ADD  ID_IND  ID_QNTY
    
    ...
    
    1223	A1	2012-09-14    BB        D       4   
    1223	A2	2012-09-14    BB        D      10-->Need to fetch,latest date in 3rd Qtr in 2012 and D row   
    1223	B1	2014-02-28    B1        I      10 
    1223	B1	2014-02-28    B2        D      10-->Need to fetch,latest date in 1st Qtr in 2014 and D row
    
    ...
    and
    Quote Originally Posted by Billa007 View Post
    Code:
    WITH TEMP(ID_NO,ID_NAME,PUR_DATE,ID_ADD,ID_IND,ID_QNTY)
    VALUES
    ...
    ('1223','A1','2012-09-14','BB','D','4')   
    ('1223','A2','2012-09-14','BB','D','10')
    ('1223','B1','2014-02-28','B1','I','10')
    ('1223','B2','2014-02-28','B2','D','10')
    ...
    If you copied (and pasted to another) one of the sample data(i.e. "Qty_pri_table ... " or "WITH TEMP(...) ...") then edit it,
    No such inconsistencies might be included in your descriptions.

  15. #15
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I removed this because of double posts.

Posting Permissions

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