Overlapping spell - Create new spell

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Overlapping spell - Create new spell

Author

Topic

Clas
Starting Member

33 Posts

Posted - 2009-03-19 : 10:33:05

/*
Find date (and create new spell) when spell is overlapping.
Compare 2 individuals spell in household (father present, brother present, husband/wife living together)
Maybe an function ?
*/

create table #source
(
individual int,
movingIN date,
movingOut date,
household int
)

/* Individual spell in household:
*/
Insert INTO #source

SELECT '123','1925-05-05','1927-06-02','100'
UNION ALL SELECT '123','1927-06-01','1932-04-04','200'
UNION ALL SELECT '123','1932-04-04','1942-06-06','100'

UNION ALL SELECT '234','1926-01-01', '1927-01-01','100'
UNION ALL SELECT '234','1927-01-01', '1929-01-01','500'
UNION ALL SELECT '234','1929-01-01', '1941-07-07','100'

select * from #source

create table #LivingTogether
(
individual_1 int,
individual_2 int,
Type nvarchar(20),
Startdate date,
Enddate date,
household int
)

/*
Individual 123 and 234 are living together in household 100 (spell overlapp)

RESULT, create new spell and insert in #LivingTogether:

Indiv_1 Indiv_2 Type Startdate Enddate
123 234 HusbandWife 1926-01-01 1927-01-01
123 234 HusbandWife 1932-04-04 1941-07-07

(Type in this case is HusbandWife)

THANKS !
*/

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-03-20 : 10:06:54

I'll try to guess your question. Are you asking how do you display rows which have overlapping time periods? A select statement of the table you placed data in gives mostly your "RESULT" except for the column 'Type' - which I'm not sure where you want to fill it from:

select a.individual as individual_1, b.individual as individual_2,
a.movingIN as Startdate_1,b.movingIN as Startdate_2,a.movingOut as Enddate_1,b.movingOut as Enddate_2
from #source a, #source b
WHERE a.individual != b.individual and
b.movingIN >= a.movingIN and
b.movingOut <= a.movingOut
ORDER BY b.individual;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-20 : 10:20:16

[code]
select a.individual as individual_1, b.individual as individual_2,
CASE WHEN a.movingIn > b.movingIn THEN a.movingIn ELSE b.movingIn END as Startdate,
CASE WHEN a.movingOut < b.movingOut THEN a.movingOut ELSE b.movingOut END as Enddate
from #source a
join #source b
ON a.individual <> b.individual and
a.household =b.household
and (b.movingOut BETWEEN a.movingIn AND a.movingOut
OR b.movingIn BETWEEN a.movingIn AND a.movingOut
)
ORDER BY b.individual;
[/code]

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-03-20 : 11:14:26

We can add a little to Visakh's to remove the duplicate row where a.individual and b.individual are placed in alternate columns by forcing one column to always be the lower id. I.ve also added the household to the SELECT.

select a.individual as individual_1, b.individual as individual_2,
CASE WHEN a.movingIn > b.movingIn THEN a.movingIn ELSE b.movingIn END as Startdate,
CASE WHEN a.movingOut < b.movingOut THEN a.movingOut ELSE b.movingOut END as Enddate
,b.household
from #source a
join #source b
ON a.individual <> b.individual and
--here is extra line to prevent alternate duplication
a.individual < b.individual and
a.household =b.household
and (b.movingOut BETWEEN a.movingIn AND a.movingOut
OR b.movingIn BETWEEN a.movingIn AND a.movingOut
)
ORDER BY individual_2;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources