I am using:
Oracle SQL Developer (3.0.04)
Build MAin-04.34
Oracle Database 11g
Enterprise Edition 11.2.0.1.0 - 64bit Production
I would like to identify the Lead and Lags based on two groups.
The groupping is that multiple C_ID can be found in a Single W_ID
and that multiple D_VAL can be found in C_ID
So I would like to identify and mark with "Lead" and "Lag" related to the "consecutivedaysc" (this already matches the D_VAL and C_ID very well) for example W_ID 2004 has C_ID 2059 with D_VAL of 44 for April 2 & 3, the consecutive days are 2 and I would like to correctly mark April 2 as the Lead and April 3 as the lag.
Then I would like to mark the "Lead" and "Lag" independent of if there are multiple D_VAL on the same W_ID
Example that I am having trouble with:
W_ID 2285 on April 10 for C_ID 7847, I don't understand whay I can't get "Lag" in stead of a "Lead" realted to the consecutivedaysw
I would like to eventually have it that the data gets summarized based on W_ID and sinlge (non-repeating) dt with Lead and Lags.
table
with t as (
select 4592 U_KEY,0 D_VAL_PRESENT,2004 W_ID,to_date('4/1/2013','mm-dd-yyyy') dt,2059 C_ID, (null) D_VAL,0 GWID,13 GCID,1 CONSECUTIVEDAYSC,1 CONSECUTIVEDAYSW from dual union all
select 4591,1,2004,to_date('4/2/2013','mm-dd-yyyy'),2059,44,1,11,2,13 from dual union all
select 4590,1,2004,to_date('4/3/2013','mm-dd-yyyy'),2059,44,1,11,2,13 from dual union all
select 4589,1,2004,to_date('4/4/2013','mm-dd-yyyy'),2059,389,1,0,11,13 from dual union all
select 4588,1,2004,to_date('4/5/2013','mm-dd-yyyy'),2059,389,1,0,11,13 from dual union all
select 4587,1,2004,to_date('4/6/2013','mm-dd-yyyy'),2059,389,1,0,11,13 from dual union all
select 4586,1,2004,to_date('4/7/2013','mm-dd-yyyy'),2059,389,1,0,11,13 from dual union all
select 4585,1,2004,to_date('4/8/2013','mm-dd-yyyy'),2059,389,1,0,11,13 from dual union all
select 4584,1,2004,to_date('4/9/2013','mm-dd-yyyy'),2059,389,1,0,11,13 from dual union all
select 4583,1,2004,to_date('4/10/2013','mm-dd-yyyy'),2059,389,1,0,11,13 from dual union all
select 4582,1,2004,to_date('4/11/2013','mm-dd-yyyy'),2059,389,1,0,11,13 from dual union all
select 4581,1,2004,to_date('4/12/2013','mm-dd-yyyy'),2059,389,1,0,11,13 from dual union all
select 4580,1,2004,to_date('4/13/2013','mm-dd-yyyy'),2059,389,1,0,11,13 from dual union all
select 4579,1,2004,to_date('4/14/2013','mm-dd-yyyy'),2059,389,1,0,11,13 from dual union all
select 1092,0,2686,to_date('4/1/2013','mm-dd-yyyy'),7210,(null),0,11,3,3 from dual union all
select 3416,0,2686,to_date('4/1/2013','mm-dd-yyyy'),7211,(null),0,11,3,3 from dual union all
select 18118,0,2686,to_date('4/1/2013','mm-dd-yyyy'),17391,(null),0,11,3,3 from dual union all
select 1091,0,2686,to_date('4/2/2013','mm-dd-yyyy'),7210,(null),0,11,3,3 from dual union all
select 3415,0,2686,to_date('4/2/2013','mm-dd-yyyy'),7211,(null),0,11,3,3 from dual union all
select 18117,0,2686,to_date('4/2/2013','mm-dd-yyyy'),17391,(null),0,11,3,3 from dual union all
select 1090,0,2686,to_date('4/3/2013','mm-dd-yyyy'),7210,(null),0,11,3,3 from dual union all
select 3414,0,2686,to_date('4/3/2013','mm-dd-yyyy'),7211,(null),0,11,3,3 from dual union all
select 18116,0,2686,to_date('4/3/2013','mm-dd-yyyy'),17391,(null),0,11,3,3 from dual union all
select 1089,1,2686,to_date('4/4/2013','mm-dd-yyyy'),7210,51,9,0,11,11 from dual union all
select 3413,1,2686,to_date('4/4/2013','mm-dd-yyyy'),7211,51,9,0,11,11 from dual union all
select 18115,1,2686,to_date('4/4/2013','mm-dd-yyyy'),17391,51,9,0,11,11 from dual union all
select 1088,1,2686,to_date('4/5/2013','mm-dd-yyyy'),7210,51,9,0,11,11 from dual union all
select 3412,1,2686,to_date('4/5/2013','mm-dd-yyyy'),7211,51,9,0,11,11 from dual union all
select 18114,1,2686,to_date('4/5/2013','mm-dd-yyyy'),17391,51,9,0,11,11 from dual union all
select 1087,1,2686,to_date('4/6/2013','mm-dd-yyyy'),7210,51,9,0,11,11 from dual union all
select 3411,1,2686,to_date('4/6/2013','mm-dd-yyyy'),7211,51,9,0,11,11 from dual union all
select 18113,1,2686,to_date('4/6/2013','mm-dd-yyyy'),17391,51,9,0,11,11 from dual union all
select 1086,1,2686,to_date('4/7/2013','mm-dd-yyyy'),7210,51,9,0,11,11 from dual union all
select 3410,1,2686,to_date('4/7/2013','mm-dd-yyyy'),7211,51,9,0,11,11 from dual union all
select 18112,1,2686,to_date('4/7/2013','mm-dd-yyyy'),17391,51,9,0,11,11 from dual union all
select 1085,1,2686,to_date('4/8/2013','mm-dd-yyyy'),7210,51,9,0,11,11 from dual union all
select 3409,1,2686,to_date('4/8/2013','mm-dd-yyyy'),7211,51,9,0,11,11 from dual union all
select 18111,1,2686,to_date('4/8/2013','mm-dd-yyyy'),17391,51,9,0,11,11 from dual union all
select 1084,1,2686,to_date('4/9/2013','mm-dd-yyyy'),7210,51,9,0,11,11 from dual union all
select 3408,1,2686,to_date('4/9/2013','mm-dd-yyyy'),7211,51,9,0,11,11 from dual union all
select 18110,1,2686,to_date('4/9/2013','mm-dd-yyyy'),17391,51,9,0,11,11 from dual union all
select 1083,1,2686,to_date('4/10/2013','mm-dd-yyyy'),7210,51,9,0,11,11 from dual union all
select 3407,1,2686,to_date('4/10/2013','mm-dd-yyyy'),7211,51,9,0,11,11 from dual union all
select 18109,1,2686,to_date('4/10/2013','mm-dd-yyyy'),17391,51,9,0,11,11 from dual union all
select 1082,1,2686,to_date('4/11/2013','mm-dd-yyyy'),7210,51,9,0,11,11 from dual union all
select 3406,1,2686,to_date('4/11/2013','mm-dd-yyyy'),7211,51,9,0,11,11 from dual union all
select 18108,1,2686,to_date('4/11/2013','mm-dd-yyyy'),17391,51,9,0,11,11 from dual union all
select 1081,1,2686,to_date('4/12/2013','mm-dd-yyyy'),7210,51,9,0,11,11 from dual union all
select 3405,1,2686,to_date('4/12/2013','mm-dd-yyyy'),7211,51,9,0,11,11 from dual union all
select 18107,1,2686,to_date('4/12/2013','mm-dd-yyyy'),17391,51,9,0,11,11 from dual union all
select 1080,1,2686,to_date('4/13/2013','mm-dd-yyyy'),7210,51,9,0,11,11 from dual union all
select 3404,1,2686,to_date('4/13/2013','mm-dd-yyyy'),7211,51,9,0,11,11 from dual union all
select 18106,1,2686,to_date('4/13/2013','mm-dd-yyyy'),17391,51,9,0,11,11 from dual union all
select 1079,1,2686,to_date('4/14/2013','mm-dd-yyyy'),7210,51,9,0,11,11 from dual union all
select 3403,1,2686,to_date('4/14/2013','mm-dd-yyyy'),7211,51,9,0,11,11 from dual union all
select 18105,1,2686,to_date('4/14/2013','mm-dd-yyyy'),17391,51,9,0,11,11 from dual union all
select 17390,1,3034,to_date('4/1/2013','mm-dd-yyyy'),5395,298,0,0,14,14 from dual union all
select 17389,1,3034,to_date('4/2/2013','mm-dd-yyyy'),5395,298,0,0,14,14 from dual union all
select 17388,1,3034,to_date('4/3/2013','mm-dd-yyyy'),5395,298,0,0,14,14 from dual union all
select 17387,1,3034,to_date('4/4/2013','mm-dd-yyyy'),5395,298,0,0,14,14 from dual union all
select 17386,1,3034,to_date('4/5/2013','mm-dd-yyyy'),5395,298,0,0,14,14 from dual union all
select 7305,1,3034,to_date('4/6/2013','mm-dd-yyyy'),5394,44,0,0,7,14 from dual union all
select 17385,1,3034,to_date('4/6/2013','mm-dd-yyyy'),5395,298,0,0,14,14 from dual union all
select 14123,1,3034,to_date('4/6/2013','mm-dd-yyyy'),22421,44,0,0,7,14 from dual union all
select 17384,1,3034,to_date('4/7/2013','mm-dd-yyyy'),5395,298,0,0,14,14 from dual union all
select 17383,1,3034,to_date('4/8/2013','mm-dd-yyyy'),5395,298,0,0,14,14 from dual union all
select 7302,1,3034,to_date('4/9/2013','mm-dd-yyyy'),5394,44,0,0,7,14 from dual union all
select 17382,1,3034,to_date('4/9/2013','mm-dd-yyyy'),5395,298,0,0,14,14 from dual union all
select 14120,1,3034,to_date('4/9/2013','mm-dd-yyyy'),22421,44,0,0,7,14 from dual union all
select 7301,1,3034,to_date('4/10/2013','mm-dd-yyyy'),5394,44,0,0,7,14 from dual union all
select 17381,1,3034,to_date('4/10/2013','mm-dd-yyyy'),5395,298,0,0,14,14 from dual union all
select 14119,1,3034,to_date('4/10/2013','mm-dd-yyyy'),22421,44,0,0,7,14 from dual union all
select 7300,1,3034,to_date('4/11/2013','mm-dd-yyyy'),5394,44,0,0,7,14 from dual union all
select 17380,1,3034,to_date('4/11/2013','mm-dd-yyyy'),5395,298,0,0,14,14 from dual union all
select 14118,1,3034,to_date('4/11/2013','mm-dd-yyyy'),22421,44,0,0,7,14 from dual union all
select 7299,1,3034,to_date('4/12/2013','mm-dd-yyyy'),5394,44,0,0,7,14 from dual union all
select 17379,1,3034,to_date('4/12/2013','mm-dd-yyyy'),5395,298,0,0,14,14 from dual union all
select 14117,1,3034,to_date('4/12/2013','mm-dd-yyyy'),22421,44,0,0,7,14 from dual union all
select 7298,1,3034,to_date('4/13/2013','mm-dd-yyyy'),5394,44,0,0,7,14 from dual union all
select 17378,1,3034,to_date('4/13/2013','mm-dd-yyyy'),5395,298,0,0,14,14 from dual union all
select 14116,1,3034,to_date('4/13/2013','mm-dd-yyyy'),22421,44,0,0,7,14 from dual union all
select 7297,1,3034,to_date('4/14/2013','mm-dd-yyyy'),5394,44,0,0,7,14 from dual union all
select 17377,1,3034,to_date('4/14/2013','mm-dd-yyyy'),5395,298,0,0,14,14 from dual union all
select 14115,1,3034,to_date('4/14/2013','mm-dd-yyyy'),22421,44,0,0,7,14 from dual
)
the script that I am using:
select
t.*/
case
when lag(dt) over(partition by c_id, d_val order by dt, u_key)+1 = dt
then'Lag'
when lead(dt) over(partition by c_id, d_val order by dt, u_key)-1 = dt
then 'Lead_1'
when consecutivedaysc = 1
then'Lead_3'
else 'wrong'
end LeadLagD_VAL,
case
when lag(dt) over(partition by w_id, c_id, d_val_present,gwid order by dt)+1 = dt
then'Lag'
when lead(dt) over(partition by w_id, c_id, d_val_present, gwid order by dt)-1 = dt
then 'Lead_A'
when consecutivedaysw = 1
then 'Lead_B'
else 'wrong'
end Lead_Lag2
from t
order by
W_ID,
dt asc,
C_ID asc
;
the results should look like this (but haveing issues)
u_key D_VAL_PRESENT W_ID C_ID DT D_VAL GWID GCID CONSECUTIVEDAYSC CONSECUTIVEDAYSW LEADLAGD_VAL LEAD_LAG2
4592 0 2004 2059 01-APR-13 0 13 1 1 Lead_1 Lead_A
4591 1 2004 2059 02-APR-13 44 1 11 2 13 Lead_1 Lead_A
4590 1 2004 2059 03-APR-13 44 1 11 2 13 Lag Lag
4589 1 2004 2059 04-APR-13 389 1 0 11 13 Lead_1 Lag
4588 1 2004 2059 05-APR-13 389 1 0 11 13 Lag Lag
4587 1 2004 2059 06-APR-13 389 1 0 11 13 Lag Lag
4586 1 2004 2059 07-APR-13 389 1 0 11 13 Lag Lag
4585 1 2004 2059 08-APR-13 389 1 0 11 13 Lag Lag
4584 1 2004 2059 09-APR-13 389 1 0 11 13 Lag Lag
4583 1 2004 2059 10-APR-13 389 1 0 11 13 Lag Lag
4582 1 2004 2059 11-APR-13 389 1 0 11 13 Lag Lag
4581 1 2004 2059 12-APR-13 389 1 0 11 13 Lag Lag
4580 1 2004 2059 13-APR-13 389 1 0 11 13 Lag Lag
4579 1 2004 2059 14-APR-13 389 1 0 11 13 Lag Lag
1092 0 2686 7210 01-APR-13 0 11 3 3 Lead_1 Lead_A
3416 0 2686 7211 01-APR-13 0 11 3 3 Lead_1 Lead_A
18118 0 2686 17391 01-APR-13 0 11 3 3 Lead_1 Lead_A
1091 0 2686 7210 02-APR-13 0 11 3 3 Lag Lag
3415 0 2686 7211 02-APR-13 0 11 3 3 Lag Lag
18117 0 2686 17391 02-APR-13 0 11 3 3 Lag Lag
1090 0 2686 7210 03-APR-13 0 11 3 3 Lag Lag
3414 0 2686 7211 03-APR-13 0 11 3 3 Lag Lag
18116 0 2686 17391 03-APR-13 0 11 3 3 Lag Lag
1089 1 2686 7210 04-APR-13 51 9 0 11 11 Lead_1 Lead_A
3413 1 2686 7211 04-APR-13 51 9 0 11 11 Lead_1 Lead_A
18115 1 2686 17391 04-APR-13 51 9 0 11 11 Lead_1 Lead_A
1088 1 2686 7210 05-APR-13 51 9 0 11 11 Lag Lag
3412 1 2686 7211 05-APR-13 51 9 0 11 11 Lag Lag
18114 1 2686 17391 05-APR-13 51 9 0 11 11 Lag Lag
1087 1 2686 7210 06-APR-13 51 9 0 11 11 Lag Lag
3411 1 2686 7211 06-APR-13 51 9 0 11 11 Lag Lag
18113 1 2686 17391 06-APR-13 51 9 0 11 11 Lag Lag
1086 1 2686 7210 07-APR-13 51 9 0 11 11 Lag Lag
3410 1 2686 7211 07-APR-13 51 9 0 11 11 Lag Lag
18112 1 2686 17391 07-APR-13 51 9 0 11 11 Lag Lag
1085 1 2686 7210 08-APR-13 51 9 0 11 11 Lag Lag
3409 1 2686 7211 08-APR-13 51 9 0 11 11 Lag Lag
18111 1 2686 17391 08-APR-13 51 9 0 11 11 Lag Lag
1084 1 2686 7210 09-APR-13 51 9 0 11 11 Lag Lag
3408 1 2686 7211 09-APR-13 51 9 0 11 11 Lag Lag
18110 1 2686 17391 09-APR-13 51 9 0 11 11 Lag Lag
1083 1 2686 7210 10-APR-13 51 9 0 11 11 Lag Lag
3407 1 2686 7211 10-APR-13 51 9 0 11 11 Lag Lag
18109 1 2686 17391 10-APR-13 51 9 0 11 11 Lag Lag
1082 1 2686 7210 11-APR-13 51 9 0 11 11 Lag Lag
3406 1 2686 7211 11-APR-13 51 9 0 11 11 Lag Lag
18108 1 2686 17391 11-APR-13 51 9 0 11 11 Lag Lag
1081 1 2686 7210 12-APR-13 51 9 0 11 11 Lag Lag
3405 1 2686 7211 12-APR-13 51 9 0 11 11 Lag Lag
18107 1 2686 17391 12-APR-13 51 9 0 11 11 Lag Lag
1080 1 2686 7210 13-APR-13 51 9 0 11 11 Lag Lag
3404 1 2686 7211 13-APR-13 51 9 0 11 11 Lag Lag
18106 1 2686 17391 13-APR-13 51 9 0 11 11 Lag Lag
1079 1 2686 7210 14-APR-13 51 9 0 11 11 Lag Lag
3403 1 2686 7211 14-APR-13 51 9 0 11 11 Lag Lag
18105 1 2686 17391 14-APR-13 51 9 0 11 11 Lag Lag
17390 1 3034 5395 01-APR-13 298 0 0 14 14 Lead_1 Lead_A
17389 1 3034 5395 02-APR-13 298 0 0 14 14 Lag Lag
17388 1 3034 5395 03-APR-13 298 0 0 14 14 Lag Lag
17387 1 3034 5395 04-APR-13 298 0 0 14 14 Lag Lag
17386 1 3034 5395 05-APR-13 298 0 0 14 14 Lag Lag
7305 1 3034 5394 06-APR-13 44 0 0 7 14 Lead_1 Lag
17385 1 3034 5395 06-APR-13 298 0 0 14 14 Lag Lag
14123 1 3034 22421 06-APR-13 44 0 0 7 14 Lead_1 Lag
17384 1 3034 5395 07-APR-13 298 0 0 14 14 Lag Lag
17383 1 3034 5395 08-APR-13 298 0 0 14 14 Lag Lag
7302 1 3034 5394 09-APR-13 44 0 0 7 14 Lead_1 Lag
17382 1 3034 5395 09-APR-13 298 0 0 14 14 Lag Lag
14120 1 3034 22421 09-APR-13 44 0 0 7 14 Lead_1 Lag
7301 1 3034 5394 10-APR-13 44 0 0 7 14 Lag Lag
17381 1 3034 5395 10-APR-13 298 0 0 14 14 Lag Lag
14119 1 3034 22421 10-APR-13 44 0 0 7 14 Lag Lag
7300 1 3034 5394 11-APR-13 44 0 0 7 14 Lag Lag
17380 1 3034 5395 11-APR-13 298 0 0 14 14 Lag Lag
14118 1 3034 22421 11-APR-13 44 0 0 7 14 Lag Lag
7299 1 3034 5394 12-APR-13 44 0 0 7 14 Lag Lag
17379 1 3034 5395 12-APR-13 298 0 0 14 14 Lag Lag
14117 1 3034 22421 12-APR-13 44 0 0 7 14 Lag Lag
7298 1 3034 5394 13-APR-13 44 0 0 7 14 Lag Lag
17378 1 3034 5395 13-APR-13 298 0 0 14 14 Lag Lag
14116 1 3034 22421 13-APR-13 44 0 0 7 14 Lag Lag
7297 1 3034 5394 14-APR-13 44 0 0 7 14 Lag Lag
17377 1 3034 5395 14-APR-13 298 0 0 14 14 Lag Lag
14115 1 3034 22421 14-APR-13 44 0 0 7 14 Lag Lag
I place the "wrong" showing that neither the when conditions were no able to work.
any suggestions on a better direction for me to solve this?
Edited by: 1004407 on May 23, 2013 1:16 PM
Then I am trying to get this, not to include C_ID
u_key D_VAL_PRESENT W_ID DT CONSECUTIVEDAYSW LEAD_LAG2
4592 0 2004 01-APR-13 1 Lead_A
4591 1 2004 02-APR-13 13 Lead_A
4590 1 2004 03-APR-13 13 Lag
4589 1 2004 04-APR-13 13 Lag
4588 1 2004 05-APR-13 13 Lag
4587 1 2004 06-APR-13 13 Lag
4586 1 2004 07-APR-13 13 Lag
4585 1 2004 08-APR-13 13 Lag
4584 1 2004 09-APR-13 13 Lag
4583 1 2004 10-APR-13 13 Lag
4582 1 2004 11-APR-13 13 Lag
4581 1 2004 12-APR-13 13 Lag
4580 1 2004 13-APR-13 13 Lag
4579 1 2004 14-APR-13 13 Lag
1092 0 2686 01-APR-13 3 Lead_A
1091 0 2686 02-APR-13 3 Lag
1090 0 2686 03-APR-13 3 Lag
1089 1 2686 04-APR-13 11 Lead_A
1088 1 2686 05-APR-13 11 Lag
1087 1 2686 06-APR-13 11 Lag
1086 1 2686 07-APR-13 11 Lag
1085 1 2686 08-APR-13 11 Lag
1084 1 2686 09-APR-13 11 Lag
1083 1 2686 10-APR-13 11 Lag
1082 1 2686 11-APR-13 11 Lag
1081 1 2686 12-APR-13 11 Lag
1080 1 2686 13-APR-13 11 Lag
1079 1 2686 14-APR-13 11 Lag
17390 1 3034 01-APR-13 14 Lead_A
17389 1 3034 02-APR-13 14 Lag
17388 1 3034 03-APR-13 14 Lag
17387 1 3034 04-APR-13 14 Lag
17386 1 3034 05-APR-13 14 Lag
7305 1 3034 06-APR-13 14 Lag
17384 1 3034 07-APR-13 14 Lag
17383 1 3034 08-APR-13 14 Lag
7302 1 3034 09-APR-13 14 Lag
7301 1 3034 10-APR-13 14 Lag
7300 1 3034 11-APR-13 14 Lag
7299 1 3034 12-APR-13 14 Lag
7298 1 3034 13-APR-13 14 Lag
7297 1 3034 14-APR-13 14 Lag
then into this (which I would use where Lead_Lag2 = "Lead_A"
u_key D_VAL_PRESENT W_ID DT CONSECUTIVEDAYSW LEAD_LAG2
4592 0 2004 01-APR-13 1 Lead_A
4591 1 2004 02-APR-13 13 Lead_A
1092 0 2686 01-APR-13 3 Lead_A
11089 1 2686 04-APR-13 11 Lead_A
17390 1 3034 01-APR-13 14 Lead_A
but onething at a time.
Thanks for point out the errors Frank, always helpful to know what others see.
Edited by: 1004407 on May 23, 2013 2:36 PM
Edited by: 1004407 on May 23, 2013 4:01 PM