TSQL - Find maximum running date gap in a date range and also date gap ranking

Question

Post reply

TSQL - Find maximum running date gap in a date range and also date gap ranking

iBar

SSCertifiable

Points: 5872
More actions
February 27, 2012 at 6:37 am

#256434

Hi,
I want to find maximum running date gap in a data set and also want to rank the gap days. Following is the sample data.
USE TempDB
GO
SET LANGUAGE US_ENGLISH
SET NOCOUNT ON
IF OBJECT_ID('#temp_date_gap') IS NOT NULL
DROP TABLE #temp_date_gap
CREATE TABLE #temp_date_gap
(
date_and_time datetime primary key
)
INSERT INTO #temp_date_gap VALUES ('2012-05-01 09:00:00.000')
INSERT INTO #temp_date_gap VALUES ('2012-05-03 09:00:00.000')
INSERT INTO #temp_date_gap VALUES ('2012-05-06 11:30:00.000')
INSERT INTO #temp_date_gap VALUES ('2012-06-01 01:00:00.000')
INSERT INTO #temp_date_gap VALUES ('2012-06-03 00:00:01.000')
INSERT INTO #temp_date_gap VALUES ('2012-06-20 14:00:00.000')
INSERT INTO #temp_date_gap VALUES ('2012-08-01 09:00:00.000')
INSERT INTO #temp_date_gap VALUES ('2012-08-03 09:00:00.000')
INSERT INTO #temp_date_gap VALUES ('2012-08-06 11:30:00.000')
INSERT INTO #temp_date_gap VALUES ('2012-09-01 01:00:00.000')
INSERT INTO #temp_date_gap VALUES ('2012-09-03 00:00:01.000')
INSERT INTO #temp_date_gap VALUES ('2012-09-20 14:00:00.000')
select *
from #temp_date_gap
1: Output 1 - Maximum Gap with dates
Last_date_BEFORE_largest_gap First_date_AFTER_largest_gap
20/06/2012 14:0001/08/2012 09:00
2: Output 2 - Gap ranking
date_and_time_startdate_and_time_endrunning_day_diffRank
20/06/2012 14:0001/08/2012 09:00421
06/05/2012 11:3001/06/2012 01:00262
06/08/2012 11:3001/09/2012 01:00262
03/06/2012 00:0020/06/2012 14:00183
03/09/2012 00:0020/09/2012 14:00183
03/05/2012 09:0006/05/2012 11:3034
03/08/2012 09:0006/08/2012 11:3034
01/05/2012 09:0003/05/2012 09:0025
01/08/2012 09:0003/08/2012 09:0025
01/06/2012 01:0003/06/2012 00:0025
01/09/2012 01:0003/09/2012 00:0025
If possible then can we make this dynamic to find the Nth largest GAP?
Thanks.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 1

You can use this to give you output 2. Output 1 is where Rank is 1

WITH CTE AS (

SELECT date_and_time,

ROW_NUMBER() OVER(ORDER BY date_and_time) AS rn

FROM #temp_date_gap)

SELECT t1.date_and_time AS date_and_time_start,

t2.date_and_time AS date_and_time_end,

DATEDIFF(Day,t1.date_and_time,t2.date_and_time) AS running_day_diff,

DENSE_RANK() OVER(ORDER BY DATEDIFF(Day,t1.date_and_time,t2.date_and_time) DESC) AS Rank

FROM CTE t1

INNER JOIN CTE t2 ON t2.rn=t1.rn+1

ORDER BY running_day_diff DESC,date_and_time_start;

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

iBar SSCertifiable Points: 5872 More actions · Answer 2

Thanks Marks,

1: Any other possibility without CTE?

2: What if maximum date GAP required in each year? so that year will be a sort of group key and within each group what is the maximum date gap?

So now the sample is:

USE TempDB

GO

SET LANGUAGE US_ENGLISH

SET NOCOUNT ON

IF OBJECT_ID('#temp_date_gap') IS NOT NULL

DROP TABLE #temp_date_gap

CREATE TABLE #temp_date_gap

(

date_year varchar(4),

date_and_time datetime primary key

)

INSERT INTO #temp_date_gap VALUES ('2011','2011-05-01 09:00:00.000')

INSERT INTO #temp_date_gap VALUES ('2011','2011-05-03 09:00:00.000')

INSERT INTO #temp_date_gap VALUES ('2011','2011-05-06 11:30:00.000')

INSERT INTO #temp_date_gap VALUES ('2011','2011-06-01 01:00:00.000')

INSERT INTO #temp_date_gap VALUES ('2011','2011-06-03 00:00:01.000')

INSERT INTO #temp_date_gap VALUES ('2011','2011-06-20 14:00:00.000')

INSERT INTO #temp_date_gap VALUES ('2011','2011-08-01 09:00:00.000')

INSERT INTO #temp_date_gap VALUES ('2011','2011-08-03 09:00:00.000')

INSERT INTO #temp_date_gap VALUES ('2011','2011-08-06 11:30:00.000')

INSERT INTO #temp_date_gap VALUES ('2011','2011-09-01 01:00:00.000')

INSERT INTO #temp_date_gap VALUES ('2011','2011-09-03 00:00:01.000')

INSERT INTO #temp_date_gap VALUES ('2011','2011-09-20 14:00:00.000')

INSERT INTO #temp_date_gap VALUES ('2012','2012-05-01 09:00:00.000')

INSERT INTO #temp_date_gap VALUES ('2012','2012-05-03 09:00:00.000')

INSERT INTO #temp_date_gap VALUES ('2012','2012-05-06 11:30:00.000')

INSERT INTO #temp_date_gap VALUES ('2012','2012-06-01 01:00:00.000')

INSERT INTO #temp_date_gap VALUES ('2012','2012-06-03 00:00:01.000')

INSERT INTO #temp_date_gap VALUES ('2012','2012-06-20 14:00:00.000')

INSERT INTO #temp_date_gap VALUES ('2012','2012-08-01 09:00:00.000')

INSERT INTO #temp_date_gap VALUES ('2012','2012-08-03 09:00:00.000')

INSERT INTO #temp_date_gap VALUES ('2012','2012-08-06 11:30:00.000')

INSERT INTO #temp_date_gap VALUES ('2012','2012-09-01 01:00:00.000')

INSERT INTO #temp_date_gap VALUES ('2012','2012-09-03 00:00:01.000')

INSERT INTO #temp_date_gap VALUES ('2012','2012-09-20 14:00:00.000')

select *

from #temp_date_gap

Sorry for the requirement changes but its related and i want to keep it on one place.

Cheers.

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 3

Per year as below. As for not using CTEs, you could change the CTE to a derived table, but that's pretty much just a cosmetic difference.

WITH CTE AS (

SELECT date_year,

date_and_time,

ROW_NUMBER() OVER(PARTITION BY date_year ORDER BY date_and_time) AS rn

FROM #temp_date_gap)

SELECT t1.date_year,

t1.date_and_time AS date_and_time_start,

t2.date_and_time AS date_and_time_end,

DATEDIFF(Day,t1.date_and_time,t2.date_and_time) AS running_day_diff,

DENSE_RANK() OVER(ORDER BY DATEDIFF(Day,t1.date_and_time,t2.date_and_time) DESC) AS Rank

FROM CTE t1

INNER JOIN CTE t2 ON t2.rn=t1.rn+1

AND t2.date_year=t1.date_year

ORDER BY running_day_diff DESC,date_and_time_start;

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

iBar SSCertifiable Points: 5872 More actions · Answer 4

Great!

For output 1, i still need a drived table OR in-line view to apply where on dense_rank?

As you know, Dense_rank cannot be use in the same where clause.

in my scenario, i want to use this logic in a DML. Can you suggest any simple way please.

Thanks.

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 5

Lots of options. For your temporary table solution you can do this

WITH CTE AS (

SELECT date_year,

date_and_time,

ROW_NUMBER() OVER(PARTITION BY date_year ORDER BY date_and_time) AS rn

FROM #temp_date_gap),

Results AS (

SELECT t1.date_year,

t1.date_and_time AS date_and_time_start,

t2.date_and_time AS date_and_time_end,

DATEDIFF(Day,t1.date_and_time,t2.date_and_time) AS running_day_diff,

DENSE_RANK() OVER(ORDER BY DATEDIFF(Day,t1.date_and_time,t2.date_and_time) DESC) AS Rank

FROM CTE t1

INNER JOIN CTE t2 ON t2.rn=t1.rn+1

AND t2.date_year=t1.date_year)

SELECT date_year,date_and_time_start,date_and_time_end,running_day_diff,Rank

FROM Results

WHERE Rank=1;

If your data is in a permanent table you could create a view which you can more easily use in any other DML.

CREATE VIEW dbo.myview

AS

WITH CTE AS (

SELECT date_year,

date_and_time,

ROW_NUMBER() OVER(PARTITION BY date_year ORDER BY date_and_time) AS rn

FROM dbo.mytable)

SELECT t1.date_year,

t1.date_and_time AS date_and_time_start,

t2.date_and_time AS date_and_time_end,

DATEDIFF(Day,t1.date_and_time,t2.date_and_time) AS running_day_diff,

DENSE_RANK() OVER(ORDER BY DATEDIFF(Day,t1.date_and_time,t2.date_and_time) DESC) AS Rank

FROM CTE t1

INNER JOIN CTE t2 ON t2.rn=t1.rn+1

AND t2.date_year=t1.date_year;

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

iBar SSCertifiable Points: 5872 More actions · Answer 6

Thanks Mark,

yes, as i said view or drived tabled are the options for that.

The thing which i have just noticed was if there is only one date in a year or group, for example, then this logic will not working.

As i want that if there is only that in a group then return same date with rank 1

possible?

Thanks.

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 7

Changing the INNER JOIN to a LEFT OUTER should work

WITH CTE AS (

SELECT date_year,

date_and_time,

ROW_NUMBER() OVER(PARTITION BY date_year ORDER BY date_and_time) AS rn

FROM #temp_date_gap)

SELECT t1.date_year,

t1.date_and_time AS date_and_time_start,

COALESCE(t2.date_and_time,t1.date_and_time) AS date_and_time_end,

COALESCE(DATEDIFF(Day,t1.date_and_time,t2.date_and_time),0) AS running_day_diff,

DENSE_RANK() OVER(ORDER BY COALESCE(DATEDIFF(Day,t1.date_and_time,t2.date_and_time),0) DESC) AS Rank

FROM CTE t1

LEFT OUTER JOIN CTE t2 ON t2.rn=t1.rn+1

AND t2.date_year=t1.date_year

ORDER BY running_day_diff DESC,date_and_time_start;

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Dwain Camps SSC Guru Points: 86948 More actions · Answer 8

MidBar,

This version has a very slightly better query plan cost (49%/51%) than the CTE version and may not have the issue you report when 1 record.

SELECT date_and_time_start

,date_and_time_end

,DATEDIFF(day,date_and_time_start, date_and_time_end) as running_day_diff

,DENSE_RANK() OVER(ORDER BY DATEDIFF(Day, date_and_time_start, date_and_time_end) DESC) AS Rank

FROM (

SELECT t1.date_and_time AS date_and_time_start

,(SELECT TOP 1 date_and_time

FROM #temp_date_gap t2

WHERE t1.date_and_time < t2.date_and_time

ORDER BY date_and_time) AS date_and_time_end

FROM #temp_date_gap t1

) x

WHERE date_and_time_end IS NOT NULL

ORDER BY DATEDIFF(day,date_and_time_start, date_and_time_end) DESC

Give it a try and let us know.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 9

MidBar (2/27/2012)
1: Any other possibility without CTE?

I have to ask... why didn't you want this with a CTE?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)