CODEWITHSUNDEEP
mysqlsql
Stroes
Stroes

Reputation: 361

Complex SQL per hour query

I am sitting in a bit of a pickle. I have a database with multiple tables that gets updated realtime with products being created, each product goes to a specific table in the db. Now i have to do hourly queries to monitor production but have to manually run the query every hour (as seen below) is it possible to have this query but it shows every hour and each total for that hour? Basically starting at 05:00 and ending 16:00+ with each showing on a seperate line?

SET 
@start = '2014-06-23 05:00',
@end = '2014-06-23 06:00';

SELECT 
(SELECT COUNT(id) FROM HandsetKit_T WHERE createdDate BETWEEN @start AND @end) AS 'Handset Kit',
(SELECT CONCAT (
(SELECT COUNT(p.id) FROM Pallet_T p, PalletNotify_T pn WHERE p.id = pn.palletID AND p.createdDate BETWEEN @start AND @end AND pn.esbStatus = '3' AND p.mtnAssetID NOT LIKE '11' AND p.mtnAssetID NOT LIKE '12') , ' / ' ,
(SELECT COUNT(p.id) FROM Pallet_T p, PalletNotify_T pn WHERE p.id = pn.palletID AND p.createdDate BETWEEN @start AND @end AND pn.esbStatus = '4' AND p.mtnAssetID NOT LIKE '11' AND p.mtnAssetID NOT LIKE '12'))) AS 'HK EOL',
(SELECT COUNT(id) FROM Kit_T WHERE createdDate BETWEEN @start AND @end AND mtnAssetID = '3') AS 'Wallet One',
(SELECT COUNT(id) FROM Kit_T WHERE createdDate BETWEEN @start AND @end AND mtnAssetID = '10') AS 'Wallet Two',
(SELECT COUNT(b.id) FROM Brick_T b, BrickNotify_T bn WHERE b.id = bn.brickID AND bn.esbStatus = '3' AND b.createdDate BETWEEN @start AND @end) AS 'Wholesale',
(SELECT CONCAT (
(SELECT COUNT(p.id) FROM Pallet_T p, PalletNotify_T pn WHERE p.id = pn.palletID AND p.createdDate BETWEEN @start AND @end AND pn.esbStatus = '3' AND p.mtnAssetID = '12') , ' / ' ,
(SELECT COUNT(p.id) FROM Pallet_T p, PalletNotify_T pn WHERE p.id = pn.palletID AND p.createdDate BETWEEN @start AND @end AND pn.esbStatus = '4' AND p.mtnAssetID = '12'))) AS 'Wholesale EOL',
(SELECT COUNT(bb.id) FROM BlisterBrick_T bb, BlisterBrickNotify_T bbn WHERE bb.id = bbn.blisterBrickID AND bb.createdDate BETWEEN @start AND @end AND bbn.esbStatus = '3') AS 'Blister',
(SELECT CONCAT (
(SELECT COUNT(p.id) FROM Pallet_T p, PalletNotify_T pn WHERE p.id = pn.palletID AND p.createdDate BETWEEN @start AND @end AND pn.esbStatus = '3' AND p.mtnAssetID = '11') , ' / ' ,
(SELECT COUNT(p.id) FROM Pallet_T p, PalletNotify_T pn WHERE p.id = pn.palletID AND p.createdDate BETWEEN @start AND @end AND pn.esbStatus = '4' AND p.mtnAssetID = '11'))) AS 'Blister EOL'

Upvotes: 0

Views: 77

Answers (1)

wallyk
wallyk

Reputation: 57784

If the existing schema is working well enough, then I would add another table which consolidates all the products (products) which is filled by triggers on all the other tables. Maybe the only columns it has are product_type and createdDate:

Then the query is simple:

SELECT COUNT(*)
 FROM products
 WHERE createdDate BETWEEN @start AND @end
 GROUP BY product_type

If the current schema is already burdensome, then it is time to refactor the database. Generally, having parallel tables of the same structure is poor database design. (Exceptions where parallel tables is a good thing include data archiving, data replication, and similar uses.)

Upvotes: 1

Related Questions