By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE RejectedPallets (
Name_Sku VARCHAR(200) NOT NULL DEFAULT '',
LocationName VARCHAR(20) NOT NULL DEFAULT '',
Message VARCHAR(200) NOT NULL DEFAULT '',
-- LPN ignored as unused
-- EventDateTime could be the first element of the primary key
-- Otherwise, it needs its own index
EventDateTime DATETIME NOT NULL
-- location type?
-- has crane?
-- has palletizer?
);
INSERT INTO RejectedPallets (Name_Sku, LocationName, Message, EventDateTime)
VALUES
('abc', 'xyzULxyz', 'aaa', '7-26-2022 8:00:00 AM'),
('abc', 'xyzPLxyz', 'bbb', '7-26-2022 8:00:00 AM'),
('abc', 'ULxyzOPD', 'ccc', '7-26-2022 8:00:00 AM')
;
3 rows affected
WITH inp AS (
SELECT
Name_Sku,
LocationName,
Message
FROM RejectedPallets
-- Ensure that EventDateTime is indexed if you want this to be fast
-- Use between rather than >= x AND <= y and add an index on EventDateTime
-- These are passed parameters, not manually typed... right?
WHERE EventDateTime BETWEEN '7-26-2022 7:00:00 AM' AND '7-27-2022 7:00:00 AM'
),
crane AS (
SELECT
Name_Sku,
LocationName,
Message
FROM inp
-- Add location type as its own field! Or add has crane boolean
WHERE LocationName LIKE '%UL%'
AND LocationName NOT LIKE 'UL%OPD'
),
palzr AS (
SELECT
Name_Sku,
LocationName
FROM inp
-- Add location type as its own field! Or add has palletizer boolean
WHERE LocationName LIKE '%PL%'
)
-- Don't have distinct and group by in the same query
SELECT
palzr.Name_Sku AS PalletizerSku,
crane.Name_Sku AS CraneSku,
palzr.LocationName AS PL,
crane.LocationName AS Crane,
crane.Message,
| PalletizerSku | CraneSku | PL | Crane | Message | Total |
|---|---|---|---|---|---|
| abc | abc | xyzPLxyz | xyzULxyz | aaa | 1 |