All Forums Database
Gowtham 16 posts Joined 12/12
03 Apr 2013
find the min & max value for the below query

I need your help to find the result for the below table

pil1              pil2         timp     Mdat              inti
3Week    3Month    1Year    9/27/2013        40
1Week    1Month    1Year    9/27/2013        40
1Week    1Month    6Month    3/27/2013        42
1Week    1Month    2Week    10/11/2012    44
1Week    1Month    9Month    6/27/2013        45
1Week    1Month    1Month    10/27/2012    46
1Week    1Month    Overnight    9/27/2012      47
1Week    1Month    3Month    12/27/2012    43
1Week    1Month    1Week    10/4/2012        41
1Week    8Month    1Year    9/27/2013        40

 
expected result is

 
pil1           pil2           timp         Mdat            inti
1Week    1Month    1Week    10/4/2012        41
1Week    1Month    1Month    10/27/2012    46
3Week    3Month    2Week    10/11/2012    44
3Week    3Month    3Month    12/27/2012    43
1Week    8Month    1Week    10/4/2012        41
1Week    8Month    1Year    9/27/2013        40

 
compare pil1 with timp,
if it is matched then put its mdat and inti value
if not matched then find the minimum value for that, for instance 2Week is smaller than 3week
so put 2week's mdat and inti values.
 
compare pil2 with timp,
if it is matched then put its mdat and inti value
if not matched then find the Maximum value for that, for instance 1year is higher than 8month
so put 1year's mdat and inti values.

 

Tags:
dnoeth 3247 posts Joined 11/04
03 Apr 2013

Sorry, but your explanation is quite confusing.
Could you provide DDL plus INSERTs and for each row: why or why not it's in the result.
Dieter

Dieter

Gowtham 16 posts Joined 12/12
03 Apr 2013

CREATE SET TABLE RETAIL.tab ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
pil1 VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,
pil2 VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,
timp VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,
mdat DATE FORMAT 'YYYY-MM-DD',
inti INTEGER)
PRIMARY INDEX ( pil1 );

ins tab('3Week','3Month','1Year',1130927,40);
ins tab('1Week','1Month','1Year',1130927,40);
ins tab('1Week','1Month','6Month',1130327,42);
ins tab('1Week','1Month','2Week',1121011,44);
ins tab('1Week','1Month','9Month',1130627,45);
ins tab('1Week','1Month','1Month',1121027,46);
ins tab('1Week','1Month','Overnight',1120927,47);
ins tab('1Week','1Month','3Month',1121227,43);
ins tab('1Week','1Month','1Week',1121004,41);
ins tab('1Week','8Month','1Year',1130927,40);

run the below query

sel 1 sno, A.pil1,A.pil2, B.timp,B.mdat,B.inti from tab A left join tab B
on A.pil1=B.timp group by 1,2,3,4,5,6
union all
sel 2, A.pil1,A.pil2, C.timp,C.mdat,C.inti from tab A left join tab C
on A.pil2=C.timp group by 1,2,3,4,5,6

result is

1 1Week 1Month 1Week 10/4/2012 41
1 1Week 8Month 1Week 10/4/2012 41
1 3Week 3Month ? ? ?
2 1Week 8Month ? ? ?
2 3Week 3Month 3Month 12/27/2012 43
2 1Week 1Month 1Month 10/27/2012 46

for Sno 1, PIL1 column value is 3week, i want less than 3week from TIMP column, so the value is 2week.
for Sno 2, PIL1 column value is 8month, i want greater than 8month from TIMP column, so the value is 1year, see the fields bolded in my expected result.

dnoeth 3247 posts Joined 11/04
04 Apr 2013

When you actually have values like '8Month' and this is supposed to be less than '1Year' you should strongly consider changing your data model. i wouldn't want to write a parsing routine to define less/greater.
Dieter
 
 
 

Dieter

Gowtham 16 posts Joined 12/12
04 Apr 2013

dieter, there is no values like '8Month', here i have changed the datas and column names but the scenario is same. for pil1 i want the matched value, if not then i want the immediate lesser value as like this for pil2 i need the matched value, if not then want the immediate greater value. after i found all this, i wanna to subtract both the dates.

dnoeth 3247 posts Joined 11/04
04 Apr 2013

Ok, what is the actual data type?
Is it possible to compare '1Year' to '8Month'?
Is the query you posted anything you actually use?
Why is it so complicated to post some actual DDL and data?
Dieter

Dieter

Gowtham 16 posts Joined 12/12
04 Apr 2013

data type is VARCHAR(10).
i know, we cannot able to compare 1Year and 8Month but as per its order using CASE function we can define some interger values for that, then we can compare right.
yes am going to use this query.
am working for banking project, i dont have an access for this site.

You must sign in to leave a comment.