Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Removing Overlapping periods Expand / Collapse
Author
Message
Posted Tuesday, August 27, 2013 11:57 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, October 4, 2013 1:45 PM
Points: 39, Visits: 136
Hi,
I have a problem and I have created a solution using loops. I would like it to be more efficient if possible. The explanation of the problem is in the code below. If any one can come up with a better solution I would be very appreciative!





/****Create sample table of Intervals********************/



IF Object_ID('tempdb.dbo.#Intervals') is not null
Drop table #Intervals

Create table #Intervals
(ID Int Not null,
PeriodBegin INT not null,
PeriodEnd INT not null)

Declare @id int = 1
Declare @PeriodBegin INT = 1
Declare @i int = 0
While @i <= 2000
Begin

INSert into #Intervals
Select id = @id, PeriodBegin = @PeriodBegin+ @i,PeriodEnd = @PeriodBegin+ @i+ 62

Set @i = @i + 1

END


Select * FROM #Intervals

/****************************
For the above table I need to retain only the records that do not overlap a previous period starting with periodBegin = 1.
The next 63 rows have a period begin less than the 1st row's period end, so those rows will be removed.
The next valid row would be row 64 as the period begin is less than the first row's period end.
I have a method of doing this below but it uses a loop and the tables that I am doing this for have millions of rows in some cases so it is not as efficient as I would like.
I cannot just choose the 1st, 64th,127th...etc. rows because not every ordinal value will be represented in the period begin column, the length of the period will always remain consistent however.
Can anyone come up with a faster method for doing this???
*********************************************************************************************************/



IF Object_ID('tempdb.dbo.#UniqueIntervals') is not null
Drop table #UniqueIntervals
Create table #UniqueIntervals
(ID Int Not null,
PeriodBegin INT not null,
PeriodEnd Int Not null)



Declare @loop int = 1
While @loop <= 32 ---32 possible unique periods

Begin

Insert into #UniqueIntervals (id, PeriodBegin,PeriodEnd )
Select id, PeriodBegin = Min(PeriodBegin), PeriodENd = Min(PeriodEnd)
FROM #Intervals
Group by id
order by Min(PeriodBegin)

Delete b FROM #UniqueIntervals a
INNER JOIN #Intervals b
ON a.id = b.id
and b.PeriodBegin <= a.PeriodEnd

Set @loop = @loop + 1
END


Select * FROM #UniqueIntervals
/************************************************************************/
Post #1488880
Posted Tuesday, August 27, 2013 2:31 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, August 28, 2014 7:29 AM
Points: 114, Visits: 612
It will be helpful if you attach also sample data, expected result and the version of SQL Server that you are using.



Post #1488943
Posted Tuesday, August 27, 2013 3:09 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 8:23 AM
Points: 4,352, Visits: 6,168
And while you are gen'ing up sample data for us search the web for Itzik Ben-Gan's various posts and articles (SQLMag has some iirc) on interval processing. There is some AMAZINGLY efficient stuff that can be done with SQL 2005+ using new mechanisms!!

Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #1488958
Posted Friday, August 30, 2013 10:12 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, October 4, 2013 1:45 PM
Points: 39, Visits: 136
The first query is there to create a sample table. Does it not work?
Post #1490221
Posted Friday, August 30, 2013 10:14 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, October 4, 2013 1:45 PM
Points: 39, Visits: 136
Oh, and I have access to Sql Server 2012. The second part of the script is my solution and gives the expected result. I'm just checking to see if there is a better solution.
Post #1490222
Posted Friday, August 30, 2013 1:56 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, August 28, 2014 7:29 AM
Points: 114, Visits: 612
If I understood it correctly, you want groups of 63 rows supposing they are consecutives and then select the first row for each group.

WITH T AS (
SELECT
ID,
PeriodBegin,
PeriodEnd,
ROW_NUMBER() OVER(PARTITION BY ID, ((PeriodBegin - 1) / 63) + 1 ORDER BY PeriodBegin) AS rn
FROM
#Intervals
)
SELECT
ID,
PeriodBegin,
PeriodEnd
FROM
T
WHERE
rn = 1
ORDER BY
ID,
PeriodBegin;
GO

This expression ((PeriodBegin - 1) / 63) + 1 assign an incremental group number (63 rows each group) and the ROW_NUMBER enumerate the rows partition by (ID, grpnum). The rest is to select all rows where rn = 1.

I am afraid I am taking it too simple.



Post #1490290
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse