Consecutive Dates in SQL

Link to problem: https://www.hackerrank.com/challenges/projects/problem

The issue here is that we need to identify consecutive dates for the problem. Take a look at the sample data for this problem. Notice anything?

At first, we can clearly see that between the consecutive start and end dates there is one day’s difference. This is irrelevant though. Notice the dates that occur in only the start date column, and likewise for the end date column. Please excuse the crude drawing. Note that I have highlighted each unique value in each column. We can extract these dates by using the NOT IN clause. We can extract each of these unique dates in unique columns with separate SELECT statements that utilize a subquery to select the dates not in the adjacent column in the given table. This will ensure we select only nonconsecutive dates. Here is an example of what the SELECT statement would look like.

The problem here is we need to combine these two statements together with a JOIN. We can create a variable in order to assign a row number thereby creating a field to join the two SELECT statements together. Once done, we can extract what we need from both and ORDER the tables by the total amount of days between the start and end dates. We can do this by using the DATEDIFF function. Here is the end result of the query.

About the author

programmingforfinance

Hi, I'm Frank. I have a passion for coding and extend it primarily within the realm of Finance.

View all posts

Leave a Reply

Your email address will not be published. Required fields are marked *