Biggest User - Step Three

#!/bin/bash
#
#   YearInReview
#
#   Version 8
#   Date: 2021-05-15
#

[ ! -f "$1"  ] && echo -e "\nError: Database missing\nUsage: bash YearInReview.sh <database_name>\n" && exit 1

sqlite3 "$1" <<-"SQL_EOF"
    DROP VIEW IF EXISTS YearInReview;
    .timer ON

    CREATE VIEW
        YearInReview (ip_addr, Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec)  
    AS
    SELECT 
        ip_addr, 
        MAX(CASE theMonth WHEN '01' THEN totalBytes ELSE NULL END) AS Jan,
        MAX(CASE theMonth WHEN '02' THEN totalBytes ELSE NULL END) AS Feb,
        MAX(CASE theMonth WHEN '03' THEN totalBytes ELSE NULL END) AS Mar,
        MAX(CASE theMonth WHEN '04' THEN totalBytes ELSE NULL END) AS Apr,
        MAX(CASE theMonth WHEN '05' THEN totalBytes ELSE NULL END) AS May,
        MAX(CASE theMonth WHEN '06' THEN totalBytes ELSE NULL END) AS Jun,
        MAX(CASE theMonth WHEN '07' THEN totalBytes ELSE NULL END) AS Jul,
        MAX(CASE theMonth WHEN '08' THEN totalBytes ELSE NULL END) AS Aug,
        MAX(CASE theMonth WHEN '09' THEN totalBytes ELSE NULL END) AS Sep,
        MAX(CASE theMonth WHEN '10' THEN totalBytes ELSE NULL END) AS Oct,
        MAX(CASE theMonth WHEN '11' THEN totalBytes ELSE NULL END) AS Nov,
        MAX(CASE theMonth WHEN '12' THEN totalBytes ELSE NULL END) AS Dec
    FROM
        addr_byMonth
    WHERE
        theDate > DATETIME('now','localtime','-1 year', 'start of month', '-1 second')
    GROUP BY
        ip_addr
    ORDER by
        CAST(substr(ip_addr, 12) AS NUMERIC)
    ;
SQL_EOF

[root@ipfire ~]# sqlite3 /var/spool/pmacct/pmacct_sqlitev1a.db
sqlite> 

.mode columns
.headers ON

SELECT * FROM YearInReview;

sqlite3 -header -cmd ".mode columns" -cmd ".width 16 12 12 12 12 12 12 12 12 12 12 12 12" pmacct_sqlitev1a.db "SELECT * FROM YearInReview WHERE ip_addr='192.168.60.105';"