-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathdemographicsSiblings.sql
More file actions
36 lines (27 loc) · 1.59 KB
/
demographicsSiblings.sql
File metadata and controls
36 lines (27 loc) · 1.59 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
/*
* Copyright (c) 2015-2018 LabKey Corporation
*
* Licensed under the Apache License, Version 2.0: http://www.apache.org/licenses/LICENSE-2.0
*/
SELECT
d1.id,
CASE
WHEN (COALESCE(d1.id.parents.sire, '') = COALESCE(d2.id.parents.sire, '') and COALESCE(d1.id.parents.dam, '') = COALESCE(d2.id.parents.dam, '') AND COALESCE(d1.id.parents.sire, '')!='' AND COALESCE(d1.id.parents.dam, '')!='')
THEN 'Full Sib'
WHEN (COALESCE(d1.id.parents.sire, '') = COALESCE(d2.id.parents.sire, '') AND COALESCE(d1.id.parents.sire, '') != '' AND (COALESCE(d1.id.parents.dam, '') != COALESCE(d2.id.parents.dam, '') OR COALESCE(d1.id.parents.dam, '') = ''))
THEN 'Half-Sib Paternal'
WHEN (COALESCE(d1.id.parents.dam, '') = COALESCE(d2.id.parents.dam, '') AND COALESCE(d1.id.parents.dam, '') != '' AND (COALESCE(d1.id.parents.sire, '') != COALESCE(d2.id.parents.sire, '') OR COALESCE(d1.id.parents.sire, '') = ''))
THEN 'Half-Sib Maternal'
WHEN (COALESCE(d1.id.parents.sire, '') != COALESCE(d2.id.parents.sire, '') and COALESCE(d1.id.parents.dam, '') != COALESCE(d2.id.parents.dam, ''))
THEN 'ERROR'
END AS Relationship,
d2.id AS Sibling,
d2.id.parents.dam AS SiblingDam,
d2.id.parents.sire AS SiblingSire,
d1.qcstate
FROM study.Demographics d1
--removed left join
JOIN study.Demographics d2
ON ((d2.id.parents.sire = d1.id.parents.sire OR d2.id.parents.dam = d1.id.parents.dam) AND d1.id != d2.id)
WHERE d2.id IS NOT NULL AND d1.qcstate.publicdata = true AND d2.qcstate.publicdata = true
-- ) t