Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Next and previous day SQL

Posted on 2013-05-21
2
Medium Priority
?
466 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 37

Assisted Solution

by:momi_sabag
momi_sabag earned 80 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 1920 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 does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
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…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

636 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