November 29, 2012 at 5:24 am
Hi all,
I have a requirement to place family groups into a "pecking order". This involves:
Identifying the "Head of Household" group. This could consist of one or two people. Members of this group rank above non-members at all times.
Where the Head of Household group is mixed sex, rank them based on gender, with Males first.
Then rank any non-members of the Head of Household group by age.
Not all records have a gender, where at least one gender is unknown, the following rules apply:
If the gender mix is U/M then rank M first. If the gender mix is U/F, rank U first, where both are U, rank by age.
DOB is not always populated, where no DOB is present, rank these last.
Note: I didn't make the rules.
I have a solution, but it feels a bit over-complicated. I was hoping that someone might be able to suggest a better way.
My solution:
if object_id('tempdb..#tmp') is not null drop table #tmp
go
create table #tmp (FamilyId int, Gender char(1), DOB datetime)
-- M/F parents with children
insert #tmp values (1,'M','1 October 1950')
insert #tmp values (1,'F','1 October 1949')
insert #tmp values (1,'M','1 October 1970')
insert #tmp values (1,'F',null)
-- same sex parents with children
insert #tmp values (2,'F','1 October 1950')
insert #tmp values (2,'F','1 October 1949')
insert #tmp values (2,'M','1 October 1970')
insert #tmp values (2,'F','1 October 1971')
-- M/F group without children
insert #tmp values (3,'M','1 October 1950')
insert #tmp values (3,'F','1 October 1949')
-- single parent with children
insert #tmp values (4,'M','1 October 1950')
insert #tmp values (4,'M','1 October 1970')
insert #tmp values (4,'F','1 October 1971')
-- M/U parents with children
insert #tmp values (5,'M','1 October 1950')
insert #tmp values (5,'U','1 October 1949')
insert #tmp values (5,'M','1 October 1970')
insert #tmp values (5,'F','1 October 1971')
-- F/U parents with children
insert #tmp values (6,'F','1 October 1950')
insert #tmp values (6,'U','1 October 1949')
insert #tmp values (6,'M','1 October 1970')
insert #tmp values (6,'F','1 October 1971')
-- perform a raw sort and key on age
; with cte as (
select *
, row_number() over (partition by FamilyId order by isnull(DOB,getdate()), Gender desc) as RowKey
from #tmp
)
, cte2 as ( -- calculate the age difference between each person and the oldest person
select cte.*
, AgeDiff = datediff(yy,oldest.DOB,isnull(cte.DOB,getdate()))
from cte
join (
select FamilyId
, DOB
from cte
where RowKey = 1
) oldest
on cte.FamilyId = oldest.FamilyId
)
, cte3 as (
select cte2.*
, IsHead = case when AgeDiff > 18 then 0 else 1 end -- assume that persons > 18 years younger than oldest are children
from cte2
)
, cte4 as (
select cte3.*
, GenderSortPriority = case IsHead
when 1 then
case Gender
when 'M' then 1
when 'U' then 2
when 'F' then 3
end
else 4
end
from cte3
)
select cte4.*
, row_number() over (partition by FamilyId order by IsHead desc, GenderSortPriority, isnull(cte4.DOB,getdate())) as HouseholdOrder
from cte4
drop table #tmp
Regards, Iain
November 29, 2012 at 3:08 pm
you may want to look into using the OVER CLAUSE with aggregate functions, I think that could simplify the number of steps you're taking to get to the IsHead column and your Pecking Order.
http://msdn.microsoft.com/en-US/library/ms189461(v=SQL.90).aspx
maybe something like:
;WITH cte AS
(SELECT FamilyID, Gender, DOB,
MIN(DOB) OVER (PARTITION BY FamilyID) AS oldest_dob,
DateDiff(yy,MIN(DOB) OVER (PARTITION BY FamilyID), ISNULL(DOB,GetDate())) AS AgeDiff
FROM #tmp)
SELECT FamilyID, Gender, DOB,
CASE WHEN AgeDiff > 18 THEN 0 ELSE 1 END AS IsHead,
ROW_NUMBER() OVER (PARTITION BY FamilyID ORDER BY
CASE WHEN AgeDiff > 18 THEN 2 ELSE 1 END,
CASE Gender WHEN 'M' THEN 1 WHEN 'U' THEN 2 WHEN 'F' THEN 3 ELSE 4 END,
DOB) HouseholdOrder
FROM cte
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply