Code: Select all
SELECT DISTINCT
T24.CALEN_YEAR
,T24.IN_LOC
,T24.PART_NO
,T24.PART_DES
,T16.EFF_DT
FROM PROC_TABLE T24
LEFT OUTER JOIN
DEV_TABLE T16
ON T16.IN_LOC = T24.IN_LOC
AND T16.PART_NO = T24.PART_NO
AND T16.PART_DES= T24.PART_DES
WHERE
T24.PART_NO ='PART1111'
AND T24.IN_LOC ='ARCANSA'
AND T24.PART_DES ='MATERIAL'
AND T24.CALEN_YEAR IN ('2011','2012','2013')
ORDER BY
T24.IN_LOC
,T24.PART_NO
,T24.PART_DES
,T16.EFF_DT
DESC
In the DEV_TABLE having T16.EFF_DT DATE (10) values such as 2005-01-01 to 2013-12-31
My query need to compare the value of EFF_DT year filed to CALEN_YEAR ,If the EFF_DT year
having 2011 and before means that record should display CALEN_YEAR value 2011 rows
If the EFF_DT year
having 2012 means that record should display CALEN_YEAR value 2012 rows
If the EFF_DT year
having 2013 means that record should display CALEN_YEAR value 2013 rows
We should do match with CALEN_YEAR value and EFF_DT year value
My query should return the values like
Code: Select all
CALEN_YEAR IN_LOC PART_NO PART_DES EFF_DT
2011 ARCANSA PART1111 MATERIAL 28-02-2008
2011 ARCANSA PART1111 MATERIAL 28-02-2009
2011 ARCANSA PART1111 MATERIAL 28-02-2010
2011 ARCANSA PART1111 MATERIAL 28-02-2011
2012 ARCANSA PART1111 MATERIAL 12-12-2012
2012 ARCANSA PART1111 MATERIAL 31-12-2012
2013 ARCANSA PART1111 MATERIAL 01-04-2013
2013 ARCANSA PART1111 MATERIAL 11-09-2013