|
View previous topic :: View next topic
|
| Author |
Message |
Ajay Kumar Bang
New User
Joined: 10 Feb 2023
Posts: 8
Location: Delhi
|
|
|
|
0
I have the following table1:
ID_BOOK|MONTH_BOOK|QUALITY_BOOK|DPD
1110|201911|4|22
1110|201910|3|15
1110|201907|1|2
1117|201911|4|2
1117|201909|3|7
1117|201907|2|7
2114|201911|3|7
2114|201910|3|7
2114|201909|1|0
2114|201908|1|0
3226|201911|5|19
3226|201910|4|10
3226|201908|1|4
4555|201911|2|11
4555|201910|2|10
7888|201911|2|12
I want to achieve results by choosing ID_BOOK, MONTH_BOOK, QUALITY_BOOK AND DPD with the following provisions:
(there was an increase from the previous MONTH_BOOK)
(there was an increase in DPD from the previous MONTH_BOOK). ie:
ID_BOOK|MONTH_BOOK|QUALITY_BOOK|DPD
1110|201911|4|22
1117|201911|2|2
3226|201911|5|19
4555|201911|2|11
2114 has same QUALITY_BOOK AND DPD has no change so should not be fetched
7888 is appeared only once so even this should not be fetched
How can I achieve this? |
|
| Back to top |
|
 |
dneufarth
Active User
Joined: 27 Apr 2005
Posts: 420
Location: Inside the SPEW (Southwest Ohio, USA)
|
|
|
|
| You say you have done something. Please show what you have tried. |
|
| Back to top |
|
|
Ajay Kumar Bang
New User
Joined: 10 Feb 2023
Posts: 8
Location: Delhi
|
|
|
|
| Actually I tried to use inner join with table alias but kind of not working, not sure if we can use Sub query. |
|
| Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2141
Location: USA
|
|
|
|
| Ajay Kumar Bang wrote: |
| Actually I tried to use inner join with table alias but kind of not working, not sure if we can use Sub query. |
Please, demonstrate the sample(s) of your attempts.
And also: learn how to use the Code button to present any code/data samples. |
|
| Back to top |
|
|
Ajay Kumar Bang
New User
Joined: 10 Feb 2023
Posts: 8
Location: Delhi
|
|
|
|
| Code: |
select
this.id_book as current_id_book,
this.month_book as current_month_book ,
this.quality_book as current_quality_book,
this.dpd as current_dpd,
old.id_book as old_id_book,
old.month_book as old_month_book ,
old.quality_book as old_quality_book,
old.dpd as old_dpd
from
TABLE1 as this
inner join TABLE1 as old on
old.ID_BOOK = this.ID_BOOK
where
This.QUALITY_BOOK > old.QUALITY_BOOK
or this.DPD > old.DPD; |
|
|
| Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2141
Location: USA
|
|
|
|
| Ajay Kumar Bang wrote: |
| Code: |
select
this.id_book as current_id_book,
this.month_book as current_month_book ,
this.quality_book as current_quality_book,
this.dpd as current_dpd,
old.id_book as old_id_book,
old.month_book as old_month_book ,
old.quality_book as old_quality_book,
old.dpd as old_dpd
from
TABLE1 as this
inner join TABLE1 as old on
old.ID_BOOK = this.ID_BOOK
where
This.QUALITY_BOOK > old.QUALITY_BOOK
or this.DPD > old.DPD; |
|
| Quote: |
(there was an increase from the previous MONTH_BOOK)
(there was an increase in DPD from the previous MONTH_BOOK) |
1. Please, demonstrate the actual output you are not satisfied with?
2. There is no sign of any "previous month" verification in your code sample?
3. I usually split the complex SQL into smaller subqueries, with output of intermediate results, to find out: which of them has produced unexpected results? |
|
| Back to top |
|
|
Ajay Kumar Bang
New User
Joined: 10 Feb 2023
Posts: 8
Location: Delhi
|
|
|
|
1. Please, demonstrate the actual output you are not satisfied with?
getting duplicates.
2. There is no sign of any "previous month" verification in your code sample?
I want the latest record
3. I usually split the complex SQL into smaller subqueries, with output of intermediate results, to find out: which of them has produced unexpected results?
I'm new to Mainframe haven't worked on too much db2 queries. So looking for a help. |
|
| Back to top |
|
|
Ajay Kumar Bang
New User
Joined: 10 Feb 2023
Posts: 8
Location: Delhi
|
|
|
|
| I basically want to compare last 2 records inserted in the table, for instance on 4th execution, day 4 inserted record should be compared with day 3 inserted record and day 4 should not be compared with day 2 or day 1. |
|
| Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2141
Location: USA
|
|
|
|
The full set of your sample records is as follows
| Code: |
1110 201911 4 22
1110 201910 3 15
1110 201907 1 2
1117 201911 4 2
1117 201909 3 7
1117 201907 2 7
2114 201911 3 7
2114 201910 3 7
2114 201909 1 0
2114 201908 1 0
3226 201911 5 19
3226 201910 4 10
3226 201908 1 4
4555 201911 2 11
4555 201910 2 10
7888 201911 2 12 |
To simplify the analysis, let's take only one single group
| Code: |
1110 201911 4 22
1110 201910 3 15
1110 201907 1 2 |
After this operation
| Code: |
inner join TABLE1 as old on
old.ID_BOOK = this.ID_BOOK |
The expected result must be "each-to-each":
| Code: |
1110 201911 4 22 1110 201911 4 22
1110 201911 4 22 1110 201910 3 15
1110 201911 4 22 1110 201907 1 2
1110 201910 3 15 1110 201911 4 22
1110 201910 3 15 1110 201910 3 15
1110 201910 3 15 1110 201907 1 2
1110 201907 1 2 1110 201911 4 22
1110 201907 1 2 1110 201910 3 15
1110 201907 1 2 1110 201907 1 2 |
Next, this set is truncated after applying
| Code: |
where
This.QUALITY_BOOK > old.QUALITY_BOOK
or this.DPD > old.DPD |
It must produce the following:
| Code: |
1110 201911 4 22 1110 201910 3 15
1110 201911 4 22 1110 201907 1 2
1110 201910 3 15 1110 201911 4 22
1110 201910 3 15 1110 201907 1 2
1110 201907 1 2 1110 201911 4 22
1110 201907 1 2 1110 201910 3 15 |
In your code there is nothing to further extract only the required rows from this whole set of data. You need to enhance your SQL further.
Besides this, it is not clear: do you need the last WHERE clause to check for "greater than", or "not equal to"? |
|
| Back to top |
|
|
Ajay Kumar Bang
New User
Joined: 10 Feb 2023
Posts: 8
Location: Delhi
|
|
|
|
| Code: |
can I do like this?
Select a.id_book, max(a.month_book)
From table1 a. Table1 B
Where (a.quality_book - b.quality_month) > 0
Group by a.id_book
Having count(a.id_book) > 1;
But this might also compare day 3 and day 2, day 2 and Day 1.
Where as I need only day 4 and day 3. |
|
|
| Back to top |
|
|
Ajay Kumar Bang
New User
Joined: 10 Feb 2023
Posts: 8
Location: Delhi
|
|
|
|
| In simple I want to compare latest entry with immediate previous entry but not the older entries. Is this possible? |
|
| Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2141
Location: USA
|
|
| Back to top |
|
|
|
|
