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
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100

SELECT P.project_id,
       P.fiscalyearcompleted,
       PO.featureid,
       PO.shape,
       Concat(P.title, '_', A.actiondescription) AS Project_Name,
       P.status                                  AS Effort_Status,
       MD.subactivity                            AS Activity,
       MD.subactivity_method                     AS Subactivity,
       CASE
         WHEN PC.categoryother = 'Fire Rehab' THEN 'Yes'
         ELSE 'No'
       END                                       AS Post_Fire,
       CASE
         WHEN A.actiondescription IN (
              'Seeding (primary)', 'Seeding (secondary/shrub)'
                                     ) THEN 'Native/Non-Native Mixed'
       END                                       AS Seeding_Type,
       P.projectstartdate                        AS Start_Date,
       P.projectenddate                          AS End_Date,
       'Acres'                                   AS Metric_Type,
       -- Area left blank, must be filled in by GIS software
       NULL                                      AS Metric_Value,
       NULL                                      AS GIS_Acres,
       '9'                                       AS Implementing_Party,
       '116'                                     AS Office,
       'Tyler Thompson'                          AS Created_By,
       '0'                                       AS NumberDocs,
       /* NumberCollaborators must include DNR, this case statement is
       checking if DNR is present and if it is not then adding +1 */
       CASE
         WHEN Isnull(U.state, 0) = 0 THEN Isnull(U.notstate, 0 ) + 1
         ELSE Isnull(U.notstate, 0)
       END                                       AS NumberCollaborators,
       L.lnum                                    AS NumberLandOwners,
       '3'                                       AS Effectiveness_Status,
       Isnull(numberthreatsaddress, 0)           AS NumberThreatsAddress,
       CASE
         WHEN PC.categoryother = 'Sagebrush Restoration' THEN 'Yes'
         ELSE 'No'
       END                                       AS SagebrushRestoration
FROM   wri.dbo.areaaction AS A
       LEFT JOIN wri.dbo.methods_domain AS MD
              ON MD.actionid = A.actionid
       LEFT JOIN wri.dbo.poly AS PO
              ON PO.featureid = A.featureid
       LEFT JOIN (SELECT featureid,
                         -- counting number of landowners in each feature
                         Count(*) AS lnum
                  FROM   wri.dbo.landowner
                  GROUP  BY featureid) AS L
              ON PO.featureid = L.featureid
       LEFT JOIN wri.dbo.project AS P
              ON PO.project_id = P.project_id
       LEFT JOIN (SELECT project_fk,
                         -- only counting DNR to check if it is present 
                         Count(DISTINCT ( CASE
                                            WHEN agency = 9 THEN agency
                                          END )) AS state,
                         -- counting all agencies including DNR 
                         Count(DISTINCT agency)  AS notstate
                  FROM   (SELECT project_fk,
                                 user_fk,
                                 ced_code AS Agency
                          FROM   wri.dbo.contributor
                                 RIGHT JOIN (SELECT ced_code,
                                                    user_id
                                             FROM   wri.dbo.users AS X
                                 LEFT JOIN wri.dbo.agency_domain AS
                                           Y
                                        ON X.agency =
                                            Y.wri_agencyid) AS
                                            domain
                                         ON user_id = user_fk) AS users
                  GROUP  BY project_fk) AS U
              ON U.project_fk = P.project_id
       LEFT JOIN wri.dbo.projectcategoryother AS PC
              ON P.project_id = PC.project_id
       LEFT JOIN (SELECT project_id,
                         -- counting distinct threats per project
                         Count(DISTINCT ced_code) AS NumberThreatsAddress
                  FROM   wri.dbo.species
                         LEFT JOIN (SELECT TD.threat_id,
                                           ced_code,
                                           species_id,
                                           impact_id
                                    FROM   wri.dbo.species_threat_impact AS TI
                                           LEFT JOIN wri.dbo.threats_domain AS
                                                     TD
                                                  ON TD.threat_id = TI.threat_id
                                    -- CED must be > 0 because 0s are not threats, but are listed
                                    WHERE  ced_code > 0) AS TITD
                                ON species.threat_id = TITD.threat_id
                  WHERE  project_id IS NOT NULL
                         AND ced_code IS NOT NULL
                  GROUP  BY project_id) AS T
              ON T.project_id = P.project_id
       LEFT JOIN wri.dbo.projectcategoryfunding AS PCF
              ON PCF.project_id = P.project_id
-- Filter to only WRI projects
WHERE  categoryfundingid = 1