?
Solved

Next and previous day SQL

Posted on 2013-05-21
2
Medium Priority
?
463 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
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…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

801 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