Report: Number of Deaths in year-ranges

From TNG_Wiki
Jump to: navigation, search

This report lists (as written below) the number of Deaths in 10-year ranges between 1500 - 1949.
It should be quite easy to adjust the ranges and/or select a place or an area.
The resulting table can f.ex. be used to find increased number of deaths during f.ex. the Salem witch trials or the "Little Ice Age".

The original SQL was written by Giorgos Betsos and was found here: StackOverflow.com
The report was uploaded by Erik Hoppe

NOTE: Henny Savenije has posted a similar report here: Report:_individuals:_death_frequency_by_decades
The differences:

  • You are not limited to equal ranges but can use different ranges for different periods of time: A range can be a single day if you like.
  • You can also set the earliest and latest dates to whatever you like.
  • I don't have a graph.


Installation

  • Go to the Reports section on the Administration page and click on the Add New tab.
  • Fill in the Report name and remember to click on the Active button if you want your users to have access to it.
  • Scroll to the bottom where it says "OR Leave Display, Criteria and Sort fields blank and enter direct SQL SELECT statement here:"
    and copy and paste the Mysql code below.


SELECT x.Year, COALESCE(Total, 0) AS Total
FROM (
  SELECT "Bef. 1500" AS Year 
  UNION SELECT "1500 - 1509"
  UNION SELECT "1510 - 1519"
  UNION SELECT "1520 - 1529"
  UNION SELECT "1530 - 1539"
  UNION SELECT "1540 - 1549"
  UNION SELECT "1550 - 1559"
  UNION SELECT "1560 - 1569"
  UNION SELECT "1570 - 1579"
  UNION SELECT "1580 - 1589"
  UNION SELECT "1590 - 1599"
  UNION SELECT "1600 - 1609"
  UNION SELECT "1610 - 1619"
  UNION SELECT "1620 - 1629"
  UNION SELECT "1630 - 1639"
  UNION SELECT "1640 - 1649"
  UNION SELECT "1650 - 1659"
  UNION SELECT "1660 - 1669"
  UNION SELECT "1670 - 1679"
  UNION SELECT "1680 - 1689"
  UNION SELECT "1690 - 1699"
  UNION SELECT "1700 - 1709"
  UNION SELECT "1710 - 1719"
  UNION SELECT "1720 - 1729"
  UNION SELECT "1730 - 1739"
  UNION SELECT "1740 - 1749"
  UNION SELECT "1750 - 1759"
  UNION SELECT "1760 - 1769"
  UNION SELECT "1770 - 1779"
  UNION SELECT "1780 - 1789"
  UNION SELECT "1790 - 1799"
  UNION SELECT "1800 - 1809"
  UNION SELECT "1810 - 1819"
  UNION SELECT "1820 - 1829"
  UNION SELECT "1830 - 1839"
  UNION SELECT "1840 - 1849"
  UNION SELECT "1850 - 1859"
  UNION SELECT "1860 - 1869"
  UNION SELECT "1870 - 1879"
  UNION SELECT "1880 - 1889"
  UNION SELECT "1890 - 1899"
  UNION SELECT "1900 - 1909"
  UNION SELECT "1910 - 1919"
  UNION SELECT "1920 - 1929"
  UNION SELECT "1930 - 1939"
  UNION SELECT "1940 - 1949"
  UNION SELECT "1950 and later" ) x
LEFT JOIN (  
   SELECT
      CASE when deathdatetr > "0000-00-00" and deathdatetr < "1500-00-00" then "Bef. 1500"

           when deathdatetr >= "1500-00-00" and deathdatetr < "1510-00-00" then "1500 - 1509"
           when deathdatetr >= "1510-00-00" and deathdatetr < "1520-00-00" then "1510 - 1519"
           when deathdatetr >= "1520-00-00" and deathdatetr < "1530-00-00" then "1520 - 1529"
           when deathdatetr >= "1530-00-00" and deathdatetr < "1540-00-00" then "1530 - 1539"
           when deathdatetr >= "1540-00-00" and deathdatetr < "1550-00-00" then "1540 - 1549"
           when deathdatetr >= "1550-00-00" and deathdatetr < "1560-00-00" then "1550 - 1559"
           when deathdatetr >= "1560-00-00" and deathdatetr < "1570-00-00" then "1560 - 1569"
           when deathdatetr >= "1570-00-00" and deathdatetr < "1580-00-00" then "1570 - 1579"
           when deathdatetr >= "1580-00-00" and deathdatetr < "1590-00-00" then "1580 - 1589"
           when deathdatetr >= "1590-00-00" and deathdatetr < "1600-00-00" then "1590 - 1599"

           when deathdatetr >= "1600-00-00" and deathdatetr < "1610-00-00" then "1600 - 1609"
           when deathdatetr >= "1610-00-00" and deathdatetr < "1620-00-00" then "1610 - 1619"
           when deathdatetr >= "1620-00-00" and deathdatetr < "1630-00-00" then "1620 - 1629"
           when deathdatetr >= "1630-00-00" and deathdatetr < "1640-00-00" then "1630 - 1639"
           when deathdatetr >= "1640-00-00" and deathdatetr < "1650-00-00" then "1640 - 1649"
           when deathdatetr >= "1650-00-00" and deathdatetr < "1660-00-00" then "1650 - 1659"
           when deathdatetr >= "1660-00-00" and deathdatetr < "1670-00-00" then "1660 - 1669"
           when deathdatetr >= "1670-00-00" and deathdatetr < "1680-00-00" then "1670 - 1679"
           when deathdatetr >= "1680-00-00" and deathdatetr < "1690-00-00" then "1680 - 1689"
           when deathdatetr >= "1690-00-00" and deathdatetr < "1700-00-00" then "1690 - 1699"

           when deathdatetr >= "1700-00-00" and deathdatetr < "1710-00-00" then "1700 - 1709"
           when deathdatetr >= "1710-00-00" and deathdatetr < "1720-00-00" then "1710 - 1719"
           when deathdatetr >= "1720-00-00" and deathdatetr < "1730-00-00" then "1720 - 1729"
           when deathdatetr >= "1730-00-00" and deathdatetr < "1740-00-00" then "1730 - 1739"
           when deathdatetr >= "1740-00-00" and deathdatetr < "1750-00-00" then "1740 - 1749"
           when deathdatetr >= "1750-00-00" and deathdatetr < "1760-00-00" then "1750 - 1759"
           when deathdatetr >= "1760-00-00" and deathdatetr < "1770-00-00" then "1760 - 1769"
           when deathdatetr >= "1770-00-00" and deathdatetr < "1780-00-00" then "1770 - 1779"
           when deathdatetr >= "1780-00-00" and deathdatetr < "1790-00-00" then "1780 - 1789"
           when deathdatetr >= "1790-00-00" and deathdatetr < "1800-00-00" then "1790 - 1799"

           when deathdatetr >= "1800-00-00" and deathdatetr < "1810-00-00" then "1800 - 1809"
           when deathdatetr >= "1810-00-00" and deathdatetr < "1820-00-00" then "1810 - 1819"
           when deathdatetr >= "1820-00-00" and deathdatetr < "1830-00-00" then "1820 - 1829"
           when deathdatetr >= "1830-00-00" and deathdatetr < "1840-00-00" then "1830 - 1839"
           when deathdatetr >= "1840-00-00" and deathdatetr < "1850-00-00" then "1840 - 1849"
           when deathdatetr >= "1850-00-00" and deathdatetr < "1860-00-00" then "1850 - 1859"
           when deathdatetr >= "1860-00-00" and deathdatetr < "1870-00-00" then "1860 - 1869"
           when deathdatetr >= "1870-00-00" and deathdatetr < "1880-00-00" then "1870 - 1879"
           when deathdatetr >= "1880-00-00" and deathdatetr < "1890-00-00" then "1880 - 1889"
           when deathdatetr >= "1890-00-00" and deathdatetr < "1900-00-00" then "1890 - 1899"

           when deathdatetr >= "1900-00-00" and deathdatetr < "1910-00-00" then "1900 - 1909"
           when deathdatetr >= "1910-00-00" and deathdatetr < "1920-00-00" then "1910 - 1919"
           when deathdatetr >= "1920-00-00" and deathdatetr < "1930-00-00" then "1920 - 1929"
           when deathdatetr >= "1930-00-00" and deathdatetr < "1940-00-00" then "1930 - 1939"
           when deathdatetr >= "1940-00-00" and deathdatetr < "1950-00-00" then "1940 - 1949"
           when deathdatetr >= "1950-00-00" then "1950 and later"

      END AS Year,
      COUNT(*) as Total
   FROM tng_people
   GROUP BY 1 ) y ON x.Year = y.Year

Then hit Save and Exit and click on the tick button next to the report name to test the result.

Note that this assumes the default naming of tables in TNG. If yours is different, then you will need to adjust the table name in the "FROM" line.


Example

This shows the first few years of a typical report.

# Year Total
1 Bef. 1500 20
2 1500 - 1509 13
3 1510 - 1519 19
4 1520 - 1529 32
5 1530 - 1539 43
6 1540 - 1549 32


Customising the report

It's easy to use the ranges you want and/or count for a selected place or area.

  • To adjust the ranges, you simply edit them and/or delete or insert lines.
    Note that the ranges in the top part must correspond to the ranges in the bottom part:
    Both the number of them and the text inside quotes must correspond exactly.
  • To add a specific place you add a
    WHERE deathplace = "The Placename As Recorded"
    between the FROM and GROUP BY lines at the very bottom. For example:
    WHERE deathplace = "Salem, Massachusetts, USA"
  • To add an area you add a
    WHERE deathplace LIKE "%A Common Placename As Recorded%"
    between the FROM and GROUP BY lines at the very bottom. For example:
    WHERE deathplace LIKE "%USA%"