Solved

Next and previous day SQL

Posted on 2013-05-21
2
450 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 31

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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
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…

758 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now