3
\$\begingroup\$

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?

\$\endgroup\$
2
\$\begingroup\$

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;
\$\endgroup\$
1
\$\begingroup\$

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
    SELECT DATEADD(HOUR, 1, counteddate)
    FROM HOURS
    WHERE DATEADD(HOUR, 1, counteddate) <= @EndDate
)

SELECT CASE COUNT(*) > 1 THEN 1 ELSE 1 END AS counted,
        DATEADD(HOUR, DATEPART(HOUR, counteddate) - 1
            DATEADD(DAY, DATEPART(DAY, counteddate) - 1, 
                DATEADD(MONTH, DATEPART(MONTH, counteddate) - 1,
                    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
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.