SQl query to get the date status changed

Ron Thomas

SQl query to get the date status changed

Hello.

We have a below data 

ITEM_NBR ITEM_ACTION  lOG_TS STATUS_CODE

1            U                      2015-06-01 12:01:45             A

1            U                       2015-05-28 22:07:38            D

1            U                       2015-02-10 12:55:48            A

        

2            U                       2015-05-28 22:07:38            D

2            U                       2015-02-10 12:55:48            A

3            U                       2015-05-28 22:07:38            I

3            U                       2015-02-10 12:55:48            A

 

We need to get all items  that have changed status from A to I or D . In our case 2 and 3 . Item 1 is not needed as it again got changed to status "A"

 

primary key for the table is (item_nbr & log_ts) and table has around a million rows 

Output needed 

Item_nbr  Log_ts  (Latest status change date)

Could someone please let me know how we can get that info ?

 

Thanks

Ron T

 

 

 

Edited By:
Ron Thomas[Organization Members] @ Jul 21, 2020 - 01:21 PM (America/Eastern)

bernd oppolzer

SQl query to get the date status changed
(in response to Ron Thomas)
select item_nbr
from tablex t1
where status_code in ('D', 'I')
and log_ts =
   (select max (log_ts)
   from tablex
  where item_nbr = t1.item_nbr);

this only checks if the newest log record has status 'D' or 'I';
it does not check, if there has been a change from 'A to 'D' or 'I'.
If this must be checked, too, the query will be more complicated

(some sort of AND EXISTS has to be added;
there must be a A line before the D and I line
and no other between the two)

HTH, kind regards

Bernd


Am 21.07.2020 um 19:18 schrieb Ron Thomas:
>
> Hello.
>
> We have a below data
>
> ITEM_NBR ITEM_ACTION  lOG_TS STATUS_CODE
>
> 1            U                      2015-06-01 12:01:45      A
>
> 1            U                       2015-05-28 22:07:38     D
>
> 1            U                       2015-02-10 12:55:48     A
>
> 2            U                       2015-05-28 22:07:38     D
>
> 2            U                       2015-02-10 12:55:48     A
>
> 3            U                       2015-05-28 22:07:38     I
>
> 3            U                       2015-02-10 12:55:48     A
>
> We need to get all items  that have changed status from A to I or D .
> In our case 2 and 3 . Item 1 is not needed as it again got changed to
> status "A"
>
> primary key for the table is (item_nbr & log_ts) and table has around
> a million rows
>
> Could someone please let me know how we can get that info ?
>
> Thanks
>
> Ron T
>
>
> -----End Original Message-----

Peter Vanroose

RE: SQl query to get the date status changed
(in response to Ron Thomas)

If I understand correctly: per group of the same item_nbr, you want to show max(log_ts), but only for groups that (1) have at least one status_code = 'A'; and (2) have 'D' or 'I' as status_code of that last iog_ts.

The "classical" approach thus is a group by item_nbr, then HAVING on the two additional conditions, where (2) is a bit trickier than (1).

An alternative approach is to use windowing functions (LEAD / LAG / MAX(...) OVER (...)). Makes the query less readable, I'm afraid.

The following could be a more readable solution; it traverses the table twice (which should not be problematic for a 1 million row table):

WITH a AS (SELECT item_nbr AS itnr, max(log_ts) AS last_ts
FROM tablename
WHERE status_code IN ('A','D','I')
GROUP BY item_nbr
HAVING min(status_code)='A'
)
SELECT item_nbr, log_ts, status_code
FROM a JOIN tablename ON itnr = item_nbr AND log_ts = last_ts
WHERE status_code IN ('D','I')

I'm using the fact that 'A' is the lowest possible status code amongst ('A','I','D')

--      Peter Vanroose
        ABIS Training & Consulting,
        Leuven, Belgium.
        https://abis.be/


In Reply to Ron Thomas:

Hello.

We have a below data 

ITEM_NBR ITEM_ACTION  lOG_TS STATUS_CODE

1            U                      2015-06-01 12:01:45             A

1            U                       2015-05-28 22:07:38            D

1            U                       2015-02-10 12:55:48            A

        

2            U                       2015-05-28 22:07:38            D

2            U                       2015-02-10 12:55:48            A

3            U                       2015-05-28 22:07:38            I

3            U                       2015-02-10 12:55:48            A

 

We need to get all items  that have changed status from A to I or D . In our case 2 and 3 . Item 1 is not needed as it again got changed to status "A"

 

primary key for the table is (item_nbr & log_ts) and table has around a million rows 

Output needed 

Item_nbr  Log_ts  (Latest status change date)

Could someone please let me know how we can get that info ?

 

Thanks

Ron T

Michael Hannan

RE: SQl query to get the date status changed
(in response to Peter Vanroose)

What about this:

SELECT item_nbr AS itnr, max(log_ts) AS last_ts
FROM tablename
WHERE status_code IN ('A','D','I')
GROUP BY item_nbr
HAVING
MAX(decode(STATUS_CODE,'A',log_ts,TIMESTAMP('0000-00-00.00.00.00'))) < MAX(log_ts)

I like to save joins where possible, if performance matters.

Michael Hannan,
DB2 Application Performance Specialist

Daniel Luksetich

SQl query to get the date status changed
(in response to Michael Hannan)
Now that is sweet!



+--------------------------------------+-----------------------------------------------------------+

| Daniel L Luksetich | IBM Certified Advanced Database Administrator – |

| IBM GOLD Consultant | Db2 10.1 for Linux UNIX and Windows |

| IDUG Content Committee Past-Chairman | IBM Certified Database Adminstrator – Db2 12 for z/OS |

| IDUG DB2-L Administrator | IBM Certified System Administrator – Db2 11 for z/OS |

| URL: https://db2expert.com https://db2expert.com | IBM Certified Application Developer – Db2 11 for z/OS |

+--------------------------------------+-----------------------------------------------------------+





From: Michael Hannan <[login to unmask email]>
Sent: Wednesday, July 22, 2020 6:15 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: SQl query to get the date status changed



What about this:

SELECT item_nbr AS itnr, max(log_ts) AS last_ts
FROM tablename
WHERE status_code IN ('A','D','I')
GROUP BY item_nbr
HAVING
MAX(decode(STATUS_CODE,'A',log_ts,TIMESTAMP('0000-00-00.00.00.00'))) < MAX(log_ts)

I like to save joins where possible, if performance matters.

Michael Hannan,
DB2 Application Performance Specialist



-----End Original Message-----

Attachments

  • image001.png (9.6k)
  • image002.png (14k)
  • image003.png (10.4k)
  • image004.png (11.7k)
  • image005.png (13.7k)
  • image006.png (13.1k)
  • image007.png (14.2k)
  • image008.png (7.5k)

James Campbell

SQl query to get the date status changed
(in response to Ron Thomas)
Assuming that you are interested in items which now have status code D or I and which at
some time in the past had status code A (which is slightly different from what you say is your
requirement)

select item_nbr
from (
select item_nbr
, IOG_TS
, status_code
, row_number() over(partition by item_nbr order by iog_ts desc) as rn
from ???
) A
where status_code in ('D','I')
and rn = 1
and exists
(select 1 from ??? b
where b.status_code = 'A'
and b.IOG_TS < a.IOG_TS
and b.item_num = a.item_num
)

James Campbell

On 21 Jul 2020 at 10:18, Ron Thomas wrote:

> Hello.
> We have a below data 
> ITEM_NBR ITEM_ACTION  lOG_TS STATUS_CODE
> 1            U                      2015-06-01 12:01:45             A
> 1            U                       2015-05-28 22:07:38            D
> 1            U                       2015-02-10 12:55:48            A
>         
> 2            U                       2015-05-28 22:07:38            D
> 2            U                       2015-02-10 12:55:48            A
> 3            U                       2015-05-28 22:07:38            I
> 3            U                       2015-02-10 12:55:48            A
>  
> We need to get all items  that have changed status from A to I or D . In our case 2 and 3 . Item 1 is not needed as it again got changed to status "A"
>  
> primary key for the table is (item_nbr & log_ts) and table has around a million rows 
>  
> Could someone please let me know how we can get that info ?
>  
> Thanks
> Ron T
>  
>  
>  

--
This email has been checked for viruses by AVG.
https://www.avg.com

Michael Hannan

RE: SQL query to get the date status changed
(in response to James Campbell)

When people pose these questions, I would first like to know, if it's a one off query, or if it will run very often and performance becomes important.

There are many ways to solve a problem, and so if performance is not important, the quickest to write and simplest.

If performance does matter, which of the requirements criteria can do a very strong filter to eliminate most rows not required, before we go any further. Then we are looking for a really clever SQL to do it efficiently.

In applications I review, I see many techniques the developer thought were quite smart, performing so badly. e.g. Call to Db2 to evaluate a "Regular Expression", Insensitive Scrollable Cursors (I might call then nasty since they copy to a Temp table under the covers). Switch-on Switch-off predicates that came up recently. 

DB2 SQL gives plenty of rope to hang yourself with.

Michael Hannan,
DB2 Application Performance Specialist

Edited By:
Michael Hannan[Organization Members] @ Jul 25, 2020 - 02:05 PM (Australia/Melbourne)

Ron Thomas

RE: SQL query to get the date status changed
(in response to Michael Hannan)

Apologies for the delay in replying as i was away.. thank you everyone for all of your suggestions .