Re: DB2 SQL help

[Peter Dow <petercdow@xxxxxxxxx>]

Hi VInay,

I haven't used these OLAP functions and date conversion functions too 
much so I wanted to try them out.  There's probably cleaner way, but 
this seems to get what you want. Or I might have misunderstood what you 
wanted.

create table testlag (
  field1  char(3) not null with default
, date1  dec(7,0) not null with default
, date2  dec(7,0) not null with default
)

insert into testlag values
('F1', 1230101, 1241231),
('F1', 1250101, 1261231),
('F1', 1270101, 1270630),
('F2', 1260101, 1260215),
('F2', 1260216, 1261231),
('F2', 1260601, 1271231),
('F3', 1240101, 1240331),
('F3', 1240601, 1241231),
('F4', 1240101, 1241231),
('F5', 1240101, 1241231),
('F5', 1250301, 1250630)

with t1 as (
 select field1
, date(timestamp_format(substr(digits(date1),2,6), 'yymmdd')) d1
, date(timestamp_format(substr(digits(date2),2,6), 'yymmdd')) d2
, lag(date(timestamp_format(substr(digits(date2),2,6), 'yymmdd')))
     over (partition by field1 order by date2) prev_d2
 from testlag
)
select field1, d1, d2, prev_D2, days(prev_D2) - days(d1)
  from t1
 where days(prev_D2) - days(d1) < -1

FIELD1  D1        D2        PREV_D2   Numeric Expression
 F3     06/01/24  12/31/24  03/31/24                62-
 F5     03/01/25  06/30/25  12/31/24                60-
********  End of data  ********


--
*Peter Dow* /
Dow Software Services, Inc.
909 793-9050
petercdow@xxxxxxxxx
pdow@xxxxxxxxxxxxxx /
On 9/9/2024 6:47 PM, Vinay Gavankar wrote:
> Hi All, I need help with creating an SQL.
> I have a table with many fields but there are 3 relevant fields for my
> problem.
> Field1, From Date, Thru Date
> The Date fields are date ranges, defined as packed(7:0) and have valid
> dates in *cymd format. I need to find Field 1 values where there is a gap
> between the date ranges.
>
> For example, if my table has:
> F1, 1230101, 1241231
> F1, 1250101, 1261231
> F1, 1270101, 1270630
> F2, 1260101, 1260215
> F2, 1260216, 1261231
> F2, 1260601, 1271231     (the date ranges may overlap or even be same)
> F3, 1240101, 1240331
> F3, 1240601, 1241231
> F4, 1240101, 1241231
> F5, 1240101, 1241231
> F5, 1250301, 1250630
>
> I need to find values F3 and F5
>
> Can this be done using SQL? It is a one time exercise to query the data, so
> multiple SQLs with intermediate files is also okay.
>
> TIA
> Vinay