Get the total counted stock take lines for each hour between two dates

I have the following table structure:

Tables

stocktakebinline
stocktakebinlinebatch

A stocktakebinline has a one to many relationship with stocktakebinlinebatch. A line consists of many batches.

Columns

stocktakebinline

lineid | reference
-------+----------
1      | Product A
2      | Product B
3      | Product C

stocktakebinlinebatch

id | lineid | batchnumber | instock | countedstocklevel | counteddate
---+--------+-------------+---------+-------------------+------------
1  | 1      | Batch A     | 3.0     | null              | null
2  | 1      | Batch B     | 1.0     | 5.0               | 2017-02-15 10:14
3  | 2      | Batch C     | 2.0     | 2.0               | 2017-02-15 11:30
4  | 2      | Batch D     | 0.0     | 0.0               | 2017-02-15 11:31
5  | 2      | Batch E     | 6.0     | null              | null
6  | 3      | Batch F     | 5.0     | 5.0               | 2017-02-15 13:58

I 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:00
1       | 2017-02-15 10:00
1       | 2017-02-15 11:00
0       | 2017-02-15 12:00
1       | 2017-02-15 13:00
0       | 2017-02-15 14:00

A 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

It's probably easier to switch to a Left Join instead of Union/Except:

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
    COALESCE(tch.counted, 0), -- set missing data to zero
    d.date
from
 (
    --Get all of the hours between the created date of the stock take
    --and right now
    select
        date_trunc('hour', d) as date
    from generate_series(
        '2017-02-15 09:00',
        now(),
        '1 hour'::interval
 ) d
left join tempCountedHours as tch
on d.date = tch.date
order by
    d.date asc;

StackExchange Code Review Q#155445, answer score: 2