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 zero stocks that have been counted.
            counteddate is not null
            and (instock &gt; 0 or (instock = 0 and countedstocklevel &gt; 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 tempCountedHours
union
(
    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 counted
In 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?
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 &gt; 0 or (instock = 0 and countedstocklevel &gt; 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;