Solved

Next and previous day SQL

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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
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…

808 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