| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
12-28-11, 05:25
|
|
Registered User
|
|
Join Date: Sep 2011
Posts: 107
|
|
|
select query should fetch past date rows
|
|
Hi;
Please find the below select query.
The Past IN_DT records are comes under the 2011 year row..(if the 2011 year row have non-zero value in ITEM_NO in TABLE3,if not that rows are not fetched )
if the 2011 year row have the ZERo value ITEM_NO means that related 2011 and rows will not fetch from TABLE2
for that case,It should comes under the next year rows like 2012 (If 2012 row will have non-zero ITEM_NO)
Code:
ELECT
t1.CD_PLT
, t1.BASE_NAME
, t1.PRE_NAME
, t1.SUF_NAME
, t2.WKLY_CA
, p. per_year
, T3.ITEM_NO
, t2.IN_DT
FROM
(SELECT DISTINCT T3.C_YEAR
FROM TABLE3 T3
WHERE T3.C_YEAR >=CHAR(YEAR(current_date))
) p(per_year)
INNER JOIN
(SELECT DISTINCT
*
FROM table1
) t1
ON 0=0
INNER JOIN
(SELECT DISTINCT *
FROM TABLE4 T3
WHERE ITEM_NO <> 0 )T3
ON t3.CD_PLT = T1.CD_PLT
AND T3.PRE_NAME = T1.PRE_NAME
AND T3.BASE_NAME = T1.BASE_NAME
AND T3.SUF_NAME = T1.SUF_NAME
AND t3.C_year = p. per_year
LEFT OUTER JOIN
(SELECT t2.*
,MAX( YEAR(IN_DT) , YEAR(current_date) ) AS norm_year
FROM table2 t2
) t2
ON t1.CD_PLT = T2.CD_PLT
AND T1.PRE_NAME = T2.PRE_NAME
AND T1.BASE_NAME = T2.BASE_NAME
AND T1.SUF_NAME = T2.SUF_NAME
AND p. per_year=CHAR(T2.NORM_YEAR)
ORDER BY
CD_PLT
, BASE_NAME DESC
, SUF_NAME
, PRE_NAME
, per_year
;
TABLE1
Code:
CD_PLT BASE_NAME PRE_NAME SUF_NAME
----------- ------------ -------------- ----------
ABACC 6065 REWS AE
ABACC 6000 ERWV DE
ABADD 9011 WEST DR
TABLE2
Code:
CD_PLT BASE_NAME PRE_NAME SUF_NAME WKLY_CA IN_DT
----------- ------------ -------------- ---------- ------- ----------------
ABACC 6065 REWS AE 21 2009-01-13
ABACC 6065 REWS AE 21 2010-01-12
ABACC 6000 ERWV DE 26 2010-11-11 -->THIS ROW SHOULD COME IN THE RESULT SET
ABACC 6000 ERWV DE 46 2011-11-11 -->THIS ROW SHOULD COME IN THE RESULT SET
ABACC 6000 ERWV DE 56 2012-11-11
ABADD 9011 WEST DR 77 2011-03-01 -->THIS ROW SHOULD COME IN THE RESULT SET
TABLE3
Code:
CD_PLT BASE_NAME PRE_NAME SUF_NAME C_YEAR ITEM_NO
----------- ------------ -------------- ---------- ------------ -------
ABACC 6065 REWS AE 2010 200
ABACC 6065 REWS AE 2011 209
ABACC 6065 REWS AE 2012 310
ABACC 6065 REWS AE 2013 100
ABACC 6000 ERWV DE 2011 0
ABACC 6000 ERWV DE 2012 500
ABACC 6000 ERWV DE 2013 600
ABACC 6000 ERWV DE 2014 700
ABADD 9011 WEST DR 2011 0
ABADD 9011 WEST DR 2013 1200
CURRENT RESULT SET
Code:
CD_PLT BASE_NAME PRE_NAME SUF_NAME WKLY_CA PER_YEAR ITEM_NO IN_DT
----------- ------------ -------------- ---------- ------------ ------- ----------- --------------
ABACC 6065 REWS AE 21 2011 209 2009-01-13
ABACC 6065 REWS AE 21 2011 209 2010-01-12
ABACC 6065 REWS AE - 2012 310 -
ABACC 6065 REWS AE - 2013 100 -
ABACC 6000 ERWV DE 56 2012 500 2012-11-11
ABACC 6000 ERWV DE - 2013 600 -
ABACC 6000 ERWV DE - 2014 700 -
ABADD 9011 WEST DR 77 2013 1200 -
I am expecting the result set like below
EXPECTED RESULT SET
Code:
CD_PLT BASE_NAME PRE_NAME SUF_NAME WKLY_CA PER_YEAR ITEM_NO IN_DT
----------- ------------ -------------- ---------- ------------ ------- ----------- --------------
ABACC 6065 REWS AE 21 2011 209 2009-01-13
ABACC 6065 REWS AE - 2012 310 -
ABACC 6065 REWS AE - 2013 100 -
ABACC 6000 ERWV DE 26 2012 500 2010-11-11
ABACC 6000 ERWV DE 46 2012 500 2011-11-11
ABACC 6000 ERWV DE 56 2012 500 2012-11-11
ABACC 6000 ERWV DE - 2013 600 -
ABACC 6000 ERWV DE - 2014 700 -
ABADD 9011 WEST DR 77 2013 1200 2011-03-01
Please help.
|
|
12-28-11, 08:46
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
I couldn't find the reason to select a row in a group with (CD_PLT = ABACC and BASE_NAME = 6065).
TABLE2(adjust blanks for readability)
Code:
CD_PLT BASE_NAME PRE_NAME SUF_NAME WKLY_CA IN_DT
------ --------- -------- -------- ------- ----------
ABACC 6065 REWS AE 21 2009-01-13 --> Why took this row?
ABACC 6065 REWS AE 21 2010-01-12 --> Why didn't took this row?
|
Last edited by tonkuma; 12-28-11 at 08:51.
|
12-29-11, 02:34
|
|
Registered User
|
|
Join Date: Sep 2011
Posts: 107
|
|
|
|
Hi;
Apology for that..Typo mistake...that row should be in the result set..
Pl help..
|
|
12-29-11, 02:42
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
> that row
What row?
Do you want to say that both of rows(IN_DT = 2009-01-13 and 2010-01-12) should be in the result set?
|
|
12-29-11, 02:51
|
|
Registered User
|
|
Join Date: Sep 2011
Posts: 107
|
|
yes tonkuma..
Below is the Expected result set
Code:
CD_PLT BASE_NAME PRE_NAME SUF_NAME WKLY_CA PER_YEAR ITEM_NO IN_DT
----------- ------------ -------------- ---------- ------------ ------- ----------- --------------
ABACC 6065 REWS AE 21 2011 209 2009-01-13
ABACC 6065 REWS AE 21 2011 209 2010-01-12
ABACC 6065 REWS AE - 2012 310 -
ABACC 6065 REWS AE - 2013 100 -
ABACC 6000 ERWV DE 26 2012 500 2010-11-11
ABACC 6000 ERWV DE 46 2012 500 2011-11-11
ABACC 6000 ERWV DE 56 2012 500 2012-11-11
ABACC 6000 ERWV DE - 2013 600 -
ABACC 6000 ERWV DE - 2014 700 -
ABADD 9011 WEST DR 77 2013 1200 2011-03-01
Thanks;
|
|
12-29-11, 03:12
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
Looking into rows in table2 and table3,
columns PRE_NAME and SUF_NAME are dependent on pair of columns(CD_PLT, BASE_NAME).
The values of PRE_NAME and SUF_NAME are in table1.
So, table2 and table3 should not have columns PRE_NAME and SUF_NAME viewed from normalization.
I thought that you would have two options.
(1) remove columns PRE_NAME and SUF_NAME from table2 and table3.
Then join table1 to get the values of the columns.
(2) ignore table1 in this query.
Join table2 and table3, take values of PRE_NAME and SUF_NAME from table3.
join conditions "T2.PRE_NAME = T3.PRE_NAME" and "T2.SUF_NAME = T3.SUF_NAME" are not necessary.
Here is an example of option(1).
Example 1:
Code:
------------------------------ Commands Entered ------------------------------
WITH
TABLE1
( CD_PLT , BASE_NAME , PRE_NAME , SUF_NAME ) AS (
VALUES
( 'ABACC' , '6065' , 'REWS' , 'AE' )
, ( 'ABACC' , '6000' , 'ERWV' , 'DE' )
, ( 'ABADD' , '9011' , 'WEST' , 'DR' )
)
, TABLE2
( CD_PLT , BASE_NAME , WKLY_CA , IN_DT ) AS (
VALUES
( 'ABACC' , '6065' , 21 , '2009-01-13' )
, ( 'ABACC' , '6065' , 21 , '2010-01-12' )
, ( 'ABACC' , '6000' , 26 , '2010-11-11' )
, ( 'ABACC' , '6000' , 46 , '2011-11-11' )
, ( 'ABACC' , '6000' , 56 , '2012-11-11' )
, ( 'ABADD' , '9011' , 77 , '2011-03-01' )
)
, TABLE3
( CD_PLT , BASE_NAME , C_YEAR , ITEM_NO ) AS (
VALUES
( 'ABACC' , '6065' , '2010' , 200 )
, ( 'ABACC' , '6065' , '2011' , 209 )
, ( 'ABACC' , '6065' , '2012' , 310 )
, ( 'ABACC' , '6065' , '2013' , 100 )
, ( 'ABACC' , '6000' , '2011' , 0 )
, ( 'ABACC' , '6000' , '2012' , 500 )
, ( 'ABACC' , '6000' , '2013' , 600 )
, ( 'ABACC' , '6000' , '2014' , 700 )
, ( 'ABADD' , '9011' , '2011' , 0 )
, ( 'ABADD' , '9011' , '2013' , 1200 )
)
SELECT t3.CD_PLT
, t3.BASE_NAME
, t1.PRE_NAME
, t1.SUF_NAME
, t2.WKLY_CA
, t3.C_YEAR AS per_year
, t3.ITEM_NO
, t2.IN_DT
FROM (SELECT t3.*
, ROW_NUMBER()
OVER( PARTITION BY CD_PLT , BASE_NAME
ORDER BY C_YEAR
) AS rnum
FROM TABLE3 t3
WHERE t3.C_YEAR >= CHAR( YEAR(current_date) )
AND t3.ITEM_NO > 0
) t3
LEFT OUTER JOIN
TABLE2 t2
ON t2.CD_PLT = t3.CD_PLT
AND t2.BASE_NAME = t3.BASE_NAME
AND
( CHAR( YEAR(t2.IN_DT) ) = t3.C_YEAR
OR t3.rnum = 1
AND CHAR( YEAR(t2.IN_DT) ) < t3.C_YEAR
)
INNER JOIN
TABLE1 t1
ON t1.CD_PLT = t3.CD_PLT
AND t1.BASE_NAME = t3.BASE_NAME
ORDER BY
CD_PLT
, BASE_NAME DESC
, per_year
, IN_DT
;
------------------------------------------------------------------------------
CD_PLT BASE_NAME PRE_NAME SUF_NAME WKLY_CA PER_YEAR ITEM_NO IN_DT
------ --------- -------- -------- ----------- -------- ----------- ----------
ABACC 6065 REWS AE 21 2011 209 2009-01-13
ABACC 6065 REWS AE 21 2011 209 2010-01-12
ABACC 6065 REWS AE - 2012 310 -
ABACC 6065 REWS AE - 2013 100 -
ABACC 6000 ERWV DE 26 2012 500 2010-11-11
ABACC 6000 ERWV DE 46 2012 500 2011-11-11
ABACC 6000 ERWV DE 56 2012 500 2012-11-11
ABACC 6000 ERWV DE - 2013 600 -
ABACC 6000 ERWV DE - 2014 700 -
ABADD 9011 WEST DR 77 2013 1200 2011-03-01
10 record(s) selected.
|
Last edited by tonkuma; 12-29-11 at 03:24.
Reason: Remove from "table2 or" in option(2) and add cte TABLE1 to Example 1 and remove comments for TABLE1.
|
12-29-11, 03:34
|
|
Registered User
|
|
Join Date: Sep 2011
Posts: 107
|
|
Excellent...thanks for the reply..working fine..
|
|
12-29-11, 03:40
|
|
Registered User
|
|
Join Date: Sep 2011
Posts: 107
|
|
Hi;
we should not ignore the TABLE1...we must use in the joins...The result set should have unmatched rows also from Table 1 so that i was using Left outer join with Table2 in my code..
Thanks;
|
|
12-29-11, 03:53
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
There is no reason to use table1 from your published data.
You might have more other data of tables which would prove your insistence.
|
|
12-30-11, 06:22
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
Here is an example of option(2).
Table1 is not necessary.
Example 2:
Code:
------------------------------ Commands Entered ------------------------------
WITH
TABLE2
( CD_PLT , BASE_NAME , PRE_NAME , SUF_NAME , WKLY_CA , IN_DT ) AS (
VALUES
( 'ABACC' , '6065' , 'REWS' , 'AE' , 21 , '2009-01-13' )
, ( 'ABACC' , '6065' , 'REWS' , 'AE' , 21 , '2010-01-12' )
, ( 'ABACC' , '6000' , 'ERWV' , 'DE' , 26 , '2010-11-11' )
, ( 'ABACC' , '6000' , 'ERWV' , 'DE' , 46 , '2011-11-11' )
, ( 'ABACC' , '6000' , 'ERWV' , 'DE' , 56 , '2012-11-11' )
, ( 'ABADD' , '9011' , 'WEST' , 'DR' , 77 , '2011-03-01' )
)
, TABLE3
( CD_PLT , BASE_NAME , PRE_NAME , SUF_NAME , C_YEAR , ITEM_NO ) AS (
VALUES
( 'ABACC' , '6065' , 'REWS' , 'AE' , '2010' , 200 )
, ( 'ABACC' , '6065' , 'REWS' , 'AE' , '2011' , 209 )
, ( 'ABACC' , '6065' , 'REWS' , 'AE' , '2012' , 310 )
, ( 'ABACC' , '6065' , 'REWS' , 'AE' , '2013' , 100 )
, ( 'ABACC' , '6000' , 'ERWV' , 'DE' , '2011' , 0 )
, ( 'ABACC' , '6000' , 'ERWV' , 'DE' , '2012' , 500 )
, ( 'ABACC' , '6000' , 'ERWV' , 'DE' , '2013' , 600 )
, ( 'ABACC' , '6000' , 'ERWV' , 'DE' , '2014' , 700 )
, ( 'ABADD' , '9011' , 'WEST' , 'DR' , '2011' , 0 )
, ( 'ABADD' , '9011' , 'WEST' , 'DR' , '2013' , 1200 )
)
SELECT t3.CD_PLT
, t3.BASE_NAME
, t3.PRE_NAME
, t3.SUF_NAME
, t2.WKLY_CA
, t3.C_YEAR AS per_year
, t3.ITEM_NO
, t2.IN_DT
FROM (SELECT t3.*
, ROW_NUMBER()
OVER( PARTITION BY CD_PLT , BASE_NAME
ORDER BY C_YEAR
) AS rnum
FROM TABLE3 t3
WHERE t3.C_YEAR >= CHAR( YEAR(current_date) )
AND t3.ITEM_NO > 0
) t3
LEFT OUTER JOIN
TABLE2 t2
ON t2.CD_PLT = t3.CD_PLT
AND t2.BASE_NAME = t3.BASE_NAME
AND
( CHAR( YEAR(t2.IN_DT) ) = t3.C_YEAR
OR t3.rnum = 1
AND CHAR( YEAR(t2.IN_DT) ) < t3.C_YEAR
)
ORDER BY
CD_PLT
, BASE_NAME DESC
, per_year
;
------------------------------------------------------------------------------
CD_PLT BASE_NAME PRE_NAME SUF_NAME WKLY_CA PER_YEAR ITEM_NO IN_DT
------ --------- -------- -------- ----------- -------- ----------- ----------
ABACC 6065 REWS AE 21 2011 209 2009-01-13
ABACC 6065 REWS AE 21 2011 209 2010-01-12
ABACC 6065 REWS AE - 2012 310 -
ABACC 6065 REWS AE - 2013 100 -
ABACC 6000 ERWV DE 26 2012 500 2010-11-11
ABACC 6000 ERWV DE 46 2012 500 2011-11-11
ABACC 6000 ERWV DE 56 2012 500 2012-11-11
ABACC 6000 ERWV DE - 2013 600 -
ABACC 6000 ERWV DE - 2014 700 -
ABADD 9011 WEST DR 77 2013 1200 2011-03-01
10 record(s) selected.
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
select query should fetch past date rows
