SQL Saved search MA quality

These queries depends on the business rules in the country. More queries than the ones below are added to V6.0 as Saved searches with a syntax that works as Saved search
CR where area/calc area have 15% difference

SELECT 
hazreduc_localid,
areasize,
calculated_area
FROM hazreduc Inner Join imsmaenum ON hazreduc.hazreductypeenum_guid = imsmaenum.imsmaenum_guid 
WHERE imsmaenum.enumvalue =  'CompletionReport' AND 
(  (coalesce(areasize,0)  > (coalesce( hazreduc.calculated_area, 0 ) * 1.15 )) OR 
( coalesce( areasize, 0 )  < (coalesce( hazreduc.calculated_area, 0 ) * 0.85) ))
ORDER BY
hazreduc_localid

CR without devices

SELECT hazreduc.hazreduc_localid
FROM hazreduc Inner Join imsmaenum ON hazreduc.hazreductypeenum_guid = imsmaenum.imsmaenum_guid 
Left Join hazreducdeviceinfo ON hazreduc.hazreduc_guid = hazreducdeviceinfo.hazreduc_guid 
WHERE imsmaenum.enumvalue =  'CompletionReport' AND 
hazreducdeviceinfo.hazreduc_guid IS NULL
ORDER BY
hazreduc.hazreduc_localid

CR without polygon/polyline

SELECT DISTINCT hazard.hazard_guid 
FROM hazard Inner Join imsmaenum ON hazard.maareatype_guid = imsmaenum.imsmaenum_guid 
WHERE imsmaenum.enumvalue =  'CHA' AND 
(hazard.hasgeodata = FALSE OR  hazard.hazard_guid NOT IN 
(SELECT DISTINCT hazard.hazard_guid 
FROM hazard Inner Join hazard_has_geospatialinfo ON hazard.hazard_guid = hazard_has_geospatialinfo.hazard_guid 
Inner Join geospatialinfo ON hazard_has_geospatialinfo.geospatialinfo_guid = geospatialinfo.geospatialinfo_guid 
Inner Join imsmaenum ON geospatialinfo.shapeenum_guid = imsmaenum.imsmaenum_guid 
WHERE imsmaenum.enumvalue LIKE  'Poly%' ))

Open Hazards with links to HazRed or QM

SELECT DISTINCT hazard_localid
FROM hazard Inner Join imsmaenum ON hazard.status_guid = imsmaenum.imsmaenum_guid 
Inner Join link ON hazard.hazard_guid = link.from_guid 
WHERE imsmaenum.enumvalue =  'Active' AND (link.to_link_type =  'HAZARD_REDUCTION' OR link.to_link_type =  'QA' )
ORDER BY
hazard_localid

{{#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 ma quality

|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 ma quality

|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 ma quality

|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 ma quality

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

}}}}}}