Get count of how many batch from parcels made in every hour of the day-CodePudding

I am very confused with DISTINCT, GROUP BY clauses and can't get my head around what I need to do to get my desired values.

My table (simplified)

CREATE TABLE manufacturing(
    [id] [int] IDENTITY(1,1) NOT NULL,
    [dt] [datetime] NOT NULL,
    [parcel_id] [int] NULL,
    [batch_no] [int] NULL,
    [tn] [int] NULL
GO

Every parcel_id has batch_no 1 to 'anything' and batch_no has tn 1 to 'anything' so they are not fixed max numbers.

When a machine completes a step (tn) it writes it to DB

I need to get how many batches there in db for every hour (from 0 to 24) A batch can start at 15:00 and finish at 17:00 I need to write that to 17.

What I tried is

SELECT COUNT(parcel_batch) AS batch_count, dt
    FROM (SELECT parcel_batch as parcel_batch, MAX(dt) as dt
        FROM (SELECT DISTINCT(CONVERT(varchar, parcel_id)   CONVERT(varchar, batch_no)) as parcel_batch, DATEPART(HOUR, dt) as dt
            FROM manufacturing WHERE DATEPART(YEAR, dt) = 2021 AND DATEPART(MONTH, dt) = 09 AND DATEPART(DAY, dt) = 15 AND fabrika = 2) as qq group by parcel_batch) as new group by dt

What I tried at the 1st quary (Line 3) was combining parcel and batch can give me unique number so I can DISTINCT. It seemed like it worked at first, with my test values. Then I used it on real db (which is much much more large) it doesn't give correctly.

I get my total batch for today like this

SELECT DISTINCT rh.parcel_id, CONVERT(varchar, MIN(rh.dt)), CONVERT(varchar, MAX(rh.dt)), MAX(rh.batch_no) FROM manufacturing as rh
WHERE DATEPART(YEAR, rh.dt) = DATEPART(YEAR, GETDATE())
AND DATEPART(MONTH, rh.dt) = DATEPART(MONTH, GETDATE())
AND DATEPART(DAY, rh.dt) = DATEPART(DAY, GETDATE())
GROUP BY rh.parcel_id

But really couldn't figure out how to get how many batches for every hour since I couldn't get my head around DISTINCT (I guess it takes everything after SELECT, I thought only 1st one or something) or GROUP BY

Thanks in advance.

Update:

This is what my data looks like.

BEGIN TRANSACTION
    INSERT INTO manufacturing VALUES(CONVERT(datetime,'2021-09-15 00:03:00.000'), 290, 1, 45)
    INSERT INTO manufacturing VALUES(CONVERT(datetime,'2021-09-15 00:05:00.000'), 290, 1, 48)
    INSERT INTO manufacturing VALUES(CONVERT(datetime,'2021-09-15 00:34:00.000'), 290, 2, 44)
    INSERT INTO manufacturing VALUES(CONVERT(datetime,'2021-09-15 00:47:00.000'), 291, 1, 58)
    INSERT INTO manufacturing VALUES(CONVERT(datetime,'2021-09-15 00:54:00.000'), 291, 1, 43)
    INSERT INTO manufacturing VALUES(CONVERT(datetime,'2021-09-15 00:58:00.000'), 291, 1, 43)
    INSERT INTO manufacturing VALUES(CONVERT(datetime,'2021-09-15 01:02:00.000'), 291, 1, 2)
    INSERT INTO manufacturing VALUES(CONVERT(datetime,'2021-09-15 01:08:00.000'), 291, 1, 41)
    INSERT INTO manufacturing VALUES(CONVERT(datetime,'2021-09-15 01:24:00.000'), 291, 2, 46)
    INSERT INTO manufacturing VALUES(CONVERT(datetime,'2021-09-15 01:36:00.000'), 291, 2, 47)
    INSERT INTO manufacturing VALUES(CONVERT(datetime,'2021-09-15 01:58:00.000'), 291, 3, 2)
    INSERT INTO manufacturing VALUES(CONVERT(datetime,'2021-09-15 02:04:00.000'), 291, 3, 42)
    INSERT INTO manufacturing VALUES(CONVERT(datetime,'2021-09-15 02:23:00.000'), 291, 3, 52)
    INSERT INTO manufacturing VALUES(CONVERT(datetime,'2021-09-15 02:54:00.000'), 292, 1, 57)
    INSERT INTO manufacturing VALUES(CONVERT(datetime,'2021-09-15 02:55:00.000'), 292, 1, 53)
    INSERT INTO manufacturing VALUES(CONVERT(datetime,'2021-09-15 02:59:00.000'), 292, 2, 48)
    INSERT INTO manufacturing VALUES(CONVERT(datetime,'2021-09-15 03:12:00.000'), 292, 2, 45)
    INSERT INTO manufacturing VALUES(CONVERT(datetime,'2021-09-15 03:15:00.000'), 292, 2, 44)
    INSERT INTO manufacturing VALUES(CONVERT(datetime,'2021-09-15 03:22:00.000'), 292, 3, 51)
    INSERT INTO manufacturing VALUES(CONVERT(datetime,'2021-09-15 03:46:00.000'), 293, 1, 58)
    INSERT INTO manufacturing VALUES(CONVERT(datetime,'2021-09-15 03:55:00.000'), 293, 1, 2)
    INSERT INTO manufacturing VALUES(CONVERT(datetime,'2021-09-15 04:24:00.000'), 293, 2, 2)
    INSERT INTO manufacturing VALUES(CONVERT(datetime,'2021-09-15 04:33:00.000'), 293, 2, 2)
    INSERT INTO manufacturing VALUES(CONVERT(datetime,'2021-09-15 04:45:00.000'), 294, 1, 2)
    INSERT INTO manufacturing VALUES(CONVERT(datetime,'2021-09-15 04:46:00.000'), 294, 1, 57)
    INSERT INTO manufacturing VALUES(CONVERT(datetime,'2021-09-15 04:58:00.000'), 294, 1, 1)
    INSERT INTO manufacturing VALUES(CONVERT(datetime,'2021-09-15 05:23:00.000'), 295, 1, 1)
    INSERT INTO manufacturing VALUES(CONVERT(datetime,'2021-09-15 05:32:00.000'), 295, 1, 53)
    INSERT INTO manufacturing VALUES(CONVERT(datetime,'2021-09-15 05:35:00.000'), 295, 2, 51)
    INSERT INTO manufacturing VALUES(CONVERT(datetime,'2021-09-15 05:38:00.000'), 295, 3, 1)
    INSERT INTO manufacturing VALUES(CONVERT(datetime,'2021-09-15 05:43:00.000'), 296, 1, 52)
    INSERT INTO manufacturing VALUES(CONVERT(datetime,'2021-09-15 05:47:00.000'), 296, 1, 52)
    INSERT INTO manufacturing VALUES(CONVERT(datetime,'2021-09-15 05:49:00.000'), 296, 1, 52)
    INSERT INTO manufacturing VALUES(CONVERT(datetime,'2021-09-15 06:45:00.000'), 296, 1, 2)
    INSERT INTO manufacturing VALUES(CONVERT(datetime,'2021-09-15 06:47:00.000'), 296, 2, 2)
    INSERT INTO manufacturing VALUES(CONVERT(datetime,'2021-09-15 06:53:00.000'), 296, 2, 2)
    INSERT INTO manufacturing VALUES(CONVERT(datetime,'2021-09-15 06:56:00.000'), 296, 2, 3)
    INSERT INTO manufacturing VALUES(CONVERT(datetime,'2021-09-15 06:58:00.000'), 296, 3, 7)
    INSERT INTO manufacturing VALUES(CONVERT(datetime,'2021-09-15 07:26:00.000'), 296, 3, 1)
    INSERT INTO manufacturing VALUES(CONVERT(datetime,'2021-09-15 07:35:00.000'), 296, 4, 1)
    INSERT INTO manufacturing VALUES(CONVERT(datetime,'2021-09-15 07:42:00.000'), 296, 5, 43)
    INSERT INTO manufacturing VALUES(CONVERT(datetime,'2021-09-15 07:46:00.000'), 296, 5, 43)

SELECT * FROM manufacturing
COMMIT TRANSACTION

What I need the output from table above:

-------------------
| dt | batch_count|
-------------------
| 0  |      2     |
| 1  |      2     |
| 2  |      2     |
| 3  |      3     |
| 4  |      2     |
| 5  |      3     |
| 6  |      2     |
| 7  |      3     |
-------------------

Total of 18 divided by hours.

CodePudding user response：

I believe the following obtains your desired results. It's still counting the distinct batches per hour, but excludes the batches you don't want to count because they don't end in the current hour. The date range fitering is not relevant to the issue, you can add that in where necessary.

select DatePart(hour, dt) hr, Count(distinct batch_no) batch_count
from manufacturing m
    where not exists (
        select * from manufacturing m2 
        where m2.parcel_id=m.parcel_id 
            and m2.batch_no=m.batch_no 
            and DatePart(hour, m2.dt)>DatePart(hour, m.dt)
    )
group by DatePart(hour, dt)

CodePudding user response：

You seem to be describing:

SELECT parcel_id, DATEPART(hour, rh.dt), COUNT(DISTINCT rh.batch_no)
FROM manufacturing rh
WHERE rh.dt >= CONVERT(DATE, GETDATE()) AND
      rh.dt < DATEADD(DAY, 1, CONVERT(DATE, GETDATE()))
GROUP BY rh.parcel_id, DATEPART(hour, rh.dt);

Note the simplifications made to the date comparisons.