# 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 > 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 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:

1. Get all of the hours where a line has been counted (and how many have been counted).
2. Get all of the hours between the two dates and exclude ones which have lines that are counted
3. 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 > 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;


Not sure if I over simplified this or not. I added variables so that maybe you won't have to reiterate over checks that have already been done. This is untested, but hopefully you can use CTE and MERGE and that the idea can steer you in the right direction.

DECLARE @StartDate as DATETIME
DECLARE @EndDate as DATETIME

SET @StartDate = '2017-02-15 09:00'
SET @EndDate = NOW()

WITH HOUR_COUNT AS
(
SELECT 0 AS counted, @StartDate AS counteddate
UNION ALL
FROM HOURS
WHERE DATEADD(HOUR, 1, counteddate) <= @EndDate
)

SELECT CASE COUNT(*) > 1 THEN 1 ELSE 1 END AS counted,
DATEADD(YEAR, DATEPART(YEAR, counteddate) - 1900, 0)))) as date
INTO #tmpCounts
FROM stocktakebinline
WHERE (counteddate IS NOT NULL AND counteddate BETWEEN @StartDate AND @EndDate)
AND (instock > 0 OR (instock = 0 AND countedstocklevel > 0))
GROUP BY
DATEPART(YEAR, counteddate),
DATEPART(MONTH, counteddate),
DATEPART(DAY, counteddate),
DATEPART(HOUR, counteddate)

MERGE INTO HOUR_COUNT AS target
USING #tmpCounts AS source
ON target.counteddate = source.counteddate
WHEN MATCHED THEN
UPDATE SET target.counted = source.counted

SELECT * FROM HOUR_COUNT