Consolidate Date Ranges
-
Hi All
I need to consolidate date ranges where overlaps may occur:
if object_id('tempdb..#Test') is not null
drop table #Test
(
[Resource tag] int
,[OUG] varchar(50)
,[start date] datetime
,[end date] datetime
)
truncate table #Test
insert into #Test values (00001 , 'A' , '01-jun-2005' , '30-jun-2005')
insert into #Test values (00001 , 'B' , '19-jun-2005' , '25-jun-2005')
insert into #Test values (00002 , 'A' , '01-jun-2005' , '15-jun-2005')
insert into #Test values (00002 , 'B' , '16-jun-2005' , '30-jun-2005')
Resource Tag OUG Start Date End Date
1 A 2005-06-01 00:00:00.000 2005-06-30 00:00:00.000
1 B 2005-06-19 00:00:00.000 2005-06-25 00:00:00.000
2 A 2005-06-01 00:00:00.000 2005-06-15 00:00:00.000
2 B 2005-06-16 00:00:00.000 2005-06-30 00:00:00.000
In this sample Resource Tag 1 worked in OUG B for a part of the time that he was actually assigned to A.
My End Results should look like so:
Resource Tag OUG Start Date End Date
1 A 2005-06-01 00:00:00.000 2005-06-18 00:00:00.000
1 B 2005-06-19 00:00:00.000 2005-06-25 00:00:00.000
1 A 2005-06-26 00:00:00.000 2005-06-30 00:00:00.000
2 A 2005-06-01 00:00:00.000 2005-06-15 00:00:00.000
2 B 2005-06-16 00:00:00.000 2005-06-30 00:00:00.000
Thanks
Peet Schultz
-
Select ResourceTag, OUG, min(startdate), max(enddate)
from #test
group by ResourceTag, OUG
-
I am a bit confused with this question. It looks as if you simply need to alter your existing data to match the actual events...
The following will yield the output you desired, but you indicated this was one example. If you have numerous examples like this, are you hoping for something that will accept user inputs to alter the output or am I misinterpretting your question.
IF object_id( 'tempdb..#Test') IS NOT NULL
DROP TABLE #Test
CREATE TABLE #Test( [ResourceTag] integer,
[OUG] varchar(1),
[StartDate] datetime,
[EndDate] datetime)
INSERT INTO #Test VALUES( 00001 , 'A' , '01-jun-2005' , '30-jun-2005')
INSERT INTO #Test VALUES( 00001 , 'B' , '19-jun-2005' , '25-jun-2005')
INSERT INTO #Test VALUES( 00002 , 'A' , '01-jun-2005' , '15-jun-2005')
INSERT INTO #Test VALUES( 00002 , 'B' , '16-jun-2005' , '30-jun-2005')
UPDATE #Test SET
EndDate = '18-jun-2005'
WHERE ResourceTag = 1
AND OUG = 'A'
INSERT INTO #Test VALUES( 00001 , 'A' , '26-jun-2005' , '30-jun-2005')
SELECT * FROM #Test
ORDER BY ResourceTag, StartDate, EndDate, OUG
I wasn't born stupid - I had to study.
-
Peet, thanks for providing a table definition and sample data that I can copy and paste into my query analyzer
And thanks for an interesting problem 
A side remark: How can you live with spaces in your column names? Makes the SQL more difficult to read, I think.
The following is a first attempt. If data is not more complicated than in your test example, it might work OK. Otherwise please supply examples where my query fails.
Good luck
insert into #Test
select t1.[Resource Tag], t1.OUG, dateadd(d, 1, t2.[End Date]), t1.[End Date]
from #Test t1 inner join #Test t2
on
t1.[Resource Tag] = t2.[Resource Tag]
and t1.OUG != t2.OUG
and t1.[Start Date] < t2.[Start Date]
and t1.[End Date] > t2.[End Date]
update t1
set t1.[End Date] = dateadd(d, -1, t2.[Start Date])
from #Test t1 inner join #Test t2 on
t1.[Resource Tag] = t2.[Resource Tag]
and t1.OUG != t2.OUG
and t1.[End Date] > t2.[Start Date]
and t2.[Start Date] > t1.[Start Date]
and not exists
(
select * from #Test t3 where
t1.[Resource Tag] = t3.[Resource Tag]
and t1.OUG != t3.OUG
and t1.[End Date] > t3.[Start Date]
and t3.[Start Date] > t1.[Start Date]
and t3.[Start Date] < t2.[Start Date]
)
select * from #Test
order by [Resource Tag], [Start Date]
-
Hi Jesper
Thanks for your post. This seems to work fine. The real-world issue is as follows:
This is a mining/Payroll application where the person (represented by the Resource Tag) may have worked in any number of Org Units in the mine (represented by the OUG). The OUG will cary an "Assignment Number" that is used for costing purposes. The default "Assignment number" may be overridden based on certain condittions. The default Assignment Number may also be changed at any stage during the costing period.
As in my sample, he may be assigned to OUG A for the whole period, but in realty he has spent some time working in OUG B. When the cost splitting between the Org Units is done the costs has to be pro-rated between the different OUG's where the person has worked based on the number of days per OUG over the total number of days in the period. We cary the Work History for the Resource Tag in a single table, but the cost splitting can be overridden by certain conditions. Thus the final result will be derived from at least four different tables.
The final result is then a table that indicates where he has worked and for which periods, also indication how the cost is to be split between the different "Assignment numbers"
The following may help to illustrate the situation:
Default Assignment # OUG Assignment # Start Date End Date Note A A01 01-jun-2005 14-jun-2005 A A02 15-jun-2005 30-jun-2005 New Default for OAG A B B01 01-jun-2005 30-jun-2005 Override Assignment # Resource Tag Assignment # Start Date End Date A0001 CC01 20-Jun-2005 25-Jun-2005 Work History Resource Tag OUG Start Date End Date Note A0001 A 01-jun-2005 30-Jun-2005 Normal Workplace A0001 B 10-jun-2005 10-Jun-2005 One day re-assigned Cost Splitting from Work History & Assignment Number Resource Tag OUG Start Date End Date Number of Days Assignment # Note A0001 A 01-jun-2005 09-Jun-2005 9 A01 Default for OUG A A0001 B 10-jun-2005 10-Jun-2005 1 B01 Default for OUG B A0001 A 11-jun-2005 14-jun-2005 4 A01 Default for OUG A A0001 A 15-jun-2005 19-jun-2005 5 A02 New Default for OUG A- Assignment Number has Changed A0001 A 20-jun-2005 25-jun-2005 6 CC01 Override Assignment No for This Resource tag A0001 A 26-jun-2005 30-Jun-2005 5 A02 Default for OUG A Total Days 30 To add some dimension to the problem, we run this payroll for about 40 000 (40 thousand!) employees. A single payrun may contain between 8 000 and 10 000 employees.
I will do some more testing and keep you posted.
With regards to the spaces in the table/ field names, it was a consious decision taken when the system was designed to use "friendly" names and we are now so used to this that in my humble opinion it actually helps in the development effort, but then I guess there are probaly more opinions about this than political parties in this old world.
Regards and again, thanks for taking time out to help.
Peet
-
Surely, there are many opinions on column names, and you shouldn't redesign a whole system based on what I think
But I would prefer "StartDate" instead of "Start Date" and so on to avoid having [] in the SQL.I am sure you will find some errors in my SQL when data is getting more complicated - employees working in other OUGs for more than one period during an assignment, cases where an employee works in A from 1st-15th and in B from 10th to 20th. I haven't tested these cases as I don't know if they are relevant.
Finally, I am sure that the SQL can be rewritten such that it performes much better.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply