find the min & max value for the below query

Database
Highlighted
Enthusiast

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.






 


6 REPLIES
Supporter

Re: find the min & max value for the below query

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


Enthusiast

Re: find the min & max value for the below query

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.
Supporter

Re: find the min & max value for the below query

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


 


 


 


Enthusiast

Re: find the min & max value for the below query

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.


Supporter

Re: find the min & max value for the below query

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


Enthusiast

Re: find the min & max value for the below query

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.