-- Following are some commands that help you screen your data for problems --

----------------------------------------------------------------------------------
-- A. COUNT THE NUMBER OF RECORDS BY QUADRAT --
-- This is useful for finding quadrats with no stems (not listed) or quadrats that have too many stems 

-- Counting all stem records, dead or alive
SELECT PlotID,CensusID,QuadratName,COUNT(*) FROM ViewFullTable GROUP BY PlotID,CensusID,QuadratName;

-- Counting all live stem records only
SELECT PlotID,CensusID,QuadratName,count(*) FROM ViewFullTable WHERE status<>'dead' and status<>'stem dead' and status<>'missing' GROUP BY PlotID,CensusID,QuadratName;

-- Counting number of trees
SELECT PlotID,CensusID,QuadratName,count(DISTINCT TreeID) FROM ViewFullTable GROUP BY PlotID,CensusID,QuadratName;

----------------------------------------------------------------------------------
-- B. COUNT THE NUMBER OF DEAD STEMS AND MISSING STEMS BY CENSUS --
SELECT CensusID,status,COUNT(*) FROM ViewFullTable WHERE status IN ('dead','stem dead','missing') GROUP BY CensusID,status;

----------------------------------------------------------------------------------
-- C. CHECK WHETHER THERE ARE ANY TREES LOCATED OUTSIDE THE LIMITS OF THE PLOT OR ANY TREES WITH MISSING COORDINATES --
SELECT * FROM ViewFullTable WHERE X IS NULL OR Y IS NULL;
-- For a 1000 by 500m plot
SELECT * FROM ViewFullTable WHERE PX>=1000 OR PY>=500;

----------------------------------------------------------------------------------
-- D. CHECK WHETHER THERE ARE ANY DUPLICATE TAGS --
SELECT Tag,COUNT(*) FROM Tree GROUP BY Tag HAVING COUNT(*)>1;
-- If StemTags are unique --
SELECT StemTag,COUNT(*) FROM Stem GROUP BY StemTag HAVING COUNT(*)>1;
-- If StemTags are not unique, i.e. starts with 1 for every tree --
SELECT TreeID,StemTag,COUNT(*) FROM Stem GROUP BY TreeID,StemTag HAVING COUNT(*)>1;

----------------------------------------------------------------------------------
-- E. DISPLAY THE RANGE OF THE DATES OF THE CENSUSES --
SELECT CensusID,MIN(ExactDate) AS DateMin, MAX(ExactDate) AS DateMax FROM ViewFullTable GROUP BY CensusID;

----------------------------------------------------------------------------------
-- F. DISPLAY THE LARGEST DBH AND HEIGHT OF MEASURE FOR EACH SPECIES --
-- Can check whether any shrub species have too large of a DBH, for example
SELECT Mnemonic, MAX(DBH), MAX(HOM) FROM ViewFullTable GROUP BY Mnemonic;

----------------------------------------------------------------------------------
-- G. CHECK WHETHER ANY TREES HAVE BOTH A DEAD AND ALIVE STATUS IN ONE CENSUS --
SELECT * FROM ViewFullTable WHERE status='dead' AND (CensusID,TreeID) IN (SELECT CensusID,TreeID FROM ViewFullTable WHERE status<>'dead' AND status<>'stem dead');

----------------------------------------------------------------------------------
-- H. CHECK WHETHER ALL TREES FROM ONE CENSUS ARE ACCOUNTED FOR IN THE NEXT CENSUS --
SELECT * FROM ViewFullTable WHERE CensusID=1 AND StemID NOT IN (SELECT StemID FROM ViewFullTable WHERE CensusID=2);

----------------------------------------------------------------------------------
-- I. CHECK WHETHER ANY TREES THAT WERE DEAD IN ONE CENSUS TURNS OUT ALIVE IN NEXT CENSUS --
SELECT * FROM ViewFullTable WHERE CensusID=2 AND status IN ('dead','stem dead') AND StemID IN (SELECT StemID FROM ViewFullTable WHERE CensusID=3 AND status<>'dead' AND status<>'stem dead');

----------------------------------------------------------------------------------
-- J. CHECK WHETHER THERE ARE ANY TREES WITH EXTREME GROWTH RATES --
-- There are several ways of calculating growth rates, the following are some examples --
-- The following uses the temporary table TempOldTrees and the view table ViewFullTable and compares the dbh from the 2nd and 3rd census

-- To find stems where the growth rate is too high, listing stems where absolute annual growth > 65 mm.
-- NOTE: Growth rates should be calculated only when the HOM is the same in both censuses and both dbhs >= 10 (if in mm).

SELECT A.Tag,A.StemTag,StemID,A.mnemonic,A.QuadratName,B.DBH AS DBH2, A.DBH AS DBH3 
FROM TempOldTrees A JOIN ViewFullTable B USING (StemID) 
WHERE B.CensusID=2 AND A.DBH>=10 AND B.DBH>=10 AND A.HOM=B.HOM 
AND (A.DBH-B.DBH)/((to_days(A.ExactDate)-to_days(B.ExactDate))/365.25) > 65;

-- To find stems where the growth rate is too negative:
-- List stems where annual relative growth <= -0.05 in stems where previous dbh>=50mm AND there is no broken stem codes in 3rd census

SELECT A.Tag,A.StemTag,StemID,A.mnemonic,A.QuadratName,B.DBH AS DBH2, A.DBH AS DBH3,A.codes 
FROM TempOldTrees A JOIN ViewFullTable B USING (StemID) 
WHERE B.CensusID=2 AND A.DBH>=10 AND B.DBH>=50 AND ((A.DBH-B.DBH)/B.DBH)/((to_days(A.ExactDate)-to_days(B.ExactDate))/365.25) <=-0.05;
