CODEWITHSUNDEEP
sqlibm-midrangedb2-400rpgle
AS400 User
AS400 User

Reputation: 187

case statement in Where clause issue

I have quite a Query to write. I have a File called FG500 which has a field called UTXT(Certification number). The key(Not Unique) for the file is Model Number(MODL).The file can have multiple rows with the model numbers. It has also create date(CRDT), create time(CRTM), change date(CHDT), Change time(CHTM)

I need to pull latest EU Certification Number(UTXT) based on Create Date& Time / Change Date& Time. That is, if the change date/Time is not 0, then pull the latest of that.. Else check the create date and pull the latest record from that.

I will have to use a case but not sure how. Any help is highly appreciated. Sample Data:

Model Number    L12G4AGAEA    L12G4AGAEA
UTXT            (Blank)       E4*2002/24*0458
Create date     07/30/12      03/16/12
Create Time     08:32:22      08:32:22
Change Date     07/31/12      03/17/12
Change Time     08:32:22      08:32:22

Expected result would be a Blank as the highest change date/Time is the first record. Had the change date been blank, then I would go ahead with the create date/Time

Upvotes: 0

Views: 87

Answers (1)

Paul Maxwell
Paul Maxwell

Reputation: 35603

Perhaps this will assist. I built the table from your first set of tabular data, and the query demonstrates use of ROW_NUMBER() to arrive at the "latest" row for each modelnumber. I have used MS SQL Server from this example as the query syntax for DB2 is similar.

This might not be a complete answer, and as I don't have access to DB2 I'm not able to demonstrate how to combine the date and time columns which really should be done.

SQL Fiddle

MS SQL Server 2014 Schema Setup:

CREATE TABLE Table1
    ([ModelNumber] varchar(10), [UTXT] varchar(15), [CREATEdate] datetime, [CREATEtime] varchar(8), [CHANGEdate] datetime, [CHANGEtime] varchar(8))
;

INSERT INTO Table1
    ([ModelNumber], [UTXT], [CREATEdate], [CREATEtime], [CHANGEdate], [CHANGEtime])
VALUES
    ('L12G4AGAEA', NULL, '2012-07-30 00:00:00', '08:32:22', '2012-07-31 00:00:00', '08:32:22'),
    ('L12G4AGAEA', 'E4*2002/24*0458', '2012-03-16 00:00:00', '08:32:22', '2012-03-17 00:00:00', '08:32:22'),
    ('L12G4AGAEA', 'E4*2002/24*0458', '2012-07-11 00:00:00', '08:32:22', '2012-07-12 00:00:00', '08:32:22'),
    ('L12G4AGAEA', NULL, '2012-07-25 00:00:00', '08:32:22', '2012-07-26 00:00:00', '08:32:22'),
    ('L12G4AGAEA', 'E4*2002/24*0458', '2012-07-11 00:00:00', '08:32:22', '2012-07-12 00:00:00', '08:32:22'),
    ('L12G4AGAEA', 'E4*2002/24*0458', '2012-05-22 00:00:00', '08:32:22', '2012-05-23 00:00:00', '08:32:22'),
    ('L12G4AGAEA', 'E4*2002/24*0458', '2012-08-03 00:00:00', '08:32:22', '2012-08-03 00:00:00', '08:32:22'),
    ('L12G4AGAEA', 'E4*2002/24*0458', '2012-05-22 00:00:00', '08:32:22', '2012-05-23 00:00:00', '08:32:22'),
    ('L12G4AGAEA', 'E4*2002/24*0458', '2012-05-15 00:00:00', '08:32:22', '2012-05-16 00:00:00', '08:32:22'),
    ('L12G4AGAEA', 'E4*2002/24*0458', '2012-07-20 00:00:00', '08:32:22', '2012-07-21 00:00:00', '08:32:22'),
    ('L12G4AGAEA', 'E4*2002/24*0458', '2012-05-11 00:00:00', '08:32:22', '2012-05-14 00:00:00', '08:32:22')
;

Query 1:

select
    *
from (
      select
          *
          , row_number() over(partition by ModelNumber 
                             order by coalesce(CHANGEdate,CREATEdate) DESC) rn
      from table1
      ) d
where rn = 1

Results:

| ModelNumber |            UTXT |           CREATEdate | CREATEtime |           CHANGEdate | CHANGEtime | rn |
|-------------|-----------------|----------------------|------------|----------------------|------------|----|
|  L12G4AGAEA | E4*2002/24*0458 | 2012-08-03T00:00:00Z |   08:32:22 | 2012-08-03T00:00:00Z |   08:32:22 |  1 |

Upvotes: 1

Related Questions