SQL to display hourly count including zero

Question

Post reply

SQL to display hourly count including zero

mikek 50666

Right there with Babe

Points: 779

March 21, 2017 at 2:03 am

#151085

I have been struggling with this for 2 days now and need some help 🙁

I am trying to display hourly production rate for a machine including hours where 0 parts are made. I have been looking and the answer seems to be to do with creating a second table containing the hours and doing a left outer join on this, but I seem to be doing something wrong and cannot get it to work.

The below is using a second table with idhours column which contains 24 rows with numbers 0-23 however it seems to be multiplying the count and still not returning the zero rows

SELECT DATE_FORMAT(MACHINE_TIMESTART, '%H') as `Hour_of_MACHINE_TIMESTART`, COUNT(*) as `Count` 
FROM `production_data` 
left outer join hours on idhours where date(MACHINE_TIMESTART)=date('2017-03-14') 
GROUP BY `Hour_of_MACHINE_TIMESTART` 
ORDER BY `Hour_of_MACHINE_TIMESTART` ASC

If I leave out the inner join then the results are correct but not showing the zero hours.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Hans van Dam SSC-Addicted Points: 486 More actions · Answer 1

Hi,

This does not look like MS SQL does it?

Maybe the wrong forum?

Regards,

Hans van Dam

DesNorton SSC-Insane Points: 24387 More actions · Answer 2

Please provide us with CREATE TABLE scripts for the 2 tables, as well as INSERT statements with sample data, and the expected results.

How to post data/code on a forum to get the best help.

DesNorton SSC-Insane Points: 24387 More actions · Answer 3

The format of your query appears to ba MySQL format.

In T-SQL, we can do it like this ...

Create some sample data
CREATE TABLE #production_data ( MACHINE_TIMESTART DATETIME NOT NULL , SomeIntData INT NOT NULL );

SET NOCOUNT ON;

DECLARE @SAMPLE_SIZE BIGINT = 1000;

DECLARE @MinInt INT = 10 , @MaxInt INT = 500 , @StartDate DATE = '2017-03-12' , @EndDate DATE = '2017-03-15';

WITH T(N) AS (SELECT N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS X(N)) , NUMS(N) AS (SELECT TOP(@SAMPLE_SIZE) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM T T1,T T2,T T3,T T4,T T5) INSERT INTO #production_data ( MACHINE_TIMESTART, SomeIntData ) SELECT MACHINE_TIMESTART = RAND(CHECKSUM(NEWID()))*DATEDIFF(DD,@StartDate,@EndDate)+CONVERT(DATETIME,@StartDate) , SomeIntData = ABS(CHECKSUM(NEWID())%(@MaxInt-@MinInt))+@MinInt FROM NUMS AS NM OPTION (RECOMPILE); GO

Get the required data
DECLARE @ReportDate DATETIME = '2017-03-14';

WITH T(N) AS (SELECT N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS X(N)) , NUMS(N) AS (SELECT TOP(24) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1 AS N FROM T T1,T T2) SELECT NM.N AS [Hour_of_MACHINE_TIMESTART] , COUNT(*) AS [Count] FROM NUMS AS NM LEFT JOIN #production_data AS d ON NM.N = DATEPART(HH, d.MACHINE_TIMESTART) AND d.MACHINE_TIMESTART >= @ReportDate AND d.MACHINE_TIMESTART < DATEADD(DD, DATEDIFF(DD, 0, @ReportDate)+1, 0) GROUP BY NM.N ORDER BY NM.N;

How to post data/code on a forum to get the best help.