SQL Saved search Duplicate ID: Difference between revisions

Latest revision as of 12:37, 24 July 2014

These queries finds duplicates. They are added to V6.0 as Saved searches with a syntax that works as Saved search
Accident ID

SELECT
accident.accident_localid,
Count(accident.accident_guid)
FROM
accident
GROUP BY
accident.accident_localid
HAVING
Count(accident.accident_guid) >  '1'
ORDER BY
accident.accident_localid ASC

Hazard ID

SELECT
hazard.hazard_localid,
Count(hazard.hazard_guid)
FROM
hazard
GROUP BY
hazard.hazard_localid
HAVING
Count(hazard.hazard_guid) >  '1'
ORDER BY
hazard.hazard_localid ASC

HazRed ID

SELECT
hazreduc.hazreduc_localid,
Count(hazreduc.hazreduc_guid)
FROM
hazreduc
GROUP BY
hazreduc.hazreduc_localid
HAVING
Count(hazreduc.hazreduc_guid) >  '1'
ORDER BY
hazreduc.hazreduc_localid ASC

Location ID

SELECT
location.location_localid,
Count(location.location_guid)
FROM
location
GROUP BY
location.location_localid
HAVING
Count(location.location_guid) >  '1'
ORDER BY
location.location_localid ASC

Location name (not a Saved search)

SELECT
trim(location.locationname),
Count(location.location_guid)
FROM
location
GROUP BY
trim(location.locationname)
HAVING
Count(location.location_guid) >  '1'
ORDER BY
trim(location.locationname) ASC

MRE ID

SELECT
mre.mre_localid,
Count(mre.mre_guid)
FROM
mre
GROUP BY
mre.mre_localid
HAVING
Count(mre.mre_guid) >  '1'
ORDER BY
mre.mre_localid ASC

QA ID

SELECT
qa.qa_localid,
Count(qa.qa_guid)
FROM
qa
GROUP BY
qa.qa_localid
HAVING
Count(qa.qa_guid) >  '1'
ORDER BY
qa.qa_localid ASC

Task ID (not a Saved search)

SELECT
task.localid,
Count(task.guid)
FROM
task
GROUP BY
task.localid
HAVING
Count(task.guid) >  '1'
ORDER BY
task.localid ASC

Victim ID

SELECT
victim.victim_localid,
Count(victim.victim_guid)
FROM
victim
GROUP BY
victim.victim_localid
HAVING
Count(victim.victim_guid) >  '1'
ORDER BY
victim.victim_localid ASC

Victim First name, Last name and Date of birth This query may need to be adapted if the country use CDFs or Age

SELECT DISTINCT d.victim_guid 
FROM victim AS b INNER JOIN victim AS d ON TRIM(UPPER(b.familyname)) = TRIM(UPPER(d.familyname)) AND 
TRIM(UPPER(b.givenname)) = TRIM(UPPER(d.givenname)) AND 
b.dateofbirth = d.dateofbirth 
WHERE b.victim_guid <> d.victim_guid

{{#switch:|subgroup|child=|none=|#default=

}}{{#if:|}}{{#if:Upgrade Process|<td style="text-align:left;border-left-width:2px;border-left-style:solid;|{{#if:|}}}}{{#if:|{{#if:IMSMA Hub{{#switch:{{#if:|{{{border}}}|child}}|subgroup|child=|none=|#default=

}}{{#ifeq:|Template|{{#ifeq:{{#if:|{{{border}}}|child}}|child||{{#ifeq:{{#if:|{{{border}}}|child}}|subgroup||{{#switch:sql saved search duplicate id

|doc
|sandbox
|testcases =
|#default = {{#switch:
 |plainlist
 |hlist
 |hlist hnum
 |hlist vcard
 |vcard hlist = 
 |#default = 
 }}
}}

}}}}}}|}}{{#if:|{{{group2}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list2}}}

}}{{#if:|{{#if:IMSMA Hub{{#switch:{{#if:|{{{border}}}|child}}|subgroup|child=|none=|#default=

}}{{#ifeq:|Template|{{#ifeq:{{#if:|{{{border}}}|child}}|child||{{#ifeq:{{#if:|{{{border}}}|child}}|subgroup||{{#switch:sql saved search duplicate id

|doc
|sandbox
|testcases =
|#default = {{#switch:
 |plainlist
 |hlist
 |hlist hnum
 |hlist vcard
 |vcard hlist = 
 |#default = 
 }}
}}

}}}}}}|}}{{#if:|{{{group3}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list3}}}

}}{{#if:|{{#if:|{{{group4}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list4}}}

}}{{#if:|{{#if:|{{{group5}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list5}}}

}}{{#if:|{{#if:|{{{group6}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list6}}}

}}{{#if:|{{#if:|{{{group7}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list7}}}

}}{{#if:|{{#if:|{{{group8}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list8}}}

}}{{#if:|{{#if:|{{{group9}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list9}}}

}}{{#if:|{{#if:|{{{group10}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list10}}}

}}{{#if:|{{#if:|{{{group11}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list11}}}

}}{{#if:|{{#if:|{{{group12}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list12}}}

}}{{#if:|{{#if:|{{{group13}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list13}}}

}}{{#if:|{{#if:|{{{group14}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list14}}}

}}{{#if:|{{#if:|{{{group15}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list15}}}

}}{{#if:|{{#if:|{{{group16}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list16}}}

}}{{#if:|{{#if:|{{{group17}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list17}}}

}}{{#if:|{{#if:|{{{group18}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list18}}}

}}{{#if:|{{#if:|{{{group19}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list19}}}

}}{{#if:|{{#if:|{{{group20}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list20}}}

}}{{#if:|{{#if:IMSMA Hub{{#switch:{{#if:|{{{border}}}|child}}|subgroup|child=|none=|#default=

}}{{#ifeq:|Template|{{#ifeq:{{#if:|{{{border}}}|child}}|child||{{#ifeq:{{#if:|{{{border}}}|child}}|subgroup||{{#switch:sql saved search duplicate id

|doc
|sandbox
|testcases =
|#default = {{#switch:
 |plainlist
 |hlist
 |hlist hnum
 |hlist vcard
 |vcard hlist = 
 |#default = 
 }}
}}

}}}}}}|}}

{{{below}}}

}}{{#switch:|subgroup|child=

|none=|#default=}}{{#ifeq:|Template|{{#ifeq:|child||{{#ifeq:|subgroup||{{#switch:sql saved search duplicate id

|doc
|sandbox
|testcases =
|#default = {{#switch:hlist
 |plainlist
 |hlist
 |hlist hnum
 |hlist vcard
 |vcard hlist = 
 |#default = 
 }}
}}

}}}}}}

@@ Line 126: / Line 126: @@
 victim.victim_localid ASC
 </pre>
-'''Victim First name, Last name and Date of birth''' This query may need to be adopted if the country use CDFs or ''Age''
+'''Victim First name, Last name and Date of birth''' This query may need to be adapted if the country use CDFs or ''Age''
 <pre>
-SELECT
+SELECT DISTINCT d.victim_guid
-trim(victim.givenname),
+FROM victim AS b INNER JOIN victim AS d ON TRIM(UPPER(b.familyname)) = TRIM(UPPER(d.familyname)) AND
-trim(victim.familyname),
+TRIM(UPPER(b.givenname)) = TRIM(UPPER(d.givenname)) AND
-victim.dateofbirth,
+b.dateofbirth = d.dateofbirth
-Count(victim.victim_guid)
+WHERE b.victim_guid <> d.victim_guid</pre>
-FROM
+{{NavBox Upgrade}}
-victim
-GROUP BY
-trim(victim.givenname),
-trim(victim.familyname),
-victim.dateofbirth
-HAVING
-Count(victim.victim_guid) >  '1'
-ORDER BY
-trim(victim.givenname) ASC,
-trim(victim.familyname) ASC,
-victim.dateofbirth ASC
-</pre>
 [[Category:NoPublic]]
 [[Category:SQL Queries]]

SQL Saved search Duplicate ID: Difference between revisions

Latest revision as of 12:37, 24 July 2014

Navigation menu

Search