I have the following table structure:TablesstocktakebinlinestocktakebinlinebatchA stocktakebinline has a one to many relationship with stocktakebinlinebatch. A line consists of many batches.Columnsstocktakebinlinelineid | reference-------+----------1      | Product A2      | Product B3      | Product Cstocktakebinlinebatchid | lineid | batchnumber | instock | countedstocklevel | counteddate---+--------+-------------+---------+-------------------+------------1  | 1      | Batch A     | 3.0     | null              | null2  | 1      | Batch B     | 1.0     | 5.0               | 2017-02-15 10:143  | 2      | Batch C     | 2.0     | 2.0               | 2017-02-15 11:304  | 2      | Batch D     | 0.0     | 0.0               | 2017-02-15 11:315  | 2      | Batch E     | 6.0     | null              | null6  | 3      | Batch F     | 5.0     | 5.0               | 2017-02-15 13:58I have omitted some columns for brevity. Also, the date fields are valid psql  dates and are not strings, I have just written them that way so they are easier to read.I am trying to calculate how many lines have been counted every hour from a given start date to now. The output will look like this:counted | date--------+--------0       | 2017-02-15 09:001       | 2017-02-15 10:001       | 2017-02-15 11:000       | 2017-02-15 12:001       | 2017-02-15 13:000       | 2017-02-15 14:00A line is considered counted if any of it's batches have been counted.To achieve this, I have written the following query:with tempCountedHours as(    select        count(*) as counted,        b.date    from stocktakebinline l    join    (        --Gets minutes and how many where counted in that hour for all lines.        select            lineid,            date_trunc('hour', counteddate) as date        from stocktakebinlinebatch        where            --Where the stock level is over 0 or if it is 0,            --then only zero stocks that have been counted.            counteddate is not null            and (instock > 0 or (instock = 0 and countedstocklevel > 0))        group by            lineid,            date_trunc('hour', counteddate)    ) b on l.id = b.lineid    group by        b.date    order by        b.date asc)select    *from tempCountedHoursunion(    select        0 as counted,        d.date    from    (        --Get all of the hours between the created date of the stock take        --and right now, except those that were counted.        select            date_trunc('hour', d) as date        from generate_series(            '2017-02-15 09:00',            now(),            '1 hour'::interval        ) d        except        select date from tempCountedHours    ) d)order by    date asc;To try and make some sense of this, I am using the following logic:Get all of the hours where a line has been counted (and how many have been counted).Get all of the hours between the two dates and exclude ones which have lines that are countedIn the end, union the two data sets and show 0 counted for hours that have not been counted and display the counted amount for hours that have been counted.This query works fine, however it's quite large. Is there a way I can simplify this query?
Get the total counted stock take lines for each hour between two dates
sql;postgresql