Click here to Skip to main content
Click here to Skip to main content
Go to top

Counting Consecutive Dates Using SQL

, 17 Feb 2013
Rate this:
Please Sign up or sign in to vote.
Recently I have answered one of the SQL based question in the CodeProject. Thanks for the participant that question wake up me to write this blog post. Question: He has a table (tblLeave) with the data like below. And he wants to output the data as below Condition: If same type of leave taken contin

Recently I have answered one of the SQL based question in the CodeProject. Thanks for the participant that question wake up me to write this blog post.

Question:

He has a table (tblLeave) with the data like below.

PAYCODE		LV_TYPE	FROM_DATE		TO_DATE	        	LVALUE
5023		SL    	14/12/2012 0:00		14/12/2012 0:00		1
5023		SL    	15/12/2012 0:00		15/12/2012 0:00		1
5023		COF   	16/12/2012 0:00		16/12/2012 0:00		1
5023		SL    	19/12/2012 0:00		19/12/2012 0:00		1
5023		SL    	22/12/2012 0:00		22/12/2012 0:00		1
5023		SL    	23/12/2012 0:00		23/12/2012 0:00		1
5023		SL    	24/12/2012 0:00		24/12/2012 0:00		1
5023		PL    	28/12/2012 0:00		28/12/2012 0:00		1
5023		PL    	29/12/2012 0:00		29/12/2012 0:00		1
5023		PL    	30/12/2012 0:00		30/12/2012 0:00		1
5023		PL    	31/12/2012 0:00		31/12/2012 0:00		1

And he wants to output the data as below

PAYCODE LV_TYPE FROM_DATE       TO_DATE         LVALUE
5023    SL      14/12/2012 0:00 15/12/2012 0:00 2
5023    COF     16/12/2012 0:00 16/12/2012 0:00 1
5023    SL      19/12/2012 0:00 19/12/2012 0:00 1
5023    SL      22/12/2012 0:00 24/12/2012 0:00 3
5023    PL      28/12/2012 0:00 31/12/2012 0:00 4

Condition: If same type of leave taken continuously, it should be merged in one row mentioning from_date to to_date.

Answer:

When I saw that question first time, I didn’t go through in details and just thought that was an easy grouping query. And I just gave following answer.

SELECT LV_TYPE,LV_TYPE,MIN(FROM_DATE) AS FROM_DATE ,MAX(TO_DATE) AS TO_DATE, COUNT(LVALUEP) AS LVALUE
FROM tblLeave
GROUP BY PAYCODE,LV_TYPE

But that’s wrong, he made comments saying it doesn’t make sense and highlighted the condition he wants. (Thanks for him; he didn’t down vote my answer). Again I read the question… Oh… That was a tricky question. He needs to group the leave by consecutive date. Isn’t that tricky?

To answer that, I use the DATEDIFF SQL function

DATEDIFF ( datepart , startdate , enddate )

http://msdn.microsoft.com/en-us/library/ms189794.aspx

Following is my answer and the output

SELECT PAYCODE,LV_TYPE, MIN(FROM_DATE) AS FROM_DATE,
       MAX(FROM_DATE) AS TO_DATE, COUNT('A') AS LVALUE
FROM (
SELECT PAYCODE,LV_TYPE,FROM_DATE,
    DATEDIFF(D, ROW_NUMBER() OVER(ORDER BY FROM_DATE), FROM_DATE) AS Diff
FROM tblLeave) AS dt
GROUP BY PAYCODE,LV_TYPE, Diff
ORDER BY FROM_DATE

PAYCODE     LV_TYPE FROM_DATE               TO_DATE                 LVALUE
----------- ------- ----------------------- ----------------------- -----------
5023        SL      2012-12-14 00:00:00.000 2012-12-15 00:00:00.000 2
5023        COF     2012-12-16 00:00:00.000 2012-12-16 00:00:00.000 1
5023        SL      2012-12-19 00:00:00.000 2012-12-19 00:00:00.000 1
5023        SL      2012-12-22 00:00:00.000 2012-12-24 00:00:00.000 3
5023        PL      2012-12-28 00:00:00.000 2012-12-31 00:00:00.000 4

Query Explanation:

Before explain the logic see the following query and the output.

SELECT PAYCODE,LV_TYPE,FROM_DATE,
	ROW_NUMBER() OVER(ORDER BY FROM_DATE) AS ROW_NUMBER,
    DATEDIFF(D, ROW_NUMBER() OVER(ORDER BY FROM_DATE), FROM_DATE) AS Diff
FROM tblLeave

PAYCODE     LV_TYPE FROM_DATE               ROW_NUMBER           Diff
----------- ------- ----------------------- -------------------- -----------
5023        SL      2012-12-14 00:00:00.000 1                    41254
5023        SL      2012-12-15 00:00:00.000 2                    41254
5023        COF     2012-12-16 00:00:00.000 3                    41254
5023        SL      2012-12-19 00:00:00.000 4                    41256
5023        SL      2012-12-22 00:00:00.000 5                    41258
5023        SL      2012-12-23 00:00:00.000 6                    41258
5023        SL      2012-12-24 00:00:00.000 7                    41258
5023        PL      2012-12-28 00:00:00.000 8                    41261
5023        PL      2012-12-29 00:00:00.000 9                    41261
5023        PL      2012-12-30 00:00:00.000 10                   41261
5023        PL      2012-12-31 00:00:00.000 11                   41261

By seen this, you will realize that above query generate the same Diff  value for all the consecutive dates. Now you can easily group this and get the counts as you like.


License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Tharaka MTR
Technical Lead Eyepax IT Consulting (Pvt) Ltd.
Sri Lanka Sri Lanka
Having more than 9 year hands-on industry experience in software development
Responsible for designing, implementing and managing complex software systems with stringent up-time requirement.

Visit my blog
Follow on   Twitter   LinkedIn

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Mobile
Web03 | 2.8.140922.1 | Last Updated 17 Feb 2013
Article Copyright 2013 by Tharaka MTR
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid