Solved

Next and previous day SQL

Posted on 2013-05-21
2
454 Views
Last Modified: 2013-05-29
I need some DB2 SQL help identifying accounts that had restrictions removed the previous day, then placed again the next day. Example below:

ACCT_NUM      START_DT               END_DT
10019220      02-28-2013      04-08-2013
10019220      04-09-2013      04-29-2013

The restriction end_dt is 4/8/2013, and the next day (start_dt 4/9/2013), it was placed on the account.
What date function/code snippet do I use to identify only this scenario above?

Thank you.
0
Comment
Question by:saved4use
2 Comments
 
LVL 37

Assisted Solution

by:momi_sabag
momi_sabag earned 20 total points
ID: 39185052
try

select t1.acct_num
from your_table t1
 join your_table t2
  on t1.acct_num = t2.acct_num and t1.end_dt = t2.end_dt - 1 day
0
 
LVL 32

Accepted Solution

by:
awking00 earned 480 total points
ID: 39185237
What version of DB2? I believe with 9.5 you have the lead/lag functions available. If so,

select acct_num start_dt, end_dt from
(select acct_num start_dt, end_dt,
 lead(start_dt) over (partition by acct_num order by start_dt) as nextdt
 from yourtable) x
where days(nextdt) - days(end_dt) <= 1;
In your example, this would retrieve
10019220      02-28-2013      04-08-2013
If you wanted
10019220      04-09-2013      04-29-2013
select acct_num start_dt, end_dt from
(select acct_num start_dt, end_dt,
 lag(end_dt) over (partition by acct_num order by start_dt) as prevdt
 from yourtable) x
where days(start) - days(prevdt) <= 1;
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I guess that all of us know that caching the data usually increase the performance, but I worried if all of us are aware about the risk that caching the data provides and how to minimize this.  That’s the reason why I decided to write this short art…
As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question