# Thread: limit the rows based on the year

1. Registered User
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

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. Registered User
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. Registered User
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. Registered User
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```

5. Registered User
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. Registered User
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```

7. Registered User
Join Date
Jul 2009
Location
NY
Posts
963

## 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. Registered User
Join Date
Nov 2011
Posts
334
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. Registered User
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. Registered User
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. Registered User
Join Date
Feb 2008
Location
Japan
Posts
3,483
Originally Posted by Billa007
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)

...
...
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. Registered User
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. Registered User
Join Date
Jul 2009
Location
NY
Posts
963
Originally Posted by Billa007
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. Registered User
Join Date
Nov 2011
Posts
334
Originally Posted by Billa007
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```
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. Registered User
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
•